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:
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…)