当前位置: 首页 > 知识库问答 >
问题:

java - 在多对多关系中,中间表和增加关联键哪种是更好的方案?

尉迟越
2023-07-28

问题背景:

现在存在:

  • app表,存放项目信息。
  • property表,存放项目属性。
  • event表,存放项目事件

项目与属性是多对多的关系。即每个项目下可能存在多个属性。(由于存在公共属性,所以不是一对多)

项目与事件是多对多的关系。即每个项目下可能存在多个事件。(由于存在公共事件,所以不是一对多)

事件与属性是多对多关系。即每个事件下可能存在多个属性。

同时,每个项目存在一个默认属性。默认属性属于property表。

因此,现在存在四种关系。

  • app和property的多对多关系
  • app和event的多对多关系
  • event和property的多对多关系
  • app的默认property的一对一关系。

待解决问题:

  • 中间表和增加关联键哪种是更好的方案?
  • 公共属性和项目属性是否有必要拆表?

问题资料:

其中app建表语句如下:

CREATE TABLE `app` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `app_id` varchar(24) NOT NULL UNIQUE KEY,    `name` varchar(128) NOT NULL,    `domain` varchar(128) NOT NULL,    `status` int(3) NOT NULL DEFAULT 0,    `description` varchar(2048) DEFAULT NULL,    `created_by` varchar(128) NOT NULL,    `updated_by` varchar(128) NOT NULL,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

property建表语句如下:

CREATE TABLE `property` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `parent_id` int(11) DEFAULT NULL,    `app_id` int(11) DEFAULT NULL,    `name` varchar(128) NOT NULL,    `label` varchar(128) NOT NULL,    `type` varchar(128) NOT NULL,    `client` varchar(128) NOT NULL,    `img` varchar(128) NOT NULL,    `description` varchar(255) DEFAULT NULL,    `status` tinyint(1) NOT NULL DEFAULT 1,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`),    `parent_id` int(11) DEFAULT NULL,    KEY `idx_parent_id` (`parent_id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`app_id`),    FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `property` (`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

event建表如下:

CREATE TABLE `event` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `app_id` int(11) DEFAULT NULL,    `name` varchar(128) NOT NULL,    `label` varchar(128) NOT NULL,    `type` varchar(128) NOT NULL,    `client` varchar(128) NOT NULL,    `img` varchar(128) NOT NULL,    `description` varchar(255) DEFAULT NULL,    `status` tinyint(1) NOT NULL DEFAULT 1,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`app_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

目前的解决方案:

方案一:增加关联键

--增加default_property_id在app表中CREATE TABLE `app` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `app_id` varchar(24) NOT NULL UNIQUE KEY,    `name` varchar(128) NOT NULL,    `domain` varchar(128) NOT NULL,    `status` int(3) NOT NULL DEFAULT 0,    `description` varchar(2048) DEFAULT NULL,    `created_by` varchar(128) NOT NULL,    `updated_by` varchar(128) NOT NULL,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    `default_property_id` int(11) DEFAULT NULL,    PRIMARY KEY (`id`),    FOREIGN KEY (`default_property_id`) REFERENCES `property` (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;--增加app_id在property表中CREATE TABLE `property` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `parent_id` int(11) DEFAULT NULL,    `app_id` int(11) DEFAULT NULL,    `event_id` int(11) DEFAULT NULL,    `name` varchar(128) NOT NULL,    `label` varchar(128) NOT NULL,    `type` varchar(128) NOT NULL,    `client` varchar(128) NOT NULL,    `img` varchar(128) NOT NULL,    `description` varchar(255) DEFAULT NULL,    `status` tinyint(1) NOT NULL DEFAULT 1,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`),    KEY `idx_parent_id` (`parent_id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`id`),    FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),    CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `property` (`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;CREATE TABLE `event` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `app_id` int(11) DEFAULT NULL,    `name` varchar(128) NOT NULL,    `label` varchar(128) NOT NULL,    `type` varchar(128) NOT NULL,    `client` varchar(128) NOT NULL,    `img` varchar(128) NOT NULL,    `description` varchar(255) DEFAULT NULL,    `status` tinyint(1) NOT NULL DEFAULT 1,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`app_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

方案二:增加中间表

-- 将属性表中的app_id改为owner字段,公共属性设置为public,非公共属性设置为其所在的app_idCREATE TABLE `property` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `parent_id` int(11) DEFAULT NULL,    `owner` varchar(24) DEFAULT NULL,    `name` varchar(128) NOT NULL,    `display_name` varchar(128) NOT NULL,    `type` varchar(128) NOT NULL,    `client` varchar(128) NOT NULL,    `img` varchar(128) NOT NULL,    `description` varchar(255) DEFAULT NULL,    `status` tinyint(1) NOT NULL DEFAULT 1,    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),    PRIMARY KEY (`id`),    `parent_id` int(11) DEFAULT NULL,    KEY `idx_parent_id` (`parent_id`),    UNIQUE KEY `idx_app_property` (`owner`, `name`) FOREIGN KEY (`app_id`) REFERENCES `app` (`app_id`),    FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `property` (`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;--event表与property同理--增加事件属性中间表CREATE TABLE `event_property` (    `event_id` int(11) NOT NULL,    `property_id` int(11) NOT NULL,    PRIMARY KEY (`event_id`, `property_id`),    FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE,    FOREIGN KEY (`property_id`) REFERENCES `property` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 增加中间表,is_default表示是否为默认属性,app下唯一CREATE TABLE `app_property` (    `app_id` int(11) NOT NULL,    `property_id` int(11) NOT NULL,    `is_default` tinyint(1) NOT NULL DEFAULT 0,    PRIMARY KEY (`app_id`, `property_id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`id`) ON DELETE CASCADE,    FOREIGN KEY (`property_id`) REFERENCES `property` (`id`) ON DELETE CASCADE,    UNIQUE KEY `uniq_app_default_property` (`app_id`, `is_default`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;--增加事件属性中间表,event_propertyCREATE TABLE `event_property` (    `event_id` int(11) NOT NULL,    `property_id` int(11) NOT NULL,    PRIMARY KEY (`event_id`, `property_id`),    FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE,    FOREIGN KEY (`property_id`) REFERENCES `property` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

共有2个答案

窦夜洛
2023-07-28

用这个问题请教了一位技术大佬,是一位非常非常厉害的大佬,所以感觉回答会有对此类问题具备比较通用的参考性,放在这里希望给遇到此类问题的人帮助。

  • 1)、从数据库设计范式的角度看,拆中间表是标准操作;但从工程实践的角度,我倾向于关联键的方式,冗余字段,一方面可以降低不必要的数据库IO操作,另一方面工程开发效率更高;
  • 2)、公共属性和项目属性要不要拆表的问题:好像差别不大,两种做法业界都有用的。通常情况下我喜欢放一张表,理由同1);但业务上如果场景是分开展示(或者说公共属性被用的更多),我也会考虑拆分。当然如果数据量不大,就没必要纠结了,直接放一个表
