Model关系数据库设计

表结构

表名 ContentModelRelations

字段

• id
• type
  • oneToOne
  • oneToMany
  • manyToMany
• description
• tableName

样例数据

Content models

• Talent (contentModelId == 1)
• Experience (contentModelId == 2)
• Company (contentModelId == 3)
• Industry (contentModelId == 4)
• Location (contentModelId == 5)
• Role (contentModelId == 6)

关系实例

1. Talent : Experience = 1 : n

    // 外键talentId应该和talent放在一起，对于talent，“外”包含的意思是有一个内键id，和一个外键talentId，外键是给别的model用的，可以是 Experience, Company, Industry, Role 中的一个或多个。
    {
        id: 1,
        type: "oneToMany",
        description: [
            {
                contentModelId: 1,
                foreignKey: 'talentId',
                alias: { 
                    contentModelId: 2,
                    willReturn: true,
                    contentModelAlias: 'experiences'
                }
            },
            {
                contentModelId: 2,
                foreignKey: null,
                alias: { 
                    contentModelId: 1,
                    willReturn: true,
                    contentModelAlias: 'talent'
                }
            }
        ],
        relationTableId: 2,
        relationTableName: null
    }
   

   • 创建relation时，同时在Experience对应的数据表中增加talentId字段作为外键；
   • 删除relation时，同时在Experience对应的数据表中删除talentId字段；
   • 不允许更新relation。

1. Company : Industry = n : n

    {
        id: 2,
        type: "manyToMany",
        description: [
            {
                contentModelId: 3,
                foreignKey: 'companyId',
                alias: { 
                    contentModelId: 4,
                    willReturn:true,
                    contentModelAlias: 'industries'
                }
            },
            {
                contentModelId: 4,
                foreignKey: 'industryId',
                alias: { 
                    contentModelId: 3,
                    willReturn: true,
                    contentModelAlias: 'companies'
                }
            }
        ],
        relationTableId: null,
        relationTableName: 'Companies_Industries_${now()}'
    }
   

   • 创建relation时，同时创建Companies_Industries_${now()}关系表，且该关系表以 (companyId + industryId) 作为主键；
   • 删除relation时，同时删除Companies_Industries_${now()}关系表；
   • 不允许更新relation。

1. Company : Location = 1 : 1

    {
        id: 3,
        type: "oneToOne",
        description: [
            {
                contentModelId: 3,
                foreignKey: null,
                alias: { 
                    contentModelId: 5,
                    willReturn: true,
                    contentModelAlias: 'location'
                }
            },
            {
                contentModelId: 5,
                foreignKey: 'locationId',
                alias: { 
                    contentModelId: 3,
                    willReturn: true,
                    contentModelAlias: 'company'
                }
            }
        ],
        relationTableId: 3,
        relationTableName: null
    }
   

   • 创建relation时，同时在Company对应的数据表中增加locationId字段作为外键；
   • 删除relation时，同时在Company对应的数据表中删除locationId字段；
   • 不允许更新relation。

1. Talent : Company : Role = n : n : n

    // relationAlias 是系统根据用户自定义的多个 alias 自动生成的，一般情况不需要开放给用户进行修改。
    {
        id: 4,
        type: "manyToMany",
        description: [
            {
                contentModelId: 1,
                foreignKey: 'talentId',
                aliases: [
                    { 
                        contentModelId: 3,
                        willReturn: true,
                        contentModelAlias: 'companies'
                    },
                    { 
                        contentModelId: 6,
                        willReturn: true,
                        contentModelAlias: 'roles'
                    }
                ],
                relationAlias: 'company_role_set'
            },
            {
                contentModelId: 3,
                foreignKey: 'companyId',
                aliases: [
                    { 
                        contentModelId: 1,
                        willReturn: true,
                        contentModelAlias: 'talents'
                    },
                    { 
                        contentModelId: 6,
                        willReturn: true,
                        contentModelAlias: 'roles'
                    }
                ],
                relationAlias: 'talent_role_set'
            },
            {
                contentModelId: 6,
                foreignKey: 'roleId',
                aliases: [
                    { 
                        contentModelId: 1,
                        willReturn: true,
                        contentModelAlias: 'talents'
                    },
                    { 
                        contentModelId: 3,
                        willReturn: true,
                        contentModelAlias: 'companies'
                    }
                ],
                relationAlias: 'talent_company_set'
            }
        ],
        relationTableId: null,
        relationTableName: 'Talents_Companies_Roles_${now()}'
    }
   

   • 创建relation时，同时创建Talents_Companies_Roles_${now()}关系表，且该关系表以 (talentId + companyId + roleId) 作为主键；
   • 删除relation时，同时删除Talents_Companies_Roles_${now()}关系表；
   • 不允许更新relation。

