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 the database schema. 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.

SQL versus NoSQL

Different applications require different technology. Entexture specializes in developing enterprise applications. Enterprise applications are built on relational database architecture and use Structured Query Language (SQL).

NoSQL 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 as they are “schemaless”, they lack the flexibility and power to handle complex data sets.

Relational Database Platforms:

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

• MySQL
• Microsoft SQL/TSQL
• PostgreSQL
• AWS Aurora
• Oracle

NoSQL Databases

Ideal for fast caching, user settings, wide shallow datasets.

• MongoDB
• AWS DynamoDB
• Redis (technically a caching system that follows NoSQL)

NoSQL does not support normalization

NoSQL databases like DynamoDB and MongoDB do not allow for normalization or foreign keys. The records are completely schemaless, so in theory, every record in a NoSQL table could have a unique set of columns.

SQL databases allow for complex joins, foreign keys, and grouping of related fields 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 the power of relational SQL.

Enterprise Database Table Structure

Database normalization reduces redundancy and ensures data integrity across large systems. The abstraction layer creates a robust topology that allows modification of child attributes without touching parent tables.

Enterprise database design follows the same rules as any normalized relational database. An enterprise database should always meet at a minimum the 3NF (Third Normal Form) of normalization.

Reaching 3NF ensures that the data is stored as efficiently as possible. It also ensures data integrity by limiting attributes to sets from child tables.

Database 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.

Object-oriented model database mapping in Enterprise Systems

A well-designed enterprise system will have a system object model that is mirrored in the database design.  In a high-level flowchart, the objects that populate your system are stored in persistent memory via your SQL database. The database level should follow and enforce the same data-type requirements as your objects.

Design Documentation and Planning

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

Hybrid SQL and NoSQL Enterprise Database Systems

There are occasions when the addition of a NoSQL database can be beneficial to the efficiency of a system. A SQL database can use NoSQL foreign keys to refer to metadata and document objects that are not appropriate for storage in a SQL table.

Just as large image files are best stored natively on (a web server or in S3) and referenced in a table (as opposed to actually saving the binary data inside a table), some data and document objects can be keyed into DynamoDB and connected via reference.

Redis is a great way to cache complex MySQL query results and reduce server load. This is effectively a hybrid SQL-NoSQL design although Redis acts purely as a caching engine. The server load can be reduced dramatically by adding a RAM Cache buffer and should always be considered when designing a large system.

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 metadata 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.