Database Design

Good database design is the foundation of a stable application. Your tables must be normalized, indexed and keyed properly. Your table relationships must be clearly defined and isolated. Your data types must be accurate.

Your company data is one of your most valuable assets, make sure to use best practices when you design your database. A complete understanding of how the data is used, how it is acquired, and how it will be used in reporting is an absolute requirement. You should complete a high-level application flowchart before you start the DB schema design.

We have designed database systems for multiple organizations. From small e-commerce applications to national asset tracking systems. We primarily develop with opensource platforms like MySQL and PostgreSQL.

SQL versus non-SQL

Different applications require different technology. Entexture specializes in developing enterprise applications. Enterprise applications are built on relational database architecture and use Structure Query Language (SQL). Non-SQL databases are designed for high-volume, low complexity, internet applications. They work well for single-level (very wide and very shallow) data sets. They are great for storing user settings and caching, but they lack the flexibility and power to handle complex data sets.

SQL databases allow for joins and grouping related data across multiple tables. SQL was designed to handle multi-level (wide and deep) data structures and complex relationships. When you are tying together systems for real-world business applications you need to power of relational SQL.

Postgres SQL

Relational Database Platforms:

Ideal for business applications, advanced reporting, and data-mining.

Non-relational Database Platforms

Ideal for fast caching, user settings, and mirroring client browser storage.

  • MongoDB
  • Reddis
  • NoSQL
  • Amazon DynamoDB

Normalization

Normalization ensures data integrity. We ensure your database will be designed properly following best practices and at minimum meet the 3rd normal form.  A good database architect understands the rules of normalization and knows the proper time to apply standards.

Design Documentation and Planning

We create high-level and detailed technical specification documents that fully describe your applications data. The core of a solid software application is the database, it must be completely thought out, diagrammed, modeled and sanity-checked before development begins.

Amazon RDS

Best Practices and Tips

  • Always normalize as much as possible without creating overly abstracted structures for the sake of normalization.
  • Never save calculated values based on data in another column.
  • Always store parent id’s in child records when joining 1-to-many.
  • Never store meta data in index tables. Index tables should contain 3 integers, nothing more.
  • Ensure your automated backups are running on a regular basis.
  • Perform disaster recovery drills at least every 6 months.
  • User scheduled events to keep your logs and logging tables to a reasonable size.
  • Migrate old logs out of the active DB.
  • Clearly document all triggers and stored procedures.