User Experience

Content models

• Talent (contentModelId == 1)
• Experience (contentModelId == 2)
• Company (contentModelId == 3)
• Industry (contentModelId == 4)
• Location (contentModelId == 5)
• Role (contentModelId == 6)

关系实例

1. Talent : Experience = 1 : n

    <Step 1>
   
    System says:
        Please select models you want to create relation.
   
    User action:
        'Talent' and 'Experience'
   
    Code:
        {
            description: [
                {
                    contentModelId: 1,
                },
                {
                    contentModelId: 2,
                }
            ],
        }
   
    <Step 2>
   
    System says:
        Select relation for these models. (Generate foreign key by API)
   
    User action:
        1 'Talent' to n 'Experience'
   
    Code:
        {
            type: "oneToMany",
            description: [
                {
                    contentModelId: 1,
                    foreignKey: 'talentId', // Call API getForeignKeyByContentModelId(1);
                },
                {
                    contentModelId: 2,
                    foreignKey: null,
                }
            ],
            relationTableId: 2,
            relationTableName: null
        }
   
    <Step 3>
   
    System says:
        Select the content models you want to attach to this model, then naming their alias.
   
    User action:
        On the Talent side, it has been default selected 'Experience', user types 'experiences' as the alias.
        On the Experience side, it has been default selected 'Talent', user types 'talent' as the alias.
   
    Code:
        {
            type: "oneToMany",
            description: [
                {
                    contentModelId: 1,
                    foreignKey: 'talentId', // Call API getForeignKeyByContentModelId(1);
                    alias: { 
                        contentModelId: 2,
                        contentModelAlias: 'experiences'
                    }
                },
                {
                    contentModelId: 2,
                    foreignKey: null,
                    alias: { 
                        contentModelId: 1,
                        contentModelAlias: 'talent'
                    }
                }
            ],
            relationTableId: 2,
            relationTableName: null
        }
   
    <Step 4>
   
    System says:
        Please confirm the relation settings.
   
    User action:
        Confirm
   

2. Company : Industry = n : n

    <Step 1>
   
    System says:
        Please select models you want to create relation.
   
    User action:
        'Company' and 'Industry'
   
    Code:
        {
            description: [
                {
                    contentModelId: 3,
                },
                {
                    contentModelId: 4,
                }
            ],
        }
   
    <Step 2>
   
    System says:
        Select relation for these models. (Generate foreign key by API)
   
    User action:
        n 'Company' to n 'Industry'
   
    Code:
        {
            type: "manyToMany",
            description: [
                {
                    contentModelId: 3,
                    foreignKey: 'companyId', // Call API getForeignKeyByContentModelId(3);
                },
                {
                    contentModelId: 4,
                    foreignKey: 'industryId', // Call API getForeignKeyByContentModelId(4);
                }
            ],
            relationTableId: null,
            relationTableName: 'Companies_Industries_${now()}' // Call API getRelationTableNameByContentModelIds([3, 4]);
        }
   
    <Step 3>
   
    System says:
        Select the content models you want to attach to this model, then naming their alias.
   
    User action:
        On the Company side, it has been default selected 'Industry', user types 'industries' as the alias.
        On the Experience side, it has been default selected 'Company', user types 'companies' as the alias.
   
    Code:
        {
            type: "manyToMany",
            description: [
                {
                    contentModelId: 3,
                    foreignKey: 'companyId', // Call API getForeignKeyByContentModelId(3);
                    alias: { 
                        contentModelId: 4,
                        willReturn: true,
                        contentModelAlias: 'industries'
                    }
                },
                {
                    contentModelId: 4,
                    foreignKey: 'industryId', // Call API getForeignKeyByContentModelId(4);
                    alias: { 
                        contentModelId: 3,
                        willReturn: true,
                        contentModelAlias: 'companies'
                    }
                }
            ],
            relationTableId: null,
            relationTableName: 'Companies_Industries_${now()}' // Call API getRelationTableNameByContentModelIds([3, 4]);
        }
   
    <Step 4>
   
    System says:
        Please confirm the relation settings.
   
    User action:
        Confirm
   

