I do have a noob question here. When the migration was done and I’m assuming you had a branch table already made. How does the new workers table know which branch Id to associate with a worker during the migration? Is this something where you update each row of the workers table with the correct id or is this some automagical thing that happens during the migration
Great question, actually, and the answer is 'automagic!'.
When you create an association between tables, a foreign Key (like, say, the branchId stored in the workers table) is used to explain that relationship. That's the basis of the name of the relational database paradigm.
The Sequelize docs, for example, explain it like this: "these (table association) calls will cause Sequelize to automatically add foreign keys to the appropriate models (unless they are already present)."
I have been thinking about this and i feel like i maybe don't fully grasp what you are trying to explain, or maybe it is because i am not phrasing my question correctly. Let me try again :)
The workers table gets its managerID column renamed to branchID and the constraint pointing to the managers table is removed, while a new constraint to the branch table is put in place.
Here's where i am confused, the workers table didn't have its old rows deleted because you allude to that in the section where you talk about the old managerIDs being present, however, it also seems that 'automagically' those old rows get somehow updated (duplicated maybe?) but this time with the right branchID which is why truncating the old data works.
My confusion is whether this new (updated) rows were automagically assigned foreign keys that were present in the branch table (id column) and if so how does sequelize know which managerID to assign to which row in the workers table, without first making a connection to the outgoing managerID foreign key.
I hope this better explains my misunderstanding :(
Oh heyyyy. Sorry I kept meaning to return to this, but ultimately never did.
In my real-world example, the damage wasn't significant, so I just truncated the workers table (ie, deleted all the entries in the table). In other words, truncation *is* deletion. So there were no 'old' rows to speak of.
The process was.
1. Remove the old, no-longer-applicable foreign key contraint (managerID).
2. Add the new, updated foreign key constraing (branchID).
3. Purge (ie, truncate) the table of old data.
4. Run a new database migration.
If you wanted to reassign the old workers table entries to new branches (instead of truncating them), you could create and run a table seed.
There is, however, a superior alternative if you want to keep your data from before (ie, not truncate the database): you could normalize the table instead:
In this case, since we now have two types of entities responsible for the workers (managers in the past, and branches in the present), we can create a separate table with the following metadata:
1. id
2. Name of table: "reportsTo"
3. workerId
4. reference (an enum; one of "manager" or "branch")
5. referenceId
Then we update the workers table — (instead of managerId or branchId, we have a reportsTo column, which stores the id of an entry in the reportsTo column).
So now we can keep the old managerIds, and use the new branchIds, and when we want to know who a worker reports to, we make a query like this:
PS: there may be some bugs in my query as mapping out many-to-many relationships off the cuff does my head in. But it's here as an illustration of how you could set this up if you wanted to persist old and new data via a table of foreign keys.
I do have a noob question here. When the migration was done and I’m assuming you had a branch table already made. How does the new workers table know which branch Id to associate with a worker during the migration? Is this something where you update each row of the workers table with the correct id or is this some automagical thing that happens during the migration
Great question, actually, and the answer is 'automagic!'.
When you create an association between tables, a foreign Key (like, say, the branchId stored in the workers table) is used to explain that relationship. That's the basis of the name of the relational database paradigm.
The Sequelize docs, for example, explain it like this: "these (table association) calls will cause Sequelize to automatically add foreign keys to the appropriate models (unless they are already present)."
Read more here -> https://sequelize.org/docs/v6/core-concepts/assocs/#defining-the-sequelize-associations
Hope this helps!
I have been thinking about this and i feel like i maybe don't fully grasp what you are trying to explain, or maybe it is because i am not phrasing my question correctly. Let me try again :)
The workers table gets its managerID column renamed to branchID and the constraint pointing to the managers table is removed, while a new constraint to the branch table is put in place.
Here's where i am confused, the workers table didn't have its old rows deleted because you allude to that in the section where you talk about the old managerIDs being present, however, it also seems that 'automagically' those old rows get somehow updated (duplicated maybe?) but this time with the right branchID which is why truncating the old data works.
My confusion is whether this new (updated) rows were automagically assigned foreign keys that were present in the branch table (id column) and if so how does sequelize know which managerID to assign to which row in the workers table, without first making a connection to the outgoing managerID foreign key.
I hope this better explains my misunderstanding :(
Oh heyyyy. Sorry I kept meaning to return to this, but ultimately never did.
In my real-world example, the damage wasn't significant, so I just truncated the workers table (ie, deleted all the entries in the table). In other words, truncation *is* deletion. So there were no 'old' rows to speak of.
The process was.
1. Remove the old, no-longer-applicable foreign key contraint (managerID).
2. Add the new, updated foreign key constraing (branchID).
3. Purge (ie, truncate) the table of old data.
4. Run a new database migration.
If you wanted to reassign the old workers table entries to new branches (instead of truncating them), you could create and run a table seed.
There is, however, a superior alternative if you want to keep your data from before (ie, not truncate the database): you could normalize the table instead:
In this case, since we now have two types of entities responsible for the workers (managers in the past, and branches in the present), we can create a separate table with the following metadata:
1. id
2. Name of table: "reportsTo"
3. workerId
4. reference (an enum; one of "manager" or "branch")
5. referenceId
Then we update the workers table — (instead of managerId or branchId, we have a reportsTo column, which stores the id of an entry in the reportsTo column).
So now we can keep the old managerIds, and use the new branchIds, and when we want to know who a worker reports to, we make a query like this:
SELECT *
FROM workers w
INNER JOIN reportsTo rt on w.id = rt.workerId
INNER JOIN branch b on rt.referenceId = b.id
WHERE b.reference = 'branch'
PS: there may be some bugs in my query as mapping out many-to-many relationships off the cuff does my head in. But it's here as an illustration of how you could set this up if you wanted to persist old and new data via a table of foreign keys.
Amazing read, Justin!