Thread: Poor db design?
View Single Post
  #2 (permalink)  
Old July 29th, 2009, 05:16 PM
Imar's Avatar
Imar Imar is offline
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts

I don't know the book so I can't judge on the actual design, but it's not uncommon to store parents and children in the same table. Consider this table:

Id         (int)
Name       (nvarchar(100)
ParentId   (int) (null)
Now you have a single table that allows you to store a tree of data:

1. Volvo      <null>
2. BMW        <null>
3. 850        1
4. 850 GLT    3
5. 850 T5     3
This is very useful when storing a tree with an unlimited depth. In this example, the 850 T5 is a sub category of the 850 series which in turn is in the Volvo category. To enforce the relationship, the table can have a self reference or self join where ParentId points to Id.

Not something you can easily accomplish with two separate tables.

Hope this helps,

Imar Spaanjaars
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Last edited by Imar; July 29th, 2009 at 05:18 PM..