DaveWentzel.com            All Things Data

Physical Data Modeling AntiPatterns Series

"Patterns" are commonly used solutions to a given problem.  "Anti-patterns" are the problems with commonly used solutions.  I'm not going to cover why 5NF and Boyce Codd NF are often bad choices sometimes.  And I'm not going to cover why 1NF should be shunned in favor or 3NF for transactional systems.  With antipatterns the goal is to show why common solutions might not be ideal in many situations.  This is by no means a complete list of data modeling anti-patterns, but it's a good start.  In no particular order I'm going to cover...

  1. the misuse of BIT flags
  2. "dynamic" data structures
  3. MUCK table designs
  4. XML as a persistence mechanism
  5. Column values that have different meanings based on context
  6. Circular references

As you review these posts you'll note a recurring theme to these anti-patterns.  Again and again we see cases where a single attribute may have different meanings.  I think primarily these issues occur for two reasons:

  1. data modelers think they are smarter than they really are and think they have discovered a new pattern that no one else has used.  They then apply it en masse without properly researching the idea or thinking of where it will break down. 
  2. the data modeler does not have a firm understanding of what is an "entity", an "attribute", and a "value".  

More to come...