Subject:
|
Re: A heirarchical database question
|
Newsgroups:
|
lugnet.off-topic.geek
|
Date:
|
Wed, 15 Nov 2000 19:09:54 GMT
|
Viewed:
|
135 times
|
| |
|
|
In lugnet.off-topic.geek, Jim Hughes wrote:
> Here is a question for the DB experts:
>
> Say you had some data that was heirarchical, e.g. a list of Lego Themes:
>
> -- System
>
> ----- Aquazone
>
> ---------- Aquasharks
>
> What would be the best way to describe it in a DB table? Would this work OK:
>
> -----------------------------------
> Big table of Themes
>
> theme_parent (varchar)
> theme_name (varchar)
> theme_child(varchar)
> -----------------------------------
I'm assuming you mean that your table would have three columns, named
theme_parent, theme_name, theme_child. Your table would have data like:
System Aquazone Aquanauts
System Aquazone Aquasharks
System Space Blacktron
DUPLO Disney Winnie the Pooh
Yeah, you'd be OK with that approach. You'll have to figure out how to
deal with the fact that LEGO doesn't feel constrained to sticking with a
3-level product hierarchy, but that's your problem.
> or is there a better way to do this?
As Chris mentioned, you might use a tree-structured layout, where there's a
row in the table for each "node" in the classification, and each row points
to its parent node. You'd have columns like:
Node_ID (int) -- unique key to identify the record/node
Node_Level (int) -- the topmost node is level 0, its children are level
1, etc.
Node_Name (varchar) -- 'System', 'Aquazone', etc.
Node_Parent (int) -- ID value for the next node up
Node_Path (varchar) -- Full path for node, as per Chris's note.
Using the example data from above, this table would get rows like:
0 0 Root Null
1 1 System 1 System
2 1 DUPLO 0 DUPLO
3 2 Aquazone 1 System / Aquazone
4 2 Space 1 System / Space
5 2 Disney 2 DUPLO / Disney
6 3 Aquanauts 3 System / Aquazone / Aquanauts
7 3 Aquasharks 3 System / Aquazone / Aquasharks
8 3 Winnie the Pooh 5 DUPLO / Disney / Winnie the Pooh
This approach is more flexible, because you can set up any number of
levels, as you need them. But it is more costly, because standard database
tools won't support it very well. You (or software you write) will have to
track the relationships between the nodes.
If you are planning to do a lot of custom programming, this second approach
may be just what you need. If you don't want to mess with it, stick with
the first approach.
Steve
|
|
Message is in Reply To:
 | | A heirarchical database question
|
| Hi: Here is a question for the DB experts: Say you had some data that was heirarchical, e.g. a list of Lego Themes: -- System ----- Aquazone ---...--- Aquasharks What would be the best way to describe it in a DB table? Would this work OK: ---...--- (...) (24 years ago, 15-Nov-00, to lugnet.off-topic.geek)
|
6 Messages in This Thread:
 
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
