Better Data Modeling: Are you making these 3 beginner mistakes in your data models?
There are lots of people in the database industry that end up building data models.
Some of them are very educated and well trained and are professional data modelers and data architects. (If that is you, you can probably skip this article)
Others have learned on-the-job with little or no training or education on modeling concepts or techniques. They may be database administrators or even programmers that got asked to produce a model diagram by their boss or project manager, after the project was delivered (but there was no data modeler on the project ever).
This article is for this last group of folks who may want to improve their knowledge or skill in data modeling.
So here are three of the most common mistakes I have seen over the years:
1. Only defining surrogate keys
Do all your tables have a primary key defined and that primary key is a single column integer generated by the system? That is a surrogate key.
Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.
CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key.
2. Using Visio or PowerPoint to draw your data model diagrams
This is all too common when no data modeler has been hired for a project or there is a really tight (as in NO) budget. The result is a pretty picture that gets out of date very quickly and can’t help you generate DDL code to build (or rebuild) the database.
Instead of that, you should either invest in a real data modeling tool (like ERWin), or better still, get Oracle’s totally free SQL Developer Data Modeler (my favorite!). The point being with a real data modeling tool, you can forward and reverse engineer your database tables, make changes, review them, generate DDL, etc.
3. Not reviewing the model with real business people
Build it and they will come does not work! How do you even know you are using the right terminology or have defined the relationships correctly if you have not talked to a business person about their data and data needs?
Instead of that, you need to involve business users from the very beginning of the project. You need to either set up 1:1 interviews with key stake holders or better yet have group JAD sessions to discuss the project and have them help you define the data model. That is the best way to get buy in to the end result.
Just avoiding these three rookie mistakes can greatly improve your chance of success.
Bonus Tip: The best way to avoid these and other common rookie mistakes is to use a pre-defined data model review checklist.
You can get a jump-start on your own check list by downloading my Kindle book A Checklist for Doing Data Model Design Reviews on Amazon.com.
Get it here: http://www.amazon.com/Check-Doing-Design-Reviews-ebook/dp/B008RG9L5E/, or just search Amazon for author Kent Graziano.
Here’s to building better data models! Are you with me?
Kent Graziano
The Data Warrior
The Data Warrior 