族谱树mysql [英] Genealogy tree mysql

问题描述

我正在尝试创建一种结构，以使用php和mysql存储和读取狗的血统书.

I'm trying to create a structure to store and read dogs pedigree using php and mysql.

我在stackoverflow上发现了这种结构，它似乎很有效: Inbreeding-immune数据库结构

I found on stackoverflow this kind of structure, and it seems to be efficient: Inbreeding-immune database structure

TABLE people (id, name, father_id, mother_id );
TABLE relatives ( person_id, ancestor_id );

此处有一个有效的示例: http://sqlfiddle.com/#!2/0bd39/10

There is a working example here: http://sqlfiddle.com/#!2/0bd39/10

是否可以仅检索以id开头的有序树或子树(例如4或5代)?

Is it possible to retrieve simply a ordered tree or a subtree (for example 4 or 5 generations) starting by an id?

编辑

我正在尝试通过使用第一个表获取数据...但是对于4-5代，结果查询非常繁琐.我担心数据库中有大量信息，获取家谱可能会非常缓慢且无法使用.

I'm trying to get the data from using the first table...but with 4-5 generations the resulting query is very very heavy. I fear that with a large amount of information in the DB, getting the genealogy could be to much slow and unusable.

SELECT 
    t1.name AS lev1, t2.name as f, ff1.name as ff1, fm1.name as fm1, t3.name as m, 
    mf1.name as mf1, mm1.name as mm1, .......
FROM people AS t1
LEFT JOIN people AS t2  ON t2.id = t1.father_id
 LEFT JOIN people AS ff1 ON ff1.id = t2.father_id
 LEFT JOIN people AS fm1 ON fm1.id = t2.mother_id
   ...
LEFT JOIN people AS t3  ON t3.id = t1.mother_id
 LEFT JOIN people AS mf1 ON mf1.id = t3.father_id
 LEFT JOIN people AS mm1 ON mm1.id = t3.mother_id
    ...
WHERE t1.id = 6;

推荐答案

这种方法可能更适合

This sort of this is probably better suited for a graph style of data store. Something akin to how facebook keeps hierarchies of relationships.

如果您被束缚并决心使用MySQL，则可能可以通过使用递归搜索来摆脱模式.由于树的深度可以变化，因此您可以在给定的位置开始自我连接，然后递归"分支递归，直到找不到后代为止.返回该分支并开始下一个分支.遍历寻找父母的过程类似.

If you are bound and determined to use MySQL you could probably get away with your schema by using a recursive search. Since your tree can be of variable depth you could start self-joining at given location and 'walk' down a branch recursing until you didn't find anymore descendants. Return that branch and start down the next one. Similar process for traversing up to find parents.

这篇关于族谱树mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！