Data & Intelligence

Seven Deadly Sins of Database Design

Seven Deadly Sins of Database Design

This is a summary of an article from Database Trends And Applications; The author addresses fundamental mistakes that we do or we live with in regards to our database systems.

1. Poor or missing documentation for databases in PRODUCTION

Seven Deadly Sins of Database DesignWe may have descriptive table names and columns to begin with, but as workforce turns over and a database grows, we can lose essential knowledge about the systems.
A suggested approach is to maintain the data model in a central repository. This must be followed by executing validation and quality metrics regularly to enhance the quality of the models over time.

2. Little or no normalization

All information in one table may be easier for data access but may not be the best option in terms of design. Understand normalization:
• 1st normal Form – eliminate duplicate columns and repeating values in columns
• 2nd Normal Form – remove redundant data that apply to multiple columns.
• 3rd Normal Form – Each record of a table is unique based on the primary identifier.

3. Not treating the data model like a living breathing organism

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Many people start with a good model when designing a database, and then throw it away as soon as the application is in production. The model should be updated as often as any new changes are applied on the database to communicate these changes effectively.

4. Improper storage of reference data

Store reference data in a model or have a reference in the model that points to reference data. Reference data is typically stored in several places or worse, in application code – making it very difficult when there is need to change this information.

5. Not using foreign keys or check constraints

Data quality is increased highly by having referential integrity and validation checks defined right from the database level.

6. Not using domains and naming standards

Domains allow you to create reusable attributes so that users don’t have to create them each time they need to use them. Naming standards increase readability of the database and make it easier for new users to adopt to a database. It is recommended that one uses proper names as opposed to short forms that a user has to try and figure out what they mean.

7. Not choosing primary keys properly

Choose a primary key wisely because it is painful to try and correct these down the line. A simple principle is suggested when picking a primary key; SUM: Static, Unique, Minimal. So a social security number may not be the best primary key in some cases because it may not always be unique and not everyone has one 🙂

Happy database modeling!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Joel Onditi

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us