Hi, I am looking to redesign a database for my site. Where I am having problems is a relation between "part" and "assembly" type thing, where there are multiple bosses per employee. Right now I have it so that every boss has their own table, which has become so dreadfully cluttered and inefficient its come to the point where redesigning is the best option. I was thinking that a hierarchy tree would be the best, and what I thought would be the best would to have 1 table of all employees, then when I go to display them for each department have something like

and in my database have 3 fields.

name
desc
assembly

where each part can belong to many assemblies. I know this works for the parts that are only associated with 1 assembly, but what about the parts that have more than 1? the query isnt going to match the assembly, because they are seperated by a delimiter, in my case a "comma ( , )"

in the general parts/assembly model, yes, often a part can be part of multiple assemblies

but usually an employee does not have multiple bosses

i'm sure this is also the case in your situation (one table per boss?!! well, i hope you've learned your lesson! )

so it's a hierarchy, not a many-to-many, right?

see Categories and Subcategories

oops yah, when i was typing it out i thought about doing it in employee to boss as my example but figured it would be more beneficial to just say what i needed... it is parts to assembly, but what im asking is how do i structure the database so that when part1 is in assembly1 and assembly2... how would I write that into the database and how would I extract it?

and yes, I have learnt my lesson... its been 6months in my current method and its been a royal pain to maintain... and I am sure that my novice database knowledge doesnt help either.

so if ya could help with my first paragraph i would appreciate it

what you are asking for is out there on the web, somewhere, probably many times over

writing it into the database is easy, those are just INSERT statements

extracting it is easy too, those are just SELECT statements

what you are looking for is the parts explosion data model

there are only two tables, with two foreign keys in one of them

yes i know how to insert and extract information from a database, my problem is with the "parent" assembly record. I have some parts that have multiple assemblies, that must remain exclusive. but it ignores that parts with multiple assemblies, I am guessing because it is thinking that the parent assembly is "assembly1, assembly2" when in fact my intention is to have them seperate and read as "assembly1", "assembly2".

I have ran multiple searchs on google, and yahoo, neither of which have produced an example such as what I am looking for, which is why I am here in the first place. I am not saying to structure my database for me, I am looking for a reference of where someone has "already invented the wheel" and I just need to modify it to fit my specific needs.