[thelist] Proper DB Table Design...

[Mattias Thorslund]

Travis wrote:

>It just seemed a little strange having to have unique values across 2
>tables, is all.
>
>  
>

It isn't so strange to me.  Only of course that you will probably have 
to maintain the uniqueness yourself.  Depending on your RDBMS, you might 
be able to use a check constraint that can enforece it, but at what 
performance cost I have no idea.

Before you plunge into the 'adjacent list' model, also consider the 
following articles on the subject:

http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/
http://www.sitepoint.com/article/hierarchical-data-database/2

Also, since you have two types of nodes in your hierarchy, an 
alternative design for that would be to use a 'Nodes' table for just the 
hierarchical ordering (according to one of the models above).  Then add 
a NodeID Foreign Key column to both your Documents and Categories 
table.  That would also ease your pain if you should ever need to add 
another type of node ("Links"?)...

/Mattias