3. Company : Location = 1 : 1

    <Step 1>
   
    System says:
        Please select models you want to create relation.
   
    User action:
        'Company' and 'Location'
   
    Code:
        {
            description: [
                {
                    contentModelId: 3,
                },
                {
                    contentModelId: 5,
                }
            ],
        }
   
    <Step 2>
   
    System says:
        Select relation for these models. (Generate foreign key by API)
   
    User action:
        1 'Company' to 1 'Location'
   
    Code:
        {
            type: "oneToOne",
            description: [
                {
                    contentModelId: 3,
                    foreignKey: null,
                },
                {
                    contentModelId: 5,
                    foreignKey: 'locationId',  // Call API getForeignKeyByContentModelId(5);
                }
            ],
            relationTableId: 3,
            relationTableName: null
        }
   
    <Step 3>
   
    System says:
        Select the content models you want to attach to this model, then naming their alias.
   
    User action:
        On the Company side, it has been default selected 'Location', user types 'location' as the alias.
        On the Location side, it has been default selected 'Company', user types 'company' as the alias.
   
    Code:
        {
            type: "oneToOne",
            description: [
                {
                    contentModelId: 3,
                    foreignKey: null,
                    alias: { 
                        contentModelId: 5,
                        willReturn: true,
                        contentModelAlias: 'location'
                    }
                },
                {
                    contentModelId: 5,
                    foreignKey: 'locationId',  // Call API getForeignKeyByContentModelId(5);
                    alias: { 
                        contentModelId: 3,
                        willReturn: true,
                        contentModelAlias: 'company'
                    }
                }
            ],
            relationTableId: 3,
            relationTableName: null
        }
   
    <Step 4>
   
    System says:
        Please confirm the relation settings.
   
    User action:
        Confirm
   

