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

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_id
CREATE 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_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;

Answer 1

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

Answer 2

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

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