Keep data correct after database-structure change

Question

Right now I have the following table:

+----------------+
| Sale table |
+----------------+
| ID |
| sale |
| ... |
| receiver_name |
| receiver_email |
| ... |
+----------------+

The important columns are receiver_name and receiver_email. The receiver is the person 'receiving' the object of a sale. Until now it was only possible to receive an object by mail. But in the next update it will also be possible to receive that object by other communication methods like for example SMS.

So for each sale the receiver of the sold object can select a communication method. For this question let's only consider e-mail and SMS. The new database-structure will have a extra table.

+----------------+
| Receiver table |
+----------------+
| ID |
| receive_method |
| receiver_name |
| receiver_email |
| receiver_telnr |
+----------------+

The updated sale table will have a foreign key to the new Receiver table:

+----------------+
| Sale table |
+----------------+
| ID |
| sale |
| ... |
| receiver_id |
| ... |
+----------------+

This is necessary because my business logic can't know what method the user selected. Depending on the value of receive_method having the domain ("email", "SMS") I'll collect the data from column receiver_email (if receive_method == "email") or from column receiver_telnr (if receive_method == "SMS").

I think this logic is pretty solid.

The problem, however, is that the Sale table is already filled with data! So I need a way to put the values of receiver_name and receiver_email from the old Sale table into the new version of the Sale table. And put the correct foreign key in place so the new Sale table still contains the information of the old sales before the update.

To complete this update, this are the steps I will take:

1. Create the new Receiver table
2. Put the values of receiver_name and receiver_email from the old Sale table into the new Receiver table.
3. Add the column receiver_id to the Sale table.
4. (Here I am stuck...)
5. Delete receiver_name and receiver_email from Sale table because it is referenced by the new column receiver_id.

I am stuck at step 4 though. How do I insert the foreign key in the new column receiver_id so the data stays correct?

• Rick James: Is method an ENUM? Or a SET? Your table names don't 'speak to me'. Seems like you have "customers" and "sales"? And the question is how to record that a particular customer has been sent notice of a sale?

Answer 1

You have to update your Sale table in three steps.

• First you have to add the receiver_id field as FK.
• Next you have to fill up Receiver table with data from receiver_name and receiver_email. Some denormalization emerge at this step because of data duplication.
• Finally you have to drop receiver_name and receiver_email from Sale.

• O'Niel: But the question remains: In your second step, when I fill up Receiver with the data from receiver_name and receiver_email, how do I link the ID of the newly inserted rows to column receiver_id?
• Kondybas: Sale and Receiver tables are in 1:1 relation - each row of Sale should have a single corresponding row in Receiver and vv. So you can create an empty Receiver table with IDs from Sale and use it for JOIN.