one to many to many

[boogyman]

nothing i have found online can help me with the problem I am having. I am trying to build a db on the assembly of a printer, however I am getting stuck with what to create for tables. All of the tutorials i have been able to find say that there need to be at least 3 tables to preform this action,however I dont really understand that fact. There are assemblies that contain other assemblies, now at first I thought about doing 1 table of all assemblies and 1 table of non-assembly parts, however you cannot have a table look at itself hense the third table... so do I make duplicate tables with a full list of all of the parts?? I am really lost on how I am going to set this database up. Any help would be very helpful. Thanks

[Twey]

however you cannot have a table look at itself hense the third table...

Yes you can. Self-referencing relationships are common.

[pagerefiner]

The three table approach involves creating a table called assemblies and a second table called parts. The third table includes a primary id field plus foreign key fields for the assemblies and parts tables. The third table allows you to relate any number of parts with any number of assemblies. For a simple assemblies table, you could use the adjacency list model which uses a parent_id field to create your top-down breakdown of assemblies and subassemblies. The parts table lists the actual parts, part numbers, vendors, pricing, etc.

An alternate approach is to use the nested sets model (also called modified inverse preorder tree traversal) which uses a single table to mimic a hierarchial tree structure in a RDBMS. The original concept was developed by Joe Celko (DBA guru) and there are numerous articles on the web regarding this topic, plus he wrote a book on the subject called "Joe Celko's Trees and Hierarchies in SQL for Smarties", ISBN:1-55860-920-2.

You might try doing some web searches on "nested sets", "Joe Celko", "tree traversal", etc. There are numerous other solutions based on the original nested sets model that improve the original concept if you need further enhancements.