Should I normalize my DB or not?

by

A philosophical answer: Sub-optimal (relational) databases are rife with insert, update, and delete anomalies. These all lead to inconsistent data, resulting in poor data quality. If you can’t trust the accuracy of your data, what good is it? Ask yourself this: Do you want the right answers slower or do you want the wrong answers faster?

As a practical matter: get it right before you get it fast. We humans are very bad at predicting where bottlenecks will occur. Make the database great, measure the performance over a decent period of time, then decide if you need to make it faster. Before you denormalize and sacrifice accuracy try other techniques: can you get a faster server, connection, db driver, etc? Might stored procedures speed things up? How are the indexes and their fill factors? If those and other performance and tuning techniques do not do the trick, only then consider denormalization. Then measure the performance to verify that you got the increase in speed that you “paid for”. Make sure that you are performing optimization, not pessimization.

[edit]

Q: So if I optimize last, can you
recommend a reasonable way to migrate
data after the schema is changed? If,
for example, I decide to get rid of a
lookup table – how can I migrate
existing databased to this new design?

A: Sure.

  1. Make a backup.
  2. Make another backup to a different device.
  3. Create new tables with “select into newtable from oldtable…” type commands. You’ll need to do some joins to combine previously distinct tables.
  4. Drop the old tables.
  5. Rename the new tables.

BUT… consider a more robust approach:

Create some views on your fully normalized tables right now. Those views (virtual tables, “windows” on the data… ask me if you want to know more about this topic) would have the same defining query as step three above. When you write your application or DB-layer logic, use the views (at least for read access; updatable views are… well, interestsing). Then if you denormalize later, create a new table as above, drop the view, rename the new base table whatever the view was. Your application/DB-layer won’t know the difference.

There’s actually more to this in practice, but this should get you started.

More Related Contents:

  1. Storing JSON in database vs. having a new column for each key
  2. MySql views performance [closed]
  3. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  4. How to speed up sql queries ? Indexes?
  5. How to store data with dynamic number of attributes in a database
  6. Normalization in database management system
  7. MYSQL OR vs IN performance
  8. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  9. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  10. Throw an error preventing a table update in a MySQL trigger
  11. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  12. Is there a MySQL option/feature to track history of changes to records?
  13. Using an Alias in SQL Calculations
  14. Check for x consecutive days – given timestamps in database
  15. MySQL Insert Into from one Database in another
  16. How do I get the ID of multiple inserted rows in MySQL?
  17. How do I show the schema of a table in a MySQL database?
  18. MySQL concatenating all columns
  19. Remove special characters from a database field
  20. Proper database model for a user feedback system (an interesting case)
  21. How to Optimize Queries in a Database – The Basics
  22. How big can a MySQL database get before performance starts to degrade
  23. MySQL – Replace Character in Columns
  24. FIND_IN_SET with multiple value [duplicate]
  25. remove all numeric characters from column mysql
  26. WooCommerce: Finding the products in database
  27. Is it unreasonable to assign a MySQL database to each user on my site?
  28. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
  29. Create a new database with MySQL Workbench
  30. Optimistic locking in MySQL

Leave a Comment