Nested records, Hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Nested records, Hierarchy

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2008-07-16 : 13:41:28

I have a situation where I want to use max three tables. 1) CatalogItems, 2) CatalogItemSuppliers 3) CatalogItemPrices.

The trick here is a catalog items can be anything from a single material entity to a part (multiple materials) to an assembly (multiple parts).

Eg.: In the catalog I can have the following items :
1) Lumber materials
2) A railing assembly (several pieces of lumber)
3) A stair assembly(several pieces of lumber)
4) A deck assembly (several pieces of lumber)
5) A porch assembly (includes 4|3|2)

- Any of the items above can have one or more supplier.
- For each supplier, each item can have a different price.
- Items 2,3,4, and 5 have associated labour assembly costs.
- Item 5 links to 2,3,and,4
- Items 2,3,4 link to 1

Because the level of aggregation is unknown, it would seem the best fit is a nested hierchy structure. Therefore, it would require a parent/child relationship on the item price table.

So, my structure is as follows:


Catalog
ItemID | ItemType | Category | ItemDescription
-----------------------------------------------
1      | MATERIAL | LUMBER   | 2X4X8
2      | ASSEMBLY | STEPS    | 4R48W7RN10RS7

CatalogItemSuppliers
ItemSupplierID | ItemID | SupplierID | Currency
-----------------------------------------------
1              | 1      | 101        | USD
2              | 2      | 101        | USE

CatalogItemPrices
PriceID | ItemSupplierID | ParentID | UnitQty | Price  | Units
--------------------------------------------------------------
1       | 1              | 0        | 1       | 0.50   | FT
2       | 2              | 1        | 4       | 0.50   | FT
3       | 2              | 1        | 8       | 0.50   | FT

Before I get too far in the design, will this structure work, or does anyone have any better suggestions? What pitfalls will I have to avoid?

I know, this isn't normalized, but seeing that I can have nesting "n" levels deep, this seems to be the best solution.

Any thoughts?

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources