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: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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:

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

Code:
 
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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
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..
Reply With Quote