Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CtrlAltDel
Starting Member
17 Posts |
 Posted - 2004-11-08 : 06:17:31
|
I've seen many posts in these forums about recursive queries, but I think I've got a question that hasn't been asked yet: how do people go about designing the tables?I have a table which stores a hierarchical collection of categories thusly:CREATE TABLE dbo.Categories( category_id INT NOT NULL IDENTITY(1, 1), parent_id NOT NULL, category_name NOT NULL) I've also added declarative referential integrity on to the table, so that parent_id is properly defined as a foreign key to the category_id primary key. This means you can't go deleting a category if it has sub-categories defined (one of those pesky business rules you have to follow  ). It also means that for a root-level category, parent_id needs to equal category_id, which leads me on to my question:How do you create a root-level category? Since we are using DRI, you can't have a null value for the parent_id and update it with the SCOPE_IDENTITY() value from the insert on category_id. Is there something obvious I'm missing, or do I need a more contrived approach? Or should I remove DRI and do programmatic checks for sub-categories before allowing deletes?--"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-08 : 06:52:38
|
You could do something like:insert Categories(parent_id,category_name)select (ident_current('Categories')+ident_incr('Categories')),'self referencing root categoryEdit: Note you can only insert 1 row at at time like this, since the expression evaluates to a constant for the whole insert.rockmoose |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 06:53:09
|
| usually you hard code the root level to 0 (or something like that)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-08 : 07:03:30
|
| I usually let the column referencing the parent node allow nulls.That way it is possible to keep DRI without entering a "dummy" root.Any node with NULL as parent is a root node.rockmoose |
|
|
|
CtrlAltDel
Starting Member
17 Posts |
Posted - 2004-11-08 : 07:26:51
|
Thanks for the suggestions, rockmoose. The first one looks a little awkward, albeit only aesthetically, so I'll probably go with your second suggestion and remove the NOT NULL constraint on the parent_id field, and treat all rows with a null parent_id as root-level objects. Just so long as I can keep my DRI  - it saves so much hassle when writing DML to know that the database will stop my lusers doing what they do best (such as deleting whole swathes of records that they shouldn't be going anywhere near  )nr, the only problem with your approach is that I then have to have a new record with a category_id of 0 for the declarative referential integrity to work, and I'd have to code in extra logic to filter out this record when showing root-level objects.--"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 07:31:24
|
| Technically speaking, in any hierarchical structure there is one and only one "root" node. You may want to model several different trees in the same table, they are no different than nodes of a single tree. You just start from the topmost node you want instead of the root node. |
|
|
|
|
|
|
|
|
Variation on a recursive query theme