All the SQL world should take database modeling classes, and thrive in the light of well-designed data structures. Back in the real world, though, an awful lot of folk who model DBs – or just add new database objects – miss, forget, or ignore basic normalization principles. For your consideration, Ground Zero Database Design guidelines:
- A database models something in real life, usually a business (or a piece of a business, like Sales).
- A table is a model of one real life thing, like Employee, or Order.
- Each row in that table is an instance of that thing, like Bob Smith in Accounting, or Order #42804 which totals $1,204.90.
- Don’t keep the same data in two tables. If Bob’s phone number resides both in the Employee table and in a Managers table, you have to update BOTH tables when the number changes…that’s no good. (One exception to this rule: capturing data for historical reasons, e.g. recording event attendees’ names and titles in an EventAttendees table; their title may have changed next year. But DO THIS ON PURPOSE, and don’t update the data – it’s historical!)
- If you have multiple rows in a table for a single thing, you need a new table. For example: The Orders table would need five rows for Order #42804 – one for each item ordered, like this:
Order # Company Name Order Date Item # Ordered Quantity 42804 Billco 10/10/2010 432 4 42804 Billco 10/10/2010 672 1 42804 Billco 10/10/2010 197 1 42804 Billco 10/10/2010 224 10 42804 Billco 10/10/2010 976 2 The problem with this is that we have repeating data: the order # is necessary to link the item ordered with the order, but Company name and order date are repeats. The better solution is an OrderDetail table linked to the Order table via a foreign key.
These are the bare bones basics. For more on database design that will save you space, time, money, love, and heartache, read any or all of the Database Design articles on SQLServerCentral.com, especially the Stairway to Database Design series by Joe Celko. And of course, there are a number of good database design books out there (I’ll find you a recommendation when I’m in front of my library…)
Happy days,
Jen McCown
