Two records in the same table must be related somehow I've inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two &quot;structures&quot; are combined into one larger &quot;structure&quot;, so there should be a way of linking them.
Should I use an &quot;association table&quot; to link the two IDs, or is there a better way for this sort of thing? Perhaps I've answered my own question, but I thought I'd ask if there's another way...
Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.
Maybe this is a ridiculous open-ended question for this site, but surely I'm not the first person to run into this...
Thanks,
Sean
I believe the requirement to record &quot;these 2+ items are used to make this 1 bigger item&quot; is called a BOM. (Bill Of Materials).
The only set of BOM tables I've seen in action used 1 table for each depth.  Your BOM would only need the two tables.
TL;DR - use the &quot;association table&quot;.