邢良才
2023-07-28

用中间表:目前是比较稳妥的选择,多对多的关系,唯一的缺点就是查询应用的默认属性的时候,要查询app_property表,而不是直接查询app表。加一下索引就行

CREATE TABLE `app_property` (    `app_id` int(11) NOT NULL,    `property_id` int(11) NOT NULL,    `is_default` tinyint(1) NOT NULL DEFAULT 0,    PRIMARY KEY (`app_id`, `property_id`),    FOREIGN KEY (`app_id`) REFERENCES `app` (`id`) ON DELETE CASCADE,    FOREIGN KEY (`property_id`) REFERENCES `property` (`id`) ON DELETE CASCADE,    UNIQUE KEY `uniq_app_default_property` (`app_id`, `is_default`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 类似资料:
  • 问题内容: 已关闭 。这个问题是基于观点的。它当前不接受答案。 想改善这个问题吗? 更新问题,以便通过编辑此帖子以事实和引用的形式回答。 3年前关闭。 “关联表”的正确或最受欢迎的名称是什么? 我听说过查找,关联,解析,映射和联结表。 问题答案: 对照表。CustomerProductXRef。

  • 问题内容: 更新2009.04.24 我的问题的重点不是开发人员的困惑以及如何处理。 关键是要了解定界值何时是正确的解决方案。 我已经看到了商业产品数据库(Ektron哈哈)中使用的定界数据。 SQL Server甚至具有XML数据类型,因此可以与定界字段用于相同的目的。 /结束更新 我正在设计的应用程序具有一些多对多的关系。过去,我经常使用关联表在数据库中表示这些表。这给开发人员造成了一些混乱。

  • 我使用的是Laravel 5.4,其模型和表格结构如下: 一个用户可以有多个账户 一个帐号可以被多个用户共享 每个账户有多个持有量 因此,用户通过他们的许多帐户间接拥有许多资产。 我需要帮助在用户模型上定义一个称为“持有量”的关系,以获得适用于用户的所有持有量(基于他们所链接的账户)。 我尝试了很多不同的东西,在谷歌上花了很长时间。我可以接近BelongToMany和hasManyThrough,

  • 我用复合键定义了两个实体之间的多对多关系。问题是,当我获得join对象时,它只被过滤了关系的一侧,而不是两边。 图片使问题更加清晰。这里,我要查找的是dtid=185和prid=352,但我从多对多关系中得到的是两个突出显示的行。 天丁:

  • 问题内容: 我有一个Firebase数据库。我有公司和承包商。一个承包商可以为多个公司工作,而一个公司可以有多个承包商。这是直接的多对多关系。我希望能够回答有关公司和承包商的问题: 给定一家公司,他们是当前的承包商。 给承包商一个他们要工作的公司。 在Firebase中构造数据的替代方法有哪些? 问题答案: 自我回答确实是对此进行建模的一种方法。它可能是在关系数据库中建模的最直接等效方法: 承办商

  • 问题内容: 我知道在Elasticsearch中,我们可以在文档之间建立子/父关系。 然后,在建立索引时,我可以传递父代ID,以便将子文档和父文档链接起来: 无论如何,在Elasticsearch中建立多对多关系的模型吗? 数据驻留在具有以下架构的MySQL数据库中: 当前这是我的映射(请原谅数组表示法,我在PHP中使用Elastica与我的Elasticsearch服务器通信): 这种方法的问题