Tree structure in table

Question

Tree structure in table

SQLRep

SSCrazy

Points: 2052
More actions
May 11, 2005 at 1:29 pm

#87541

Hi all,
I am very confuse about this requirement from the eng dept. They have one model that uses the assesmbly and subassembly. this is kind of confusing but please try to understand and guide me as well.
thanks.
e.g.
Assembly is part number: it has subassembly, but some times assebly can be part of subassembly as well.

How can i creat this in database.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

BarbF Newbie Points: 2 More actions · Answer 1

e.g.

It would be the same thing as a Manager is still an employee in a Personnel database, if I'm reading this correctly. To do this you need to set up fields for ParentAssemblyID, each sub assembly has a parent Assembly, whether or not the sub assembly is an assembly or a sub assembly.

So the table would look something like this:

tblAssembly

ID int

ISAssembly TinyInt

ParentAssemblyID int

..... other fields.

If the ParentAssemblyID is null or 0 then you know that it's an Assembly and not a sub assembly. When you need to know what subassembly's make up that assembly, pull all records that have that ID as the ParentAssemblyID, if the field IsAssembly is a 1 then you know that even though it's a subassembly to the current assembly, it too, is an assembly. So you'd have to pull all those subassemblies that match that one.

I hope this makes sense. I probably didn't describe it very well.

noeld SSC Guru Points: 96590 More actions · Answer 2

How do you call this ...? "Self promotion"

* Noel

iadams SSCommitted Points: 1920 More actions · Answer 3

Look up "Maintaining Hierarchies" in BOL. Also look into "Self Join" for a parent child relationship in a single table.

-Isaiah