My web app’s main MySQL table has tens of thousands of rows. Pretend it has 2 columns: ID, Content.
I’m trying to design a new feature that allows rows to be marked as related to each other.
Obviously I could add a 3rd column: Related_IDs. It could either be a JSON array or could be a simple CSV string.
But that feels wrong. I’d need to update the Related_IDs field of each row of a group of N rows to be a comma-separated list of IDs of all of the rows in the group except itself. Every time any one of those records becomes unrelated, I’d need to iterate through the group and clean up that field for each.
What is a smarter approach?