I have the details of the members of my family in a database. I want to generate a web page with a family tree generated dynamically by reading from the table.
我在数据库中有我家人的详细信息。我想通过从表中读取来生成一个动态生成的族谱的网页。
My table schema looks like this
我的表架构看起来像这样
id(int) name father(int) mother(int) spouse(int) dateOfBirth
where father, mother, and spouse are referencing the id column of the same table. The root node will have null for father and mother.
其中父亲，母亲和配偶引用同一个表的id列。对于父亲和母亲，根节点将为null。
Given this data how can I go about dynamically generating the family tree. I am new at designing tables, so if this design is sub optimal kindly suggest another schema from which this objective can be achieved.
鉴于此数据，我如何动态生成族树。我是设计表的新手，所以如果这个设计是次优的，那么建议另一个可以实现这个目标的模式。
Any pointers on how to atleast get started would be highly appreciated.
关于如何开始至少入门的任何指示都将受到高度赞赏。
3 个解决方案
#2
0
Your design looks ok, but with this design it's easier to insert nodes than to get them out of the table.
你的设计看起来不错，但是通过这种设计，插入节点比将它们从表中移除更容易。
You can look at nested sets and implement that model. Nested sets are harder to update, but you can get the nodes of any subtree with a single query, so I think it matches your problem quite well (a family tree doesn't change too often :).
您可以查看嵌套集并实现该模型。嵌套集更难以更新，但您可以通过单个查询获取任何子树的节点，因此我认为它很好地匹配您的问题（家族树不会经常更改:)。
You would need some metadata, like relation type (Child, Sibling, Spouse) in addition to the nested sets parent-child relations, but I think you can add that easily.
除了嵌套集父子关系之外，您还需要一些元数据，如关系类型（Child，Sibling，Spouse），但我认为您可以轻松添加。
#3
0
This design is ok, but you would either select all the data and then build the tree on client side iteratively checking the returned array, or perform many subqueries, which is also not very good.
这个设计没问题，但你要么选择所有数据，然后在客户端构建树，迭代地检查返回的数组，或执行许多子查询，这也不是很好。
The best solution I know (for hierarchical structures, you've got spouses also) is storing the tree path in a string field. For example, you've got grandpa with id=1, children with id=2 and 3, 2 has children 4 and 5. Then, they have paths, respectively, "", "1", "1", "1,2", 1,2".
我知道的最佳解决方案（对于分层结构，您也有配偶）将树路径存储在字符串字段中。例如，你有一个id = 1的爷爷，id = 2和3的孩子，2有孩子4和5.然后，他们分别有路径，“”，“1”，“1”，“1， 2“，1,2”。
You can use this structure to retrieve the tree elements in order, using ORDER BY path clause.
您可以使用此结构按顺序检索树元素，使用ORDER BY路径子句。
#1
#2
0
Your design looks ok, but with this design it's easier to insert nodes than to get them out of the table.
你的设计看起来不错，但是通过这种设计，插入节点比将它们从表中移除更容易。
You can look at nested sets and implement that model. Nested sets are harder to update, but you can get the nodes of any subtree with a single query, so I think it matches your problem quite well (a family tree doesn't change too often :).
您可以查看嵌套集并实现该模型。嵌套集更难以更新，但您可以通过单个查询获取任何子树的节点，因此我认为它很好地匹配您的问题（家族树不会经常更改:)。
You would need some metadata, like relation type (Child, Sibling, Spouse) in addition to the nested sets parent-child relations, but I think you can add that easily.
除了嵌套集父子关系之外，您还需要一些元数据，如关系类型（Child，Sibling，Spouse），但我认为您可以轻松添加。
#3
0
This design is ok, but you would either select all the data and then build the tree on client side iteratively checking the returned array, or perform many subqueries, which is also not very good.
这个设计没问题，但你要么选择所有数据，然后在客户端构建树，迭代地检查返回的数组，或执行许多子查询，这也不是很好。
The best solution I know (for hierarchical structures, you've got spouses also) is storing the tree path in a string field. For example, you've got grandpa with id=1, children with id=2 and 3, 2 has children 4 and 5. Then, they have paths, respectively, "", "1", "1", "1,2", 1,2".
我知道的最佳解决方案（对于分层结构，您也有配偶）将树路径存储在字符串字段中。例如，你有一个id = 1的爷爷，id = 2和3的孩子，2有孩子4和5.然后，他们分别有路径，“”，“1”，“1”，“1， 2“，1,2”。
You can use this structure to retrieve the tree elements in order, using ORDER BY path clause.
您可以使用此结构按顺序检索树元素，使用ORDER BY路径子句。