近亲繁殖免疫数据库结构 [英] Inbreeding-immune database structure
问题描述
- 任何两个人如果来自相同的遗传学行为，将无法繁殖
- 需要允许添加新的家庭生活线（没有以前的家庭的新人）
- 需要能够通过以下方式单独拉扯兄弟姐妹，父母查询
我无法想出数据库的正确结构。到目前为止，我已经提出了两个解决方案，但是它们不是很可靠，可能会很快失控。
解决方案1涉及将family_ids字段放在人员表，并存储一个独特的家庭ids列表。每次两个人繁殖时，都会检查列表，以确保没有ids匹配，并且如果所有检出都将合并两个列表，并将其设置为小孩的family_ids字段。
示例：
父亲（family_ids :(（null））与母亲（family_ids：（213,519））一起饲养 - > ;
孩子（family_ids：（213,519））与随机人（family_ids：（813,712,122,767））一起饲养 - >
Grandchild（family_ids：（213,519,813,712,122,767））
等等...等等...我看到的问题是随着时间的推移，列表变得不合理的大小。
解决方案2使用cakephp的关联来声明：
public $ belongsTo = array（
'Father'=>数组（
'className'= "'User'，
'foreignKey'=>'father_id'
），
'Mother'=>数组（
'className'=>'User' ，
'foreignKey'=>'mother_id'
）
）;
现在设置递归到2将获取母亲和父亲的结果，以及他们的母亲和父亲，等等一路下来。该路由的问题是数据是嵌套数组，我不知道如何有效地处理代码。
如果有人能够引导我在最有效的方式来处理我想要实现的，将是非常有用的方向。任何和所有的帮助是非常感谢，我很乐意回答任何人有任何问题。非常感谢。
在SQL（更准确地说，RDBS）中，我将使用以下解决方案：
1）使用以下字段创建一个表 people 表 id name ， father_id ， mother_id 。第一个是典型的主键列，parent_id和mother_id引用此列，但是为NULL（允许添加新的系列）。
2）创建一个表亲属与以下字段 - person_id ， ancestor_id 。两者都不是NULL，它们都形成一个复合主键，对于 person.id 也是FK。
而已。不完全是！ ）现在考虑你的任务：
- 添加一些没有家庭的人
这也很可行： INSERT INTO people（name）VALUES（'some_name'）。诀窍是让另一个插入与这个新人相关的亲戚：
INSERT INTO亲戚VALUES（％new_person_id％，％new_person_id％）
这是什么？考虑最常见的任务：在表中添加实际上已经列出父亲和母亲的一些人。使用这种结构，它完成的简单（将相应的记录插入人之后，得到这个 person_id 作为结果）...
INSERT INTO亲戚
SELECT％new_person_id％，ancestor_id
FROM亲戚
WHERE person_id IN（％father_id％，％mother_id％）;
INSERT INTO亲属VALUES（％new_person_id％，％new_person_id％）;
- 任何两个人如果从相同的遗传线。
使用上述结构很简单：您必须在 ancestor_id 字段中具有相同的值。例如：
SELECT COUNT（*）
FROM relatives ra
INNER JOIN亲戚rb ON ra。 ancestor_id = rb.ancestor_id
WHERE ra.person_id =％person_a_id％
AND rb.person_id =％person_b_id％
在这个结构中寻找所有的祖先和孩子很容易;但是我仍然更喜欢去规范化的方法（即在第一个表中存储father_id和mother_id），以加快直接的父母/孩子的查找 - 实际上可以用第一个表单来完成。
这是一个工作（尽管有点短） SQL Fiddle 示例以更实用的颜色显示。 ）
I have an application that requires a "simple" family tree. I would like to be able to perform queries that will give me data for an entire family given one id from a member in the family. I say simple because it does not need to take into account adoption or any other obscurities. The requirements for the application are as follows:
- Any two people will not be able to breed if they're from the same genetic line
- Needs to allow for the addition of new family lines (new people with no previous family)
- Need to be able to pull siblings, parents separately through queries
I'm having trouble coming up with the proper structure for the database. So far I've come up with two solutions but they're not very reliable and will probably get out of hand quite quickly.
Solution 1 involves placing a family_ids field on the people table and storing a list of unique family ids. Each time two people breed the lists are checked against each other to make sure no ids match and if everything checks out will merge the two lists and set that as the child's family_ids field.
Example:
Father (family_ids: (null)) breeds with Mother (family_ids: (213, 519)) ->
Child (family_ids: (213, 519)) breeds with Random Person (family_ids: (813, 712, 122, 767)) ->
Grandchild (family_ids: (213, 519, 813, 712, 122, 767))
And so on and so forth... The problem I see with this is the lists becoming unreasonably large as time goes on.
Solution 2 uses cakephp's associations to declare:
public $belongsTo = array(
'Father' => array(
'className' => 'User',
'foreignKey' => 'father_id'
),
'Mother' => array(
'className' => 'User',
'foreignKey' => 'mother_id'
)
);
Now setting recursive to 2 will fetch the results of the mother and father, along with their mother and father, and so on and so forth all the way down the line. The problem with this route is that the data is in nested arrays and I'm unsure of how to efficiently work through the code.
If anyone would be able to steer me in the direction of the most efficient way to handle what I want to achieve that would be tremendously helpful. Any and all help is greatly appreciated and I'll gladly answer any questions anyone has. Thanks a lot.
In the SQL (more correctly, RDBS) I'd use the following solution:
1) create a table people with the following fields - id, name, father_id, mother_id. The first one is a typical primary key column, father_id and mother_id refer to this column but are NULLable (to allow addition of new family lines).
2) create a table relatives with the following fields - person_id, ancestor_id. Both are not NULL, both form a composite primary key, both also are FK for person.id.
And that's it. No, really! ) Now consider your tasks:
- add some people without family lines
That's also pretty doable: INSERT INTO people (name) VALUES ('some_name'). The trick is to make another insert related to this fresh person into relatives:
INSERT INTO relatives VALUES (%new_person_id%, %new_person_id%)
What's that for? Consider the most common task: add some person which actually has both father and mother listed in your tables already. With this structure it's done as simple as (after inserting the corresponding record into people, and getting this person_id as a result)...
INSERT INTO relatives
SELECT %new_person_id%, ancestor_id
FROM relatives
WHERE person_id IN (%father_id%, %mother_id%);
INSERT INTO relatives VALUES (%new_person_id%, %new_person_id%);
- any two people will not be able to breed if they're from the same genetic line.
With the structure described above it's rather simple: you have to look for two records in relatives that has the same value in ancestor_id field. For example:
SELECT COUNT(*)
FROM relatives ra
INNER JOIN relatives rb ON ra.ancestor_id = rb.ancestor_id
WHERE ra.person_id = %person_a_id%
AND rb.person_id = %person_b_id%
It's quite easy to look for all ancestors and children in this structure; but I'd still prefer de-normalized approach (i.e., storing father_id and mother_id in the first table) to speed up the look-up for direct parents/children - it actually can be done with the first table alone.
Here's a working (albeit a bit short) SQL Fiddle example to show this in more practical color. )
这篇关于近亲繁殖免疫数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
