CODEWITHSUNDEEP
sqldatabasetreehierarchy
Oliver
Oliver

Reputation: 817

Storing multiple hierarchies in sql

I have this hierarchy to classify cars : 

                             Color
                           /       \
                      Light        Dark
                    /   |   \       /   |   \
                  Red Green Blue  Red Green Blue
                   |    |     |    |    |     |
                 Car1  Car2  Car3 Car4 Car5  Car6

But I can also have this hierarchy : 

                             Color
                      /       |       \
                   Red      Green      Blue
                  /   \     /    \     /   \
               Light Dark Light Dark Light Dark
                  |    |     |    |    |     |
                Car1  Car4  Car2 Car5 Car3  Car6

How can I create these in a table ?

My idea was something like that : 

Id | ParentId | ParentId2 | Name
--------------------------------
1    Null         Null       Color
2      1                     Light
3      1                     Dark

But if I want to add more hierarchies, adding many ParentId columns doesn't seem good. And I think I will also have a problem with multiple parents.

The goal is to put these hierarchies in a treeview control and the user would be able to call any hierarchy he wants to see by just clicking a button.

Thanks !

Upvotes: 4

Views: 1134

Answers (2)

alexb
alexb

Reputation: 971

Personally I prefer the nested sets when it comes to hierarchies stored in sql. There are many variations to the technique depending on your constraints and there are many resources over the net about CRUD sql routines.

I have to say that when you have large trees you will have a small performance penalty at Create/Update/Delete top parents but you have excellent performance when you Read and I think this is preferred over a recursive model where you have the other way around.

Edit:

I see the multiple parent issue. I would suggest a change in your approach by merging the 2nd and 3rd level. You would have something like 

**Node**      |       **Left**      |     **Right**      |     **Level**
Color                 1                   26                   1
LightRed              2                   5                    2
LightGreen            6                   9                    2
LightBlue             10                  13                   2
DarkRed               14                  17                   2
DarkGreen             18                  21                   2
DarkBlue              22                  25                   2
Car1                  3                   4                    3
Car2                  7                   8                    3
Car3                  11                  12                   3
Car4                  15                  16                   3
Car5                  19                  20                   3
Car6                  23                  24                   3

The query to get all the cars that have color would be something like

select * 
from hierarchy
where left > 1 and right < 26 and level = 3

Edit2:

a-horse-with-no-name correctly pointed that your DBMS may support recursive models. If so then it may be a better solution for you. Here is some documentation for Sql Server.

Upvotes: 2

aifarfa
aifarfa

Reputation: 3939

Should Its considered as different hierarchy type...

Id | ParentId | Type | Name
--------------------------------
1    Null         1      Color
2      1          1       Light
3      1          1       Dark
4    Null         2      Color
5      4          2       Red
6      4          2       Green
7      4          2       Blue
8      5          2       Light
...

or just normalize into 3 tables: Cars, Colors, ColorTones

So You can query raw data then generate tree structure loop in your code.

Upvotes: 2

Related Questions