DaveWentzel.com            All Things Data

Physical Data Modeling AntiPatterns - column values with different meanings

This is part five of my series on Physical Data Modeling AntiPatterns

A column in a table should be a single attribute.  SEX (male or female) in an Entity table should not mean something else (profit or non-profit legal entity as an example) when the row is a corporation.  So basically, context matters.  That's just one simple example of when the single attribute/column paradigm is violated.  Here are some others:

  1. Have you ever worked with a system where NULL actually meant something within the system?  If a SALARY col in an Employee table has a bunch of NULLs then I would assume the salary is UNKNOWN.  Surprisingly, in many system that means the employee is compensated hourly.  In other systems that means the SALARY is hidden from prying eyes.  
  2. With the NoSQL movement, people are modeling SuperColumns which is almost like a vector that is stored in a single col in a single row.  Yes, I understand Oracle has the concept of nested tables.  This doesn't mean they are a good idea.  The reason this is popular in NoSQL is because the modeling is specifically not relational!  So the trade-off of queryability for other benefits of NoSQL (such as persisted sorted data) is made intelligently upfront (hopefully).  

Add new comment