Geek Answers Handbook

Saving multiple hierarchies in sql

I have this hierarchy for classifying 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 to create them in a table?

My idea was something like this:

 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 that I will also have a problem with multiple parents.

The goal is to place these hierarchies in the TreeView control, and the user will be able to call up any hierarchy that he wants to see just by clicking a button.

Thanks!

+4
source share
2 answers

Personally, I prefer nested sets when it comes to hierarchies stored in sql. Depending on your limitations, there are many variations of the method, and there are many resources over the network regarding sql CRUD routines.

I have to say that when you have large trees, you will have a small performance penalty when creating / updating / deleting main parents, but you have excellent reading performance, and I think this is preferable to a recursive model where you have another way.

Edit:

I see a problem with multiple parents. I would suggest changing your approach by combining 2nd and 3rd levels. You will 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 request for all cars that have color will be something like

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

Edit2:

a-horse-with-no-name correctly indicated that your DBMS can support recursive models. If so, then this may be the best solution for you. There is documentation for Sql Server here.

+2
source

If it is considered as another type of hierarchy ...

 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 to 3 tables: Cars, Colors, Colorful tones

So, you can query the raw data, and then generate a tree structure loop in your code.

+1
source

More articles:


All Articles