相关家庭成员的SQL数据库 [英] SQL Database for related family members

问题描述

我很好奇您认为使此MySQL数据库正常工作的最佳方法。

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

我有这张桌子：

family.id
family.name
family.related

并认为在1个查询中将相关家庭成员连接在一起不会是一个问题：

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

所以Name2与Name3和Name5相关，其中Name5具有其他相关ID，所以我应该查询以获取以下ID：

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:

选择ID 2，输出相关ID：3,5， 4,1

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

选择id 6，输出相关ID：8

Selecting id 6, outputs related id's: 8

选择id 9，输出相关ID：7

Selecting id 9, outputs related id's: 7

但是我找不到一个查询来将所有相关的ID放在一栏中。

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

是否有可能？

推荐答案

对于多对多关系，通常需要使用链接表：

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
...

以此类推。

这篇关于相关家庭成员的SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！