1

I have data spread across two tables shipments and shipment_data. Currently I have the standard auto increment primary key id for shipments and a manually assigned unique primary key id for shipment_data. The primary key on shipment_data is also a foreign key referencing id on shipments.

In my mind this seems more efficient than making shipment_data.id auto increment and adding a shipment_data.shipment_id foreign key, since they will, as long as everything works correctly, have the same value as both will be created at the same time.

But I have a creeping feeling that there might be something I'm overlooking.

Improve this question
6
  • 6
    This is a known pattern if you have a 1:0..1 relationship. But if the relationship is 1:1 you should maybe consider if you even need two separate tables.
    – JacquesB
    Feb 6, 2020 at 7:29
  • Just to be sure that I do not misunderstand: 1) you create shipments and get the auto-incremented shipment.id 2) you copy shipment.id into shipment_data.id, so that from the point of view of their value, the primary keys are equal for two corresponding records ?
    – Christophe
    Feb 6, 2020 at 7:54
  • @Christophe yes, that is correct
    – kaan_a
    Feb 6, 2020 at 8:01
  • 1
    @JacquesB shipment_data contains a bunch varaible length of json fields whereas everything in shipments is fixed width. Both are created simultaneously but there is often no need to query both. Whether or not I should have seperated the tables is another thing I'm somewhat concerned about but it's a separate question.
    – kaan_a
    Feb 6, 2020 at 8:06
  • Ideally your application would always, always, specify what fields you query. See Why is SELECT * considered harmful?. Baring that, you can create multiple views over a single table.
    – Theraot
    Feb 6, 2020 at 8:56

1 Answer 1

Reset to default
5

Foreign key mapping is a proven technique for implementing a one-to-many relationship (not mandatory, i.e. 1 - 0..*). So having a foreign key in shipment_data that refers to shipment's primary key shipment.id would allow no, one, or many shipment_data for a specific shipment.

Using the foreign key as a primary key is possible. It requires you to first create the a shipment then the shipment_data reusing the same id, and it structurally constrains the relationship to an one-to-one (not mandatory, i.e. 1 - 0..1).

This is a reasonable solution, if you are sure that the relationship shall not evolve to become one-to-many. It also works in case shipment_data creation could be delayed (for example if the shipment is first planned, and shipment_data would only be filled once the final package weight was measured and the parcel tracking number was issued by the carrier).

Improve this answer
2
  • 1
    if you are sure that the relationship shall not evolve to become one-to-many it's pretty simple to migrate this into one-to-many relationship. If you end up needing this relationship to be one-to-many, you can just duplicate the column, convert the primary key column to be autoincrement, and adjust the foreign key constraint.
    – Lie Ryan
    Feb 7, 2020 at 1:59
  • 1
    @LieRyan yes, I agree. You just have to make sure that you spot hidden assumptions of key sharing in sql queries. I was thinking also from an operational point of view, since you’ll have to schedule a downtime for your system, run the db script, and deploy your new version (is it on a central node, or does it run on many remote nodes?) before you get it up and running again. Nothing complex, but depending on the architecture, a little more risky to organise compared to having already the column and just relaxing the constraint on how it is used.
    – Christophe
    Feb 7, 2020 at 6:54

Your Answer

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

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