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

Question

问题背景：

现在存在：

• 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;

Answer 1

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

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

Answer 2

用中间表：目前是比较稳妥的选择，多对多的关系，唯一的缺点就是查询应用的默认属性的时候，要查询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;