I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them
col2, both being foreign keys to their respective tables, and also composite primary keys.
Before this table grows any further, I want to add an
id column (
NOT NULL AUTO-INCREMENT PRIMARY KEY), and instead let
col1, col2 have a composite unique constraint.
The reasons for me wanting to do this lies in the business logic.
Is there anything I should be particularly aware of before doing this? I'm going back and forth as to which order I should do it in.
Any insight appreciated!
The added column will of course increase required storage space. This is not an issue.
Bandwidth and overhead in terms of CRUDing towards the table is also accounted for.
Setup: MySQL / InnoDB
The comments have been deleted, but there were some people asking why I wanted to make this change, and told me that it was a stupid change to make.
I'll answer it to prevent the XY problem (even though I don't think it's necessary in this case), and because the question has remained answerless thus far.
The reason I want to make this change, is because this link table will be linked to, from at least one other table, namely a change log.
Consider the change log table having the columns:
id, col1 (FK), col2 (FK), user_id, action, timestamp. Now, if I want to have a changelog, I'm duplicating the entire original link table by virtue of having both
At this point, it's an even trade. Saved the space in the link table, wasted the same amount of space in the change log table.
Now, who's to say I'll never need another table to link to it? That would be a pretty big assumption to make.