SQL Database for related family members

Question

I am curious what you think is the best way to get this MySQL database to work.

i had this table:

family.id
family.name
family.related

And thought it wouldnt be a problem to connect related family members together in 1 query like this:

id        | name        | related
1         | Name1       | 
2         | Name2       | 3,5
3         | Name3       | 
4         | Name4       | 
5         | Name5       | 4,1
6         | Name6       | 
7         | Name7       | 
8         | Name8       | 6
9         | Name9       | 7

So Name2 is related to Name3 and Name5, where Name5 have other related id's, so i should make a query to get the following id's:

Selecting id 2, outputs related id's: 3,5,4,1

Selecting id 6, outputs related id's: 8

Selecting id 9, outputs related id's: 7

But i cant find a query to get all the related id's in one column.

Is it even possible?

Answer 1

For a many-to-many relationship, you'll generally need to use a linking table:

family_id
related_to

Where, when you would have several comma-delimited items in your family table, you would instead have one entry in the linking table for each item that would be in the comma-delimited list:

family_id | related_to
2           3
2           5
5           4
5           1
...

And so on.