|
- Does the entity table make sense? Can you express the idea of the table in a simple collective or plural noun? To be is to be something in particular; to be everything in general or nothing in particular is to be nothing at all (this is known as the Law of Identity in Greek logic). This is why EAV (Entity-attribute-value) does not work – it is everything and anything.
- Do you have all the attributes that describe the thing in the table? In each row? The most important leg on a three-legged stool is the leg that is missing.
- Are all the columns scalar? Or is a column serving more than one purpose? Did you actually put hat size and shoe size in one column? Or store a CSV list in it?
- Do not store computed values, such as (unit_price * order_qty). You can compute these things in VIEWs or computed columns.
- Does the relationship table make sense? Can you express the idea of the table in a simple sentence, or even better, a name for the relationship? The relationship is “marriage” and not “man_woman_legal_thing”.
- Did you check to see if the relationship is 1:1, 1:m or n:m? Does the relationship have attributes of its own? A marriage has a date and a license number that does not belong to aether the husband or the wife. This is why we don't mind tables that model 1:1 relationships.
- Does the entity or relationship have a natural key? If it does, then you absolutely have to model it as the PRIMARY KEY or a UNIQUE constraint. Is there a standard industry identifier for it? Let someone else do all that work for you.
- If you have a lot of NULL-able columns, the table is probably not normalized.
- The NULLs could be non-related entities or relationships.
- Do the NULLs have one and only one meaning in each column?
- If you have to change more than one row to update, insert or delete a simple fact, then the table is not normalized.
- Did you confuse attributes, entities and values? Would you split the Personnel table into “Male_Personnel” and ”Female_Personnel” by splitting out the sex code? No, sex is an attribute and not an entity. Would you have a column for each shoe size? No, a shoe size is a value and not an attribute.
|
|
