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

Related Solutions

Mysql – One to many to many

You can simplify that specific query:

SELECT COUNT(1)
FROM results r 
JOIN steps s 
    ON s.result_id=r.id
WHERE r.monitor_id = 64 
  AND r.completed >= '2014-10-01' 
  AND r.completed <= '2014-10-03'

If you don't have it already, try indexes like:

CREATE INDEX ... ON results (monitor_id, completed, id);
CREATE INDEX ... ON steps (result_id, id);

Mysql – Have several rows, or one single row with a huge comma separated data

Use 1 row per ID, add an index on the ID. SQL is pretty good about finding a single value in a table, even if there are millions of rows. If you run periodic cleanup to wipe the old ID values out (like maybe have an automatic timestamp column and then have a job to drop all values older than X days), you wont run into any performance issues.

To give you an idea, in the production environment I work with, we have a similar table, about 40 columns wide, with some instances having >50 billion records, and pulling a single record out, queried by its unique, indexed ID, takes around 0.5s. Its very good hardware but its still pretty performant.

Best Answer

Related Solutions

Mysql – One to many to many

Mysql – Have several rows, or one single row with a huge comma separated data

Related Question