Mysql – How to design table to allow marking records as related to each other

database-designforeign keyMySQLprimary-key

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?

Best Answer

A bridge table

related_id  Related_TO_ID
     13           2
     13          12

If 1-->2 is equal to 2-->1 you need one row else you need two.

Now You can get all ids that are related to 13

And if you want to exclude some ration ships you can add a column that indicates that 13 is never related to 42

SELECT Related_TO_ID FROM brdgetable WHERE related_id   = 13

Why you never should save data in delimitedcolumns you can read here