4. Talent : Company : Role = n : n : n

    <Step 1>
   
    System says:
        Select models you want to create relation.
   
    User action:
        'Talent', 'Company' and 'Role'
   
    Code:
        {
            description: [
                {
                    contentModelId: 1,
                },
                {
                    contentModelId: 3,
                },
                {
                    contentModelId: 6,
                }
            ],
        }
   
    <Step 2>
   
    System says:
        Select relation for these models. (Generate foreign by API)
   
    User action:
        n 'Talent' to n 'Company' to n 'Role'
   
    Code:
        {
            type: "manyToMany",
            description: [
                {
                    contentModelId: 1,
                    foreignKey: 'talentId', // Call API getForeignKeyByContentModelId(1);
                },
                {
                    contentModelId: 3,
                    foreignKey: 'companyId', // Call API getForeignKeyByContentModelId(3);
                },
                {
                    contentModelId: 6,
                    foreignKey: 'roleId', // Call API getForeignKeyByContentModelId(6);
                },
            ],
            relationTableId: null,
            relationTableName: 'Talents_Companies_Roles_${now()}' // Call API getRelationTableNameByContentModelIds([1, 3, 6]);
        }
   
    <Step 3>
   
    System says:
        Select the content models you want to attach to this model, then naming their alias.
   
    User action:
        On the Talent side, multiple selects content model and types its alias by list[Company, Role].
        On the Company side, multiple selects content model and types its alias by list[Talent, Role].
        On the Role side, multiple selects content model and types its alias by list[Talent, Company].
   
    Code:
        {
            type: "oneToMany",
            description: [
                {
                    contentModelId: 1,
                    foreignKey: 'talentId',
                    aliases: [
                        { 
                            contentModelId: 3,
                            willReturn: true,
                            contentModelAlias: 'companies'
                        },
                        { 
                            contentModelId: 6,
                            willReturn: true,
                            contentModelAlias: 'roles'
                        }
                    ],
                    relationAlias: 'company_role_set'
                },
                {
                    contentModelId: 3,
                    foreignKey: 'companyId',
                    aliases: [
                        { 
                            contentModelId: 1,
                            willReturn: true,
                            contentModelAlias: 'talents'
                        },
                        { 
                            contentModelId: 6,
                            willReturn: true,
                            contentModelAlias: 'roles'
                        }
                    ],
                    relationAlias: 'talent_role_set'
                },
                {
                    contentModelId: 6,
                    foreignKey: 'roleId',
                    aliases: [
                        { 
                            contentModelId: 1,
                            willReturn: true,
                            contentModelAlias: 'talents'
                        },
                        { 
                            contentModelId: 3,
                            willReturn: true,
                            contentModelAlias: 'companies'
                        }
                    ],
                    relationAlias: 'talent_company_set'
                }
            ],
            relationTableId: null,
            relationTableName: 'Talents_Companies_Roles_${now()}' // Call API getRelationTableNameByContentModelIds([1, 3, 6]);
        }
   
    <Step 4>
   
    System says:
        Please confirm the relation settings.
   
    User action:
        Confirm
   

issues

• 之前的表设计见，http://wiki.inceptionpad.cn/4-projects/gig-economy/database/db-design.html，更新主要有
  • 把sourceContentModelId和targetContentModelId改成了contentModelId []
    • 多对多关系下，关联的表可能超过2个，例如user、org、role分别为三张表，它们的关系表User_Org_Role分别以userId、orgId、roleId作为主键，表示一个user在org中的role，并且role可能是多重的。为了支持这种扩展性把contentModelId设计成[]的形式。
  • 新增fkFieldName []
    • 该字段表示在db中外键的fieldName，例如talentId、companyId；如果对应的model没有外键，则在存储时留空
    • 当model的relation创建时，需要在model对应的数据表中新增外键字段，并把该外键字段保存在fkFieldName中，当relation被删除时，model对应的外键也要删除
    • 由于外键字段是创建relation时创建的，非用户自己创建的fields，因此不保存在ContentModelFields表中，目前也暂时没看到保存在ContentModelFields表中的必要
  • 新增fkField []
    • 该字段表示后续查询或编辑model数据时，在关联查询中和当前model有关系的其他model数据的字段表示
      • 例如类似在strapi中的使用场景，编辑talnet时同时编辑和该talent相关联的experience，在页面上呈现时用experiences的字段表示
      • 另一种场景是一个model关联到另一model多次，关联时外键使用不同fieldName的场景；在AvidentHealth中，PatientProfile表有userId和createdByUserId两个字段，分别表示关联该PatientProfile的user和创建该PatientProfile的user，在关联查询中分别用user和createdByUser来表示两个不同的用户数据
    • ？实现时是否自动生成单复数的表示方式
      • 在oneToOne和oneToMany的relation中，存在单数表示
