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. Commented 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 ? Commented Feb 6, 2020 at 7:54
  • @Christophe yes, that is correct Commented 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. Commented 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. Commented 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. Commented 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. Commented Feb 7, 2020 at 6:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.