modeling a recursive table in SQL Server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

modeling a recursive table in SQL Server

I am not able to figure out a way to model a table which is related to itself multiple number of times. My table stores the parts of a product and can contain something like: PartID | PartName
1 | Nut
2 | Screw
3 | Clip
4 | Plate
5 | Rod
6 | Handle
7 | Leg
8 | Tray Assume: Handle is made up of clip, nut and a screw
Leg is made up of handle, rods, nuts and screws
Tray is made up of plate, handles, nuts and screws What would be the additional attributes needed in the table to represent: nuts, screws and clip to be the sub parts for handle
handle, rods, nuts and screws to be the sub parts of leg
plate, handles, nuts and screws to be the sub parts of tray In short, I want to model a table that represents a tuple having more than one child row and more than one parent row… Thanking you in advance… time machine does not exist…
I’d probably use an item table plus a bill of materials table… tblItem (id, name)
tblBOM( id, subid, partcount ) so tblItem would have all of the items as per your list above, and tblBOM would have the tuples as in 6,1 6,2 and 6,3 for handle, etc.
Cheers
Twan

]]>