Anith Sen, an SQL and database design guy based in Tennessee, has a well-written blog entry over on Simple-Talk about database design errors.
What I liked about Sen’s post is that he has taken great care to show data and table structures that appear to have some real world complexities to them while still being simple examples. I don’t know many bloggers who do this. Most examples seem to be slathered with “PersonName”, “ZIPCodes” and “tbl_EntityName” data modeling errors that distract me from the points being made. He includes data, table structures, and SQL. Kudos.
The 5 errors discussed are:
- Common Lookup Tables
- Check Constraint Conundrum
- Entity Attribute Value Table
- Application Encroachment on DB Design
- Misusing Data Values as Data Elements
Personally, I don’t agree that all of his examples are errors, per se, but I do agree that they are anti-patterns for most uses. My usual mantra of “all design decisions come down to cost, benefit, and risk” should apply. If we take, for instance, his example of statuses in a common code table, he seems to imply that all generalizations of status are inappropriate. I do agree with his reasoning as to why the pattern is costly, but I don’t see any reason why all statuses need to be in separate tables. I don’t believe all codes should be in one big table, either. Hence, my invocation of cost, benefit, and risk still applies.
A great article, though. Well worth your time to read and absorb.
