When to Break Up One Table to Multiple Tables
Is your table data normalized? By that I mean:
1NF - A table must have atomic values and a primary key.
2NF - Requires that all non-key columns depend on the whole primary key
3NF - Requires that there be no transitive dependencies among non-key columns.
For example:
Improper 3NF
Auto_make tire_make tire_life
--------------------- --------------- -------------
1995 Mazda Protégé Michelin XJ11 50,000
1995 Acura Integra Michelin XJ11 50,000
1995 Porsche 911 Pirelli ABQ-32 112,000
The tire_life has nothing to do with the Auto_make of the car. It is really dependent on the value in tire_make. The way to correct this is to split it into 2 tables - Autos and Tires like this:
Auto_make tire_make
--------------------- ---------------
1995 Mazda Protégé Michelin XJ11
1995 Acura Integra Michelin XJ11
1995 Porsche 911 Pirelli ABQ-32
tire_make tire_life
--------------- -------------
Michelin XJ11 50,000
Pirelli ABQ-32 112,000
In my experience over the last 15 years DBA''s and Developers love to denormalize to make data access easier. i.e.: Why write a 12 table join when a 2 table join is easier to write? If you go to a 3NF won''t the optimizer slow things down? Won''t scanning more indices slow it down?
Try normalizing and running .5 Terabytes worth of data through the system. Yes, you will need hardware to support it but hardware alone is NOT the answer.
Here''s why normalized databases often perform faster:
* You have lots more tables, which means lots more choices for the Optimizer to choose from in selecting an efficient query plan. (Compared to query execution time, the time it takes the optimizer to analyze all it''s options is trivial, so don''t worry about that.)
* The several smaller tables of a normalized relationship are often smaller, in total, than the one large table would be if you denormalized them. Therefore, table scans are
Source: Keith Barrows, Interlink Group, Inc.
Viewed 5127 times