Quickly Check for Database Design Flaws
Don't you just hate it? You've finished reading, again, that blog entry about database design and you're feeling that you can design something reasonable, and then you see this table:
|1004||John Hunt||New York||212-555-1212||Dell||HP||Apple|
You can easily see that
Customer3 are wrong, but what about the rest? Try as you might, you can't quite put all of the rules together that easily to figure out what's wrong with the above table.
There's a shortcut, though, and it makes it very easy to start understanding database design.
Take a look at the above table and pull out the nouns. You see that we have employees, offices, and customers. If you assume that each noun probably requires a separate table, you have a great start at normalization without necessarily knowing what the final database form will look like.
In the above, we know we might have an
employees table, an
offices table, and a
customers table, all just by separating the nouns. At this point, you ask "what if an employee is also a customer?" That's where expertise comes in because it's often tricky knowing how to separate out issues like this.
For a counter-example of separating nouns, consider a recipes table:
recipes ------- recipe_id name directions
directions is a noun, so why isn't that in a separate table? Because the
name column is merely a shorthand for
directions. You could argue that if two recipes had the same directions, they're the same recipe. Thus, the noun
directions is wholly dependent on the key.
Here's a different, but common example:
customers --------- customer_id name address
You see that so often that it almost looks natural, but what if you have more than one customer at an address? What if a customer has more than one address? Should your database have addresses without customers? Because customer and address are both nouns and the address is clearly not wholly dependent on the customer, it belongs in a separate table.
So there you have it: if you put aside the rules of normalization for a moment and just focus on the nouns, a lot of the low-hanging fruit of database design jumps out at you.
For added fun, read A Simple Guide to Five Normal Forms in Relational Database Theory (pdf).