Storing a Hierarchy
-
I've seen multiple examples of how to store a simple hierarchy such as an Org Chart for a company, but I am trying to figure out how to store a hiearchy where an item can be related to different levels & branches within the tree. There might be another more complex name for this or maybe it's not considered a tree but a matrix. I'm not sure. Here's a representation of what I'm talking about:
Tree View Node Level Applications 1 Application 1 1.1 Reports 1.1.1 Report A 1.1.1.1 Employees 1.1.1.1.1 Employee 1 1.1.1.1.1.1 Employee 2 1.1.1.1.1.2 Report B 1.1.1.2 Employees 1.1.1.2.1 Employee 3 1.1.1.2.1.1 Employee 4 1.1.1.2.1.2 DTS Packages 1.1.1.2.2 DTS Package A 1.1.1.2.2.1 Employees 1.1.1.2.2.1.1 Employee 1 1.1.1.2.2.1.1.1 Employee 3 1.1.1.2.2.1.1.2
Table 1 ID Value Type 1 Reports Category 2 Report A Item 3 Report B Item 4 Employees Category 5 Employee 1 Item 6 Employee 2 Item 7 Employee 3 Item 8 Employee 4 Item 9 DTS Package A Item 10 DTS Packages Item 11 Applications Category 12 Application 1 Item
Table 2 Level ID 1 11 1.1 12 1.1.1 1 1.1.1.1 2 1.1.1.1.1 4 1.1.1.1.1.1 5 1.1.1.1.1.2 6 1.1.1.2 3 1.1.1.2.1 4 1.1.1.2.1.1 7 1.1.1.2.1.2 8 1.1.1.2.2 10 1.1.1.2.2.1 9 1.1.1.2.2.1.1 4 1.1.1.2.2.1.1.1 5 1.1.1.2.2.1.1.2 7
One of the things to note is that the Employees Category is related to many different types of items. Also notice that Employee 1 is related to multiple Employees Categories. I haven't gone and started to work on the procedures to insert, delete, and update the relationship table just because I'm wondering if I'm going down a path that has already been determined to not necessarily be the best.
Some items to understand:
- There can be n number of categories per item.
- A category can be used/related to many different items
- An item can be in many different categories. (although most likely just one)
The application is a prototype of an application documentor btw.
David -
This is a typical problem modeled for geographic systems where multiple parents are allowed :
Here is an example to get you started
* Noel -
This looks similar to my Products table in Which I have:
ProductCategoryID
ProductID
ParentID (The ProductID of this Product's Parent)
Now, the Parent ID points to it's Parent Product (this does not mean it's Category). This way I can have any imaginable Heirarchy I want (of course you need to know how to programatically display it
).Example:
Category Product Parent Desc
Dev Dev-01 (Null) Reports
Dev Dev-02 Dev-01 Administration Reports
Dev Dev-03 Dev-01 Productivity Reports
Dev Dev-04 Dev-02 User Activity Report
However, if ANYONE can please point me in the Direction of how to make this table into something that can display Products, and it's Components, with a Component posibly being a Product, please let me know!
-
I once wrote something like this for a Bill-Of-Material application. This is how I did it.
It contains a table with products (end products, components, etc) and a table with relations between these products.
create table SProducts
(
prdID int identity(1,1),
prdCode varchar(15) not null,
prdName varchar(50) not null,
prdUnit varchar(2),
prdDCost float,
prdRCost float,
prdTCost prdDCost + prdRCost
constraint PK_SProducts_prdID primary key(prdID),
constraint UN_SProducts_prdCode unique(prdCode)
)
create table SRelations
(
relID int identity(1,1),
prdPID int, -- ID Parent
prdCID int, -- ID Child
relNumber float
constraint PK_SRelations_relID primary key(relID),
constraint UN_SRelations_prdPID_prdCID unique(prdPID, prdCID),
constraint FK_SRelations_prdPID foreign key(prdPID) references SProducts(prdID),
constraint FK_SRelations_prdCID foreign key(prdCID) references SProducts(prdID)
)
-
Take a look here also http://www.dbazine.com/tropashko4.shtml
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply