This is a summary of an article from Database Trends And Applications; dbta.com. 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
We 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
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!
