2

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?

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
Brad
  • 11,934
  • 4
  • 45
  • 73

3 Answers3

10

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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

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.

HABO
  • 15,314
  • 5
  • 39
  • 57
  • Since I have not used triggers before, how would I know which table to check against to verify the barcode which was inserted was present in the correct table? Each item's barcode has it's own prefix I guess I have some switch statement which parses a portion of the barcode then redirects to another stored procedure? – Brad Mar 21 '12 at 14:07
  • Oh! I think i get it! I don't add to the "summary items" when I want to make a link between the box and an item (i.e. populating the boxcontents table) I insert into the summary table at the same time as when I insert into an individual item table! Then the the entries are always correct. – Brad Mar 21 '12 at 14:10
  • @Brad - When you add an item to a box, you only care that the item's barcode is in the `union` of all item barcodes. Making a `view` that provides the `union` may simplify things. The triggers are there to enforce _deferential_ integrity, i.e. your own odd version of referential integrity, so that the data cannot* be corrupted. (* - Challenge left to the interested reader.) – HABO Mar 21 '12 at 15:19
  • I ended up using the view method ( a union of all relevant PKs in different tables) Thanks a lot for the help. Sorry for the delayed response. I went through a lot of trials! – Brad May 02 '12 at 15:34
0

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I feel like either way I will end up with rows with blank fields. If I go with option 1. then not every box will have all types of items. If I go with option 2. not every item will require the same columns for it's information. And blank fields seem like they are not a good idea. Especially a lot of them by design, right? – Brad Mar 21 '12 at 00:55
  • You're probably better to have your sparse data in the item table - it's more manageable there and is of finite and small row size – Bohemian Mar 21 '12 at 00:58
  • Yeah, unfortunate, but I can manager to collapse all those tables to one. I guess I will need to enforce the required/N/A fields on the application level. – Brad Mar 21 '12 at 01:22
  • You can always use `check` constraints on the columns to enforce the columns you need are there for each item type, eg `col1 int check ( item_type_id != 1 or (item_type_id = 1 and col1 is not null))` etc – Bohemian Mar 21 '12 at 02:02