April 23, 2004 at 9:42 am
I am looking into two possible design alternatives to model an item hierarchy. Here is the hierarchy in real world ->
Item -> GroupLevel1 -> GroupLevel2 -> GroupLevel3 -> GroupLevel4 -> GroupLevel5, where GroupLevel5 is at the top of the
hierarchy and item is at the lowest level and each -> points to the parent at that level.
The simple rule is that each item should have a parent GroupLevel1ID which in turn have GroupLevel2ID etc,. Each GroupLevelx
has different attributes, hence is a different entity.
Option 1:
---------
Option 1 model looks like a star schema with the Item entity at the center, with the GroupLevel IDs of the hierarchy forming
its PK. Then the GroupLevel entities are related in a parent child relationship.
Item Entity
============
ItemID |
GroupLevel1ID (FK) |
GroupLevel2ID (FK) |---- PK
GroupLevel3ID (FK) |
GroupLevel4ID (FK) |
GroupLevel5ID (FK) |
---------------
ItemName |
Desc |
ActiveDate | ---- Other attributes
Vendor |
etc,. |
Each GroupLevelx is modeled as the following (simplified with X for 1, 2, 3, 4 and 5 levels). There are 5 different entities
like this, with different optional attributes.
GroupLevelX
===========
GroupLevelxID |
ParentGroupLevelID | ---- PK
------------------------
GroupLevelName |
Desc | --- Other attributes
Other Stuff |
Option 2:
------------
Here all entities including the item are related in a parent - child relationship.
Item Entity
============
ItemID |
GroupLevel1ID (FK) |---- PK
---------------
ItemName |
Desc |
ActiveDate | ---- Other attributes
Vendor |
etc,. |
GroupLevelX
===========
GroupLevelxID |
ParentGroupLevelID | ---- PK
------------------------
GroupLevelName |
Desc | --- Other attributes
Other Stuff |
Now the question is which one is better?
Here are different scenarios:
If I want to find all the items under a GroupLevel5 ID, with option 1, I can do that from the item table itself. With option
2, I will have drill down each entity in the hierarchy, ie may be 4 or 5 joins. So Option 1 is the winner here.
If I want to change parent for items belonging to an ID at Group Level1, say X to Y. Option 1 requires changing the PK (and
clustered Index. The same is true for the Option 2. So both are the same here.
If I want to change the parent for an ID at an intermediate level in the hierarchy, with option 1, I have to change the item
as well as the intermediate level entity. With option 2, I just have to do at the intermediate level only, since item entity
is related only to its parent. So, option 2 is the winner here.
There may be other operations too. What do you guys think? Which one do you use? Any feedback is appreciated..
April 23, 2004 at 6:51 pm
This might also have merrit for you but INSERTS and UPDATES can be a nightmare especially if you have to widen a nest. Many folks including Joe Celko prefer this method. We are testing a blended soltuion of this and Parent Child for a project also.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply