• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Recursive Many-To-Many

#1
[eluser]jroot[/eluser]
Imagine that you have a products table in a manufacturing business. Those products can be sold individually. However, in some cases those products are used like raw material to construct other products.

For example, say you have an L.E.D. light that can be sold to a customer. That same LED can be used in the construction of another product that we call an assembly.

Modeling this in CI or in the DB isn't tough. The problem comes in when an assembly of products is used to create another assembly product. In theory, you could have assemblies of assemblies of assemblies... infinity. Worse yet, multiple assemblies can be used to create other assembly products.

For example, a battery, wire, LED and solder are individual products that can be combined to create a lighting assembly. Wheels, frame and body can create a model car assembly. A car with lights assembly would be the lighting assembly plus the model car assembly.

This example, a battery, wire, LED, solder, wheels, frame and body would all be products with part numbers. The lighting assembly and the model car would each have part numbers. Finally the car with lights assembly would have a part number.

I could really use some help with a schema that make some sense. I have been thinking that you would have a base_products table with productIDs. The products would relate to an assemblies table that have assemblyIDs. I could add a column to the assemblies table that has the id of another assembly, but I wouldn't be able to create an assembly product that is comprised of multiple assemblies...

It feels like 4th normal form is necessary (a table of ids to keep all of the relationships in order), but I don't have a huge amount of experience at this level of recursion... Any advice would be great!

#2
[eluser]Michael Wales[/eluser]
Quote:parts
-----
id
number
name

assemblies
----------
id
name

assemblies2parts
----------------
assembly_id
part_id

assemblies2assemblies
---------------------
parent_assembly_id
assembly_id

All of your higher order assemblies (an assembly of assemblies) would consist of an entry in assembly and an entry in assemblies2assemblies. parent_assembly_id would be the larger assembly, with multiple assembly_id entries for each child assembly.

You could then check the child assembly's for a parent_assembly_id entry in assemblies2assemblies, if one does not exist you know it is a low-level assembly. So, you would then look for that assembly's id in assembly_id within assemblies2parts.

#3
[eluser]jroot[/eluser]
Thanks for the reply.

That's what I thought. I was looking for an easier set of relationships so that I would have to work across so many tables, but I knew it wouldn't be that easy. :grrr:

#4
[eluser]Michael Wales[/eluser]
Well you could always create one many-to-many join table and then include a 'type' column - I'm just fond of 2 column joins.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.