Home » Beginner »sql server »sqlserverpedia-syndication »SSC » Currently Reading:

Ground Zero Database Design

October 14, 2010 Beginner, sql server, sqlserverpedia-syndication, SSC 2 Comments

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:

  1. A database models something in real life, usually a business (or a piece of a business, like Sales).
  2. A table is a model of one real life thing, like Employee, or Order.
  3. Each row in that table is an instance of that thing, like Bob Smith in Accounting, or Order #42804 which totals $1,204.90.
  4. 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!)
  5. 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
http://www.MidnightDBA.com/Jen

Currently there are "2 comments" on this Article:

  1. [...] This post was mentioned on Twitter by Steve Jones, Andrew Fryer, Josh Luedeman, Jen & Sean McCown, Jen & Sean McCown and others. Jen & Sean McCown said: Blog: Ground Zero Database Design http://tinyurl.com/2euo2yx An awful lot of folks miss, forget, or ignore basic principles… [...]

  2. [...] week I wrote Ground Zero Database Design, thinking that was a good enough bare-bones intro (with recommended reading!) that I could leave [...]

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 2 days 13 hours 52 minutes 16 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/