I need to store a list of numbers, preferably into a single cell. I have a list of people each with a unique ID and I need to be able to who their family members are. I'm fairly new to SQL databases to try to keep it fairly simple please.
我需要存储一个数字列表，最好存储在一个单元格中。我有一个拥有唯一ID的人名单，我需要知道他们的家人是谁。我对SQL数据库相当陌生，我想尽量保持它的简单性。
3 个解决方案
#1
4
Do not do this: storing multiple values in the single column kills the possibility of using referential integrity constraints, and turns into a nightmare to maintain (imagine maintaining everyone's lists when a new baby's born!)
不要这样做:在单个列中存储多个值会降低使用引用完整性约束的可能性，并使维护成为一场噩梦(想象一下在新生儿出生时维护每个人的列表)。
The simplest solution is to add a column with a family_id to each person's row. All members of the same family will have this unique ID set to the same value; members of different families will have different family_ids.
最简单的解决方案是向每个人的行添加一个具有family_id的列。同一家族的所有成员都将这个唯一ID设置为相同的值;不同家庭的成员会有不同的家庭成员。
This is not ideal in cases of marriages, unless you are willing to either join the two families on marriage of their children, or move the bride or the groom to the family of the spouse.
这在婚姻中并不理想，除非你愿意加入两个家庭为他们的孩子结婚，或者将新娘或新郎移到配偶的家庭。
#2
3
You're going to want to setup a Family table with two columns, one to store the unique ID of the person in question, and another to store the unique ID of their family member. Your table will end up looking like this:
您将需要设置一个包含两列的家族表，一个用于存储相关人员的惟一ID，另一个用于存储其家族成员的惟一ID。你的桌子会变成这样:
Family(userID, familyMemberID)
家庭(userID,familyMemberID)
and there will be an entry for each family member so it could look something like this:
每个家庭成员都有一个条目，所以看起来是这样的:
(1, 2), (1, 3), (1, 4), (1, 5), (2, 8)
(1、2)、(1、3)、(1、4)、(1、5)、(2、8)
It's up to you to decide you want to store the connection both directions (i.e. for every insert, you also insert the reciprocal (1,2) also inserts (2,1)) which can easily be accomplished via triggers or other means.
由你来决定你想要存储连接的两个方向(也就是每个插入，你也插入倒数(1,2)也插入(2,1))，这很容易通过触发器或其他方法来完成。
#3
0
Adding the familyId is the simplest solution.
添加familyId是最简单的解决方案。
However, since the members of the family changes over time as children get married and form a new family. I would create a separate "relationship" table, which contains personId and familyID.
然而，由于家庭成员随着时间的推移会发生变化，孩子们会结婚，组成一个新的家庭。我将创建一个单独的“关系”表，其中包含personId和familyID。
If you want to track member movements. In a data warehousing world, you typically have a relId, relTy and Timestamp to preserve history, but you don't have to go that far.
如果你想跟踪成员的动作。在数据仓库的世界中，通常有一个可靠的、可靠的和时间戳来保存历史，但是您不需要走那么远。
Perhaps one day, you want to create a family tree. So decide if you really want a personId/familyId as column names in your relationship table.
也许有一天，你想创建一个家谱。因此，确定您是否真的希望在您的关系表中作为列名使用personId/familyId。
In summary, for your immediate need I will go with a separate relationship table.
总而言之，为了您的迫切需要，我将使用一个单独的关系表。
#1
4
Do not do this: storing multiple values in the single column kills the possibility of using referential integrity constraints, and turns into a nightmare to maintain (imagine maintaining everyone's lists when a new baby's born!)
不要这样做:在单个列中存储多个值会降低使用引用完整性约束的可能性，并使维护成为一场噩梦(想象一下在新生儿出生时维护每个人的列表)。
The simplest solution is to add a column with a family_id to each person's row. All members of the same family will have this unique ID set to the same value; members of different families will have different family_ids.
最简单的解决方案是向每个人的行添加一个具有family_id的列。同一家族的所有成员都将这个唯一ID设置为相同的值;不同家庭的成员会有不同的家庭成员。
This is not ideal in cases of marriages, unless you are willing to either join the two families on marriage of their children, or move the bride or the groom to the family of the spouse.
这在婚姻中并不理想，除非你愿意加入两个家庭为他们的孩子结婚，或者将新娘或新郎移到配偶的家庭。
#2
3
You're going to want to setup a Family table with two columns, one to store the unique ID of the person in question, and another to store the unique ID of their family member. Your table will end up looking like this:
您将需要设置一个包含两列的家族表，一个用于存储相关人员的惟一ID，另一个用于存储其家族成员的惟一ID。你的桌子会变成这样:
Family(userID, familyMemberID)
家庭(userID,familyMemberID)
and there will be an entry for each family member so it could look something like this:
每个家庭成员都有一个条目，所以看起来是这样的:
(1, 2), (1, 3), (1, 4), (1, 5), (2, 8)
(1、2)、(1、3)、(1、4)、(1、5)、(2、8)
It's up to you to decide you want to store the connection both directions (i.e. for every insert, you also insert the reciprocal (1,2) also inserts (2,1)) which can easily be accomplished via triggers or other means.
由你来决定你想要存储连接的两个方向(也就是每个插入，你也插入倒数(1,2)也插入(2,1))，这很容易通过触发器或其他方法来完成。
#3
0
Adding the familyId is the simplest solution.
添加familyId是最简单的解决方案。
However, since the members of the family changes over time as children get married and form a new family. I would create a separate "relationship" table, which contains personId and familyID.
然而，由于家庭成员随着时间的推移会发生变化，孩子们会结婚，组成一个新的家庭。我将创建一个单独的“关系”表，其中包含personId和familyID。
If you want to track member movements. In a data warehousing world, you typically have a relId, relTy and Timestamp to preserve history, but you don't have to go that far.
如果你想跟踪成员的动作。在数据仓库的世界中，通常有一个可靠的、可靠的和时间戳来保存历史，但是您不需要走那么远。
Perhaps one day, you want to create a family tree. So decide if you really want a personId/familyId as column names in your relationship table.
也许有一天，你想创建一个家谱。因此，确定您是否真的希望在您的关系表中作为列名使用personId/familyId。
In summary, for your immediate need I will go with a separate relationship table.
总而言之，为了您的迫切需要，我将使用一个单独的关系表。