• 外键约束设置
  • oneToOne
    • ON DELETE SET NULL ON UPDATE CASCADE，表示一端删除时，另一端把外键的值设置为null，如果关联的主键更新，外键也同时更新
  • oneToMany
    • ON DELETE SET NULL ON UPDATE CASCADE，表示一端删除时，另一端把外键的值设置为null，如果关联的主键更新，外键也同时更新
  • manyToMany
    • ON DELETE CASCADE ON UPDATE CASCADE，表示任何一端删除时，把关联关系也删除，如果关联的主键更新，外键也同时更新
• 一对一、一对多、多对多关系在关系型数据库中的表设计
  • 参考
    • https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de
    • https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
    • https://launchschool.com/books/sql/read/table_relationships
    • http://www.ruanyifeng.com/blog/2019/02/orm-tutorial.html
• ？多对多关系表是用多个外键作为主键，还是用一个自增id作为自增主键
  • 参考
    • https://stackoverflow.com/questions/2190272/sql-many-to-many-table-primary-key
    • https://stackoverflow.com/questions/790334/sql-do-you-need-an-auto-incremental-primary-key-for-many-many-tables
    • https://en.wikipedia.org/wiki/Associative_entity
• ？是否允许两个模型之间的relation创建多次
  • 理论上允许，并且也有这样的需求，但是要做好fkFieldName、fkField、tableName的重复校验
• ？是否需要manyToOne的relation
  • 和oneToMany的区别只是方向相反而已
• ？是否需要oneWay和manyWay的单向relation
  • 表示单向关系；例如company和industry的关系如果manyWay，表示关系单向，在编辑company可以选择关联的industry，但是在编辑industry时无法选择关联的company，并且在创建index时，company index下有industries字段，但是industry index下没有companies字段
• ？是否支持在多对多关系表中的用户自定义字段
• ？是否可以把所有关系（1:1、1:m、m:n）都抽成独立的关系表（associative/junction table）
  • 好处
    • 不需要修改model本身的数据表，只需要新增关系表
    • 几种关联关系的技术实现统一，只是在外键上的唯一约束不同
      • 1:1，两侧外键都唯一
      • 1:m，右侧外键唯一
      • m:n，两侧外键的组合唯一
    • 几种关联关系可以随意更改，只需要修改关系表中外键唯一性（指外键的值是否只能出现一次），但是冗余的关系数据需要清理
      • 1:1 -> 1:m，删除左侧的外键唯一性
      • 1:m -> 1:1，增加右侧外键的唯一性，并把多余的关系数据删除，只保留第一条
      • 1:1 -> m:n，删除两侧外键唯一性
      • m:n -> 1:1，增加两侧外键唯一性，并把多余的关系数据删除，只保留第一条
      • 1:m -> m:n，删除右侧的外键唯一性
      • m:n -> 1:m，增加右侧的外键唯一性，并把多余的关系数据删除，只保留第一条
    • 后续做model数据的reversion时，由于model的数据表本身没有加入外键等依赖，设计history表会稍微统一点
      • model数据表 + history表
      • 表示relation的数据表 + relation history表
  • 坏处
    • 现实世界中一般一对多的关系最多，如果关系都表示在独立关系表中，需要维护的中间表会多很多
    • 无法在导入到ER图时表示正确的关系
    • 过多的中间表并表查询时影响性能，但是没有大规模测试过
    • 不太符合常见关系型数据库的关系设计方式
      • 虽然是做的黑盒headless CMS，但我们还是希望底层实现上尽量使用通用的数据库设计方式，这样后续如果不用我们的CMS，还是能把DB导出来自己开发，并且通用的关系型数据库设计在性能和可用性上是有过验证的；并且CMS部分作为后续项目开发基础，数据库设计需要遵循一个较常用的best practice
      • 常见数据库关系设计参考
        • https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de
        • https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
  • 参考
    • https://stackoverflow.com/questions/50767249/what-are-the-pros-and-cons-of-associative-table
    • https://stackoverflow.com/questions/843772/can-a-junction-table-join-table-also-be-used-for-a-one-to-many-relationship