首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
mebtte
V2EX  ›  MySQL

mysql 保存族谱应该怎么设计?

  •  
  •   mebtte · 21 天前 · 3726 次点击
    58 条回复    2020-05-14 09:17:02 +08:00
    l1nyanm1ng
        1
    l1nyanm1ng   21 天前   ❤️ 1
    百度 ❌
    谷歌 ❌
    打开 V2EX 来和问问题(实则借机开始划水 ✔️
    wangkun025
        2
    wangkun025   21 天前
    people
    relationships
    person_relationships
    mebtte
        3
    mebtte   21 天前
    @l1nyanm1ng 猜对了
    mebtte
        4
    mebtte   21 天前
    @wangkun025 要支持正反向查询, 比如爷爷的孙子们 /孙子的爷爷
    xupefei
        5
    xupefei   21 天前 via iPhone   ❤️ 1
    你需要的应该是 RDF 。
    annielong
        6
    annielong   21 天前
    基本的都是递归,id,name,upid
    2exploring
        7
    2exploring   21 天前
    一张人表,一张父子关系表,不就结了
    wangkun025
        8
    wangkun025   21 天前   ❤️ 1
    @mebtte 加需求要加钱的
    superrichman
        9
    superrichman   21 天前 via iPhone
    可以参考《 sql antipatterns 》这本书第三章来设计存储和读取树形结构。
    mebtte
        10
    mebtte   21 天前
    @2exploring 隔了好几代不好查询
    yangliulnn
        11
    yangliulnn   21 天前 via Android
    左右值了解一下
    egfegdfr
        12
    egfegdfr   21 天前
    在 id,name,upid 的基础上在加一个 path 的字段、用来记录这个当前节点的所有父节点。
    hantsy
        13
    hantsy   21 天前
    这样 100%用 Neo4j 方便啦。
    hteen
        14
    hteen   21 天前 via iPhone
    闭包表
    FFFire
        15
    FFFire   21 天前
    建议使用 Excel
    k9990009
        16
    k9990009   20 天前 via Android
    不就是树嘛,除了父 ID,再加上父路径 1,3,5,…… 想怎么查都阔以
    Keyes
        17
    Keyes   20 天前 via iPhone
    爸爸辈太多的话,查孙子会比较吃性能,我只想到了买个更好硬件的 server

    然后:图数据库就干这个事的
    izoabr
        18
    izoabr   20 天前
    我觉得 JSON 也挺合适的,有结构且明文
    Solarest
        19
    Solarest   20 天前 via Android
    参考图数据库的设计方式,分表存:实体 a 、实体 a 和 b 的关系、实体 b 。
    tulongtou
        20
    tulongtou   20 天前 via iPhone   ❤️ 3
    感觉不用设计想怎么存怎嘛存,族谱能有多少人?几百几千?哪怕上万了,也不需要设计啊,这个数量级怎么存都没毛病
    ericgui
        21
    ericgui   20 天前
    族谱就是一个 tree
    你考虑一下
    lights
        22
    lights   20 天前
    如果频繁间隔很多代际查询的话,建议上图数据库,不推荐 OrientDB (我被它坑得死去活来)
    可以考虑 Mysql 层面模仿图数据库的存储结构(如 19 楼所说)

    另外因为“关系数据”内容量很小(甚至不会超过 50MB ),可以每次将关系数据从 Mysql 中缓存到内存,然后每次查询直接在内存中用图算法算,再将获取到的关系结果到 MySQL 中查询具体的数据(有现成的图查询库可以找找看)
    lumotian
        23
    lumotian   20 天前
    mongodb
    levelworm
        24
    levelworm   20 天前 via Android
    最高多少代,直接所有数据 dump 出来存储。。。
    ebony0319
        25
    ebony0319   20 天前 via Android
    多一列:祖先是谁。这样方便认祖归宗(查并集)。比如第五代与十五代,只要祖宗相同说明是同族。
    fxxwor99LVHTing
        26
    fxxwor99LVHTing   20 天前
    如果数据量不大,那么直接就一棵树就行,
    表结构,一张表也行吧,
    id,parentId
    查询的时候一次性把同族的所有数据拿出来,然后根据 parentId 把各个节点链接成一棵树,同时把节点在 map 里面根据 id 存一份,这样查询就很快了,
    entity 里面两个属性 parentNode, 和 childrenNodes,父节点只有一个,但是子节点可能有多个
    wei193
        27
    wei193   20 天前 via Android
    用两个字段,一个字段记录代数,一个字段记录关系树,比如 1/2/4/8 。那么你可以用 Like 1/2/% AND 4 代 来找他的孙子。当年我是这样做的,有更多需要可以啊。
    wei193
        28
    wei193   20 天前 via Android
    @wei193 有完整的设计,包括直接生成电子书,自定义字体这一些。😂😂😂
    tankren
        29
    tankren   20 天前
    纸质版的族谱看起来才有意思
    encro
        30
    encro   20 天前
    非常有意思的题目,
    昨天想了很久,
    觉得没有很好答案。

    今天忽然想到的:
    族谱有一个特点就是写入少,
    所以可以建立一个缓存表,
    缓存关系链。

    比如:

    people 表:
    id,
    name,
    father_id,
    mother_id,
    order,

    relation 表:
    id,
    from_id,
    to_id,
    type


    这里 people 是常规的做法就好,
    people 新增和修改的时候,维护好 relation 表上下的关系。
    这样查询的时候自动根据 relation 以及 type 就能一次查到所有关联关系。
    12tall
        31
    12tall   20 天前
    数据部太多的话,有一种想法,数据表结构
    id, name, p_id
    然后将查询结果保存到哈希表常驻内存,也可以再构造一个树,个人觉得比较适合不常变的数据

    反模式里面的闭包表也用过,就是一个自己关于自己的中间表,增删改时会相对复杂些,如果存很多份族谱的,这种应该比较合适
    encro
        32
    encro   20 天前
    people 表可以扩展一下:
    id,
    family_id, // 族号
    name,
    father_id,
    mother_id,
    order, // 第几个孩子
    level, // 第几代


    relation 表也可以扩展下:
    id,
    from_id,
    to_id,
    type,
    level, // 相对层级 可以为负数
    xuanbg
        33
    xuanbg   20 天前
    数据结构好说,麻烦的是关系。假设曹丞相收了吕布女儿为妾,吕布认曹丞相为父……

    你说这曹氏族谱该怎么写
    dog82
        34
    dog82   20 天前
    简单的就 id parent_id 递归查询就行,mysql postgre oracle 都行
    encro
        35
    encro   20 天前
    @wangkun025

    才发现 2 楼就有利用关系表的意思了,不过当时没有看清楚。

    前面有说到用 path 的办法,如果层级不多是可以的,层级多了,索引太长吧,如果真追溯到祖宗 5 代以上。

    其实前期数据不多,可以不要 relation 表,有 family_id,level 就可以了,根据 family_id 和 level 就可以查出上下几代的数据,用程序运算也很快的。
    encro
        36
    encro   20 天前
    @xuanbg

    笑死了。。
    gamexg
        37
    gamexg   20 天前
    我想起了一个族谱软件作者的吐槽

    客户反馈族谱软件挂了,作者检查后发现,爸爸的妻子是女儿(记不清了,类似的关系),死循环了。

    不知道你这里是否需要考虑这种少见的关系。
    laminux29
        38
    laminux29   20 天前
    论各种数据结构如何转化为数据库的表与关系。
    encro
        39
    encro   20 天前   ❤️ 1
    create index family_level(family_id,level);

    select * from people where family_id=3 and level between current_level-3,current_level+3;

    这样高性能将上下三代查出来了。

    这样看来不要 relation 表也是可以的。
    hbolive
        40
    hbolive   20 天前
    @tankren 产品经理:让设计加下班,你们后端也要考虑下,让电脑和手机上看起来都要有纸质版的感觉。。
    ylsc633
        41
    ylsc633   20 天前
    我也考虑过 做一个族谱! 因为我爸正在把 老祖宗传下来的族谱进行 图形化!

    我再配合一个网站 就完美了!

    族谱除了简单的 树形结构!

    其实还有 其他的情况! 比如 某某 过继 给 某某! 那么他就会有两个父级! 甚至多个! 这种情况在族谱里经常出现!

    还是这种情况 年轻时过继给某某! 去世后 迁回, 还属于出生的那一支....


    另外 图形化的 我参考过 那个 全历史 感觉不错! 可惜不会写
    tankren
        42
    tankren   20 天前
    @hbolive 哈哈 展示不是前端的活吗
    hcymk2
        43
    hcymk2   20 天前
    软件真的好难做啊 coolshell.cn/articles/4811.html
    kiracyan
        44
    kiracyan   20 天前
    @ylsc633 这个过继的关系可以区分关系类型吧 比如首先考虑直系血亲 然后过继 领养等其他的关系可以区分开来
    x66
        45
    x66   20 天前
    左右值
    legiorange
        46
    legiorange   20 天前
    @ylsc633 考虑过,这个时候关系本身的冗余设计体现了真正的用途。和 44 楼说的一样,但维持原来的父关系是必要的(溯源),再增加一个关联关系字段似乎是稳妥的方案。
    比如 两个父,一个母,异父同母的孩子 X2,看似就可以妥善的解决了?
    Baelish725
        47
    Baelish725   20 天前
    这种用图数据库比较方便,不用考虑设计复杂的数据结构,图数据库就是为了解决这种社交关系、环状、树状的关系设计的
    00chang
        48
    00chang   20 天前
    @hcymk2 我的软件没有 bug,是你的生活有 bug 手动狗头
    justfindu
        49
    justfindu   20 天前
    左右值树形结构就行了
    starcraft
        50
    starcraft   20 天前
    邻接表就行。别去存什么完整路径,顶多加个 level,数据少的可怜的话,连这个都不用,反正直接递归就完事了。注意个环检测。尽管 mysql 是 8 加入的 cte,但老版本也能自己模拟,就是性能肯定比不上原生支持。
    aguesuka
        51
    aguesuka   20 天前 via Android
    @encro close table
    guolaopi
        52
    guolaopi   20 天前
    @encro #39
    使用 level 真的很巧妙,这样的话要找任何一代的上下 X 代,就从他的 level +- X 即可。
    太棒了思路!
    我还在想怎么找爷爷的爷爷辈,这样只要找到爷爷的 id,然后 level+3 即可,无需递归。

    但是存在一个缺陷,祖先元素( root )发生变化后所有的 level 是不是都要变化?

    假设现在祖先是 A,最顶级的元素,按这个设计 A 的 level 应该是 0 。
    假若通过别的某些途径证明 A 上面还有 AAA 是 A 的父级,那么此时 AAA 就应该是族谱里的祖先,
    如果是这样的话 level 应该怎么搞,-1 吗?
    encro
        53
    encro   20 天前
    结合大家说的变更问题,那么需要将关系抽离出来:


    people 表:
    id
    name 姓名


    relation 表:
    id
    family_id 族号 一个用户可能存在多个族谱中,比如外嫁后进入其他族谱
    people_id 用户编号
    level 代系
    relate_people_id 关联人,
    type 关系:如丈夫,妻子,儿子,孙子,情人
    order 第几个孩子,第几任妻子


    alter table relation create index family_level(family_id,level);

    select * from relation where family_id=3 and level between current_level-3,current_level+3;

    这样高性能将上下三代查出来了。

    这两个表记录基本不用改(除非录错了),任何时候都是增加记录即可,所以维护也非常简单。

    界面展示上,查询出上下几代,然后名字作为主要展示标签,关系用连线展示,关系和人是多对多,完美解决关系混乱的问题。

    这里一个人可能即是二代也是三代,是两个身份,所以算两个点。

    不知道怎么 v2 贴图,参考图我先放我自己 blog 上了。

    族谱的数据设计 https://c4ys.com/archives/2141
    encro
        54
    encro   20 天前
    @guolaopi

    如果新增加了超祖辈,子代数都+1 。
    encro
        55
    encro   20 天前
    level 代表是族谱里面的第几代。
    如果发现了新的祖先,然么当然自己代数要加一。
    比如李渊原来 level 是 1 代,然后认了老子做祖宗,那么他就不 1 代了,老子是 1 代。
    Zien
        56
    Zien   20 天前 via Android
    parent_id 和 person_id 级联(假定只是父系族谱
    realpg
        57
    realpg   19 天前
    族谱这种除非录入错误,不会 update 的数据库
    基础表按 relation 结构
    然后针对热度超高冗余度就好了……
    反正你族谱再大也不会超过十万人吧
    在数据库这都是小 case

    别说爷爷,把四五代数据都生成冗余类视图的表都没啥事儿
    12tall
        58
    12tall   19 天前
    @xuanbg 让我想到了,错综复杂部门领导关系~
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3130 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 15:05 · PVG 23:05 · LAX 08:05 · JFK 11:05
    ♥ Do have faith in what you're doing.