1

I've come across a scenario where data in one table is related to another table but referential integrity doesn't seem to apply as I would usually expect. This is because table x uses data from another table y but y can change independently from x. This only happens in some scenarios related to the state of x.

Consider the example: there is a shipment_line table that has all items on a shipment. To be able to despatch the shipment there needs to be item weight and dimensions data. The item data comes from an item table. However, even though shipment_line appears to have a relationship with the item, once the shipment is despatched updates to the item should not propagate to the shipment.

This is because the shipment may have been confirmed with carriers to have particular attributes. So once despatched the info goes from being a relationship to it being baked into the shipment_line table.

This is a business logic decision, but if I have set up a PK FK relationship, it's not possible to control the update. In PK FK relationship X has Y but in this scenario X uses Y.

Since item data appears to be an embedded part of the shipment it would make sense to copy the data from the item table onto the shipment.

I have these points to question here:

  • What if the copy operation goes wrong (although it does make sense that it's the application's responsibility to create new shipment_lines with all requisite info)
  • If I copy the data I would still like the user to be able to navigate to the item from the shipment_line for any non-despatched shipments (so a relationship should still exist)
  • If the user updates an item then the details should change for non-despatched shipments. This saves them having to update every single shipment individually (again a relationship is nedded)
  • In general, how would you deal with changing referential integrity requirements according to application logic?

2 Answers 2

2

You are on the right track with copying the data when the dispatch happen. For keeping the copy I see two viable alternatives.

Obviously, you need an intermediate table shipment_line_item for all items of a shipment, with foreign keys shipment_line_id and item_id. shipment_line_item can additionally have all the columns of an item, and when the dispatch happens, the current state of the referenced item is fully copied into them. You may consider to set item_id to NULL within the copy transaction. Most sane relational databases allow nullable foreign keys, so the referential contraint is active only as long as the foreign key is not null.

An alternative would be to model items as historical data, where each item record has a date/time range which describes its validity, so you can keep old records there. Here, shipment_line_item requires just the two ids, and the copies are created within the item table itself. At the time of dispatching a shipment, one makes a copy of each contained item (with a new ID) and inserts the current end data into the copied record. Items with a set end date are marked as "historical" that way and must be never changed afterwards. The dispatched shipment line gets its current items exchanged against the IDs of the created copies. The "active" item (the one with the open end date, which might be referenced by non-dispatched shipments) gets a new start date whenever a value is changed inside.

2
  • For the historical data approach. Say shipments PO787 & PO777 both have item SKU3, it references an item that has 1 version (no changes). Someone updates SKU3, as PO787 has not been dispatched it should get the changes. But PO777 has been dispatched so what happens there? When you create a new entry for SKU3 the application needs to find all non-dispatched shipments referencing the old item and update to the new I suppose. It appears to be no different than having to find all non-dispatched shipments to update their item dimensions. In fact, you don't mess with foreign keys that way
    – durinsbane
    Sep 13, 2023 at 23:51
  • @durinsbane: you are right. It is probably better to make a historical copy of SKU3 as soon as PO777 is dispatched. I will edit my answer accordingly.
    – Doc Brown
    Sep 14, 2023 at 4:33
1

There are some sensible options:

Records in table y needs to be immutable; however, of course, they also need to be updateable.  So, you need versioning of records in table y of some sort.  Old table y records cannot be changed, but new records can be added with the same (product) identity yet different parameters.  Any consultation of the database uses the most recent entry of the records but existing orders are bound to the then-most-recent records.  Makes table y queries more complicated but this probably the most realistic approach — capture a row in table y for each version of something.  Of course, this only works if you have full control over table y and can enforce the no changes to existing versions, so only new version of some item can have changes, and all queries against table y must understand there may be multiple versions of any given item.

Alternatively, yes, copy data captured in time from table y into table x in addition to referencing the updateable foreign key from table x into table y.  You might also capture some date/time information or versioning information if possible, so one can glean that the information in table y has been updated since captured.

There may also be other options.  Which you choose depends on how much control you have over table y and its usage as well as the scenarios you want to support (such as being able to list the changes over time of an item in table y, or not, even regardless of table x).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.