The Key to Database Normalization

The Key to Database Normalization, in one phrase:
  "If you have more than one of them, it probably needs its own table."

(Inspired by Ovid's How to Fake a Database Design and Robin Smidsrød's comment thereupon at 2013-07-25T09:46:25+00:00.)


That certainly sums up first normal form, which is intended to forbid things like:

OrderID      ProductID
1            42
2            33
3            21;42;60

Big and/or high performance database normalization is usually not a good idea. To get good performance you usually need de-normalize and break normalization and other database-design rules (at least with mysql)

There is no reason not to keep a well normalized database even if we are using mysql.

There are cases where materialized views and other query speed hacks are needed. These are best used in addition to the normalized schema not as a replacement for it. The point of the normalized schema is to minimize the activity required to maintain data consistency. All DML activity is best taken through the properly normalized schema. Any required update cycle or refresh of materialized views that is needed should happen subsequently or better through proper database configuration.

Leave a comment

About Mark Leighton Fisher

user-pic Perl/CPAN user since 1992.