Normalization - Looking for rule of thumb for how far to go.

[C. Lind Aitken]

Consider the street address field in three databases - a personal address book, a town's street tree inventory and a member directory of an organization of law enforcement employees.

In the address book, you would put the whole address into the field (ie 123 Main St.) even though there could be several Main streets and there will be many St's, Blvd.'s, Ave.'s etc which could be put into a separate table.

In the street tree inventory, you might actually have a separate table for street names because you could start by downloading a street list from the tax records and all streets would be included.

The membership database can really get strange because the large federal agencies will have office all over the place and some addresses (such as 5 World Trade Center in New York City will have several agencies. That looks like a many to many relationship would be necessary even though most members are employed by invividual agencies with only one location.

Is there any rule of thumb to determine an optimum point at which one would say, "Forget any more normalization, this is getting too complex!"?

[Chuck Harrington]

The whole purpose of data normalization is so when a user queries the data,it returns accurate and complete information based on the query selection criteria. Since you use the term normaliztion, I assume that you are familiar with the various normalization steps (i.e. 1NF, 2NF, 3NF). Typically anything beyond 3rd Normal form gets really complex and requires creating so many related tables that creating access queries becomes a nightmare and way beyond any end-user's patience or desire to learn. When I get to this point (where I think I need to take the database design to 4NF), I go back and re-examine all the various scenarios that my users will need to use to access data. (Tip - shoot for the 90% factor - satisfy 90% of their requested access scenarios, the other 10% is usually off the wall stuff, and they will find they can live without it) Design the database so that it is in 3rd normal form and you will find it will probably be enough. You may have to use compound keys (multi-column) in some of your tables and linking tables that resolve the many-to-many relationships that exist between other tables, but this will sure beat trying to get a database into 4th or 5th normal form. It has worked for me and I have been at this over 20 years. Hope this helps


------------
C. Lind Aitken at 10/26/00 11:35:19 AM

Consider the street address field in three databases - a personal address book, a town's street tree inventory and a member directory of an organization of law enforcement employees.

In the address book, you would put the whole address into the field (ie 123 Main St.) even though there could be several Main streets and there will be many St's, Blvd.'s, Ave.'s etc which could be put into a separate table.

In the street tree inventory, you might actually have a separate table for street names because you could start by downloading a street list from the tax records and all streets would be included.

The membership database can really get strange because the large federal agencies will have office all over the place and some addresses (such as 5 World Trade Center in New York City will have several agencies. That looks like a many to many relationship would be necessary even though most members are employed by invividual agencies with only one location.

Is there any rule of thumb to determine an optimum point at which one would say, "Forget any more normalization, this is getting too complex!"?

[Magnus Andersson]

Going to 3NF is usually enough. You might sometimes want to de-normalise some tables to get better select performace to the cost of updates (that might seldom occur).

I really dont understand the problem with the third example. The relation between office and addresses can be diffrent from the relation between offices and employees. One can be one-to-may, the other can be a many-to-many if necessary.

/Magnus


------------
C. Lind Aitken at 10/26/00 11:35:19 AM

Consider the street address field in three databases - a personal address book, a town's street tree inventory and a member directory of an organization of law enforcement employees.

In the address book, you would put the whole address into the field (ie 123 Main St.) even though there could be several Main streets and there will be many St's, Blvd.'s, Ave.'s etc which could be put into a separate table.

In the street tree inventory, you might actually have a separate table for street names because you could start by downloading a street list from the tax records and all streets would be included.

The membership database can really get strange because the large federal agencies will have office all over the place and some addresses (such as 5 World Trade Center in New York City will have several agencies. That looks like a many to many relationship would be necessary even though most members are employed by invividual agencies with only one location.

Is there any rule of thumb to determine an optimum point at which one would say, "Forget any more normalization, this is getting too complex!"?