CODEWITHSUNDEEP
sqlsql-server-2008database-design
Brad
Brad

Reputation: 12253

Many-to-Many but sourced from multiple tables

I am supposed to be shipping out a box with variable contents and tracking this in a database. All of my items (the contents of a box) are different types and require different tables to track their respective pieces of information, although each item type has the same length serial number (i.e. PK are the same datatype). And I have a Boxes table.

So each item has a table (~7 tables) plus the box table. I want to create a BoxContents table. I tried to make a many-to-many relationship intermediate table with two columns: one for BoxID and one for ItemBarcode, where BoxID is a FK to the PK on the Boxes table and the ItemBarcode is a FK to each of the PKs on the Items tables (i.e. I tried to link multiple tables to the same column). Unsurprisingly this didn't work. I tried to insert an item and the FK constraint was violated on all but one of the ItemBarcode relationships.

How can I construct my relationships to link several types of items to one box in one table? Is this a logical approach? Do you need more information?

Upvotes: 2

Views: 4928

Answers (3)

HABO
HABO

Reputation: 15841

My first choice, if the ItemBarcode values are truly unique, would be to:

EDIT: Added description of required triggers.

  • Add triggers to enforce the barcode uniqueness. (An insert/update trigger on each item table needs to verify that all (newly) assigned barcodes do not appear in other item tables.)
  • Use a single BoxId/ItemBarcode table without a FK relation on the barcode side, but with triggers to ensure it remains valid. (An insert/update trigger on the association table needs to verify that the barcodes exist in the item tables. A delete trigger on each item table needs to prevent, or cascade, deletion of items that are in the association table. An update trigger on the item tables needs to update and changed barcodes in the association table. This last may be integrated into the insert/update trigger in the prior bullet.)
  • Consider using a view of all items to access common data by ItemBarcode.

My second choice would be n BoxId/ItemBarcode tables for the n item types. Straightforward, but a bit busy. It makes adding a new item type messier than it needs to be.

I would not use a BoxId/ItemTypeId/ItemBarcode table. It denormalizes the data by associating the ItemTypeId and ItemBarcode again, it doesn't allow the use of a FK on the barcode side, and it still requires triggers to ensure integrity.

Don't be afraid of triggers. There are some problems that they can address quite effectively.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

You need a category hierarchy (aka. class hierarchy, subtype hierarchy, inheritance hierarchy...):

enter image description here

There are 3 main strategies for implementing a category hierarchy. If you choose "all classes in one table" or "class per table", then no matter how many kinds of items you have, you only need one "link" table to implement the many-to-many relationship.

Upvotes: 9

Bohemian
Bohemian

Reputation: 425198

Relational databases are not good with this kind of problem. Your basic design is correct - an association table for FKs between the tables.

Your choices are:

  1. Have multiple columns in your association table - one for for each item table
  2. Merge the item data into one item table

I would go option 2.

Upvotes: 0

Related Questions