如何为关系使用一个通用表

2022-01-20 00:00:00 sql database foreign-keys mysql primary-key

我现在拥有的是硬编码"的表，以便在同一个表的两个与该表的两个外键之间建立关系.

What I have now is table that is "hardcoded" to have a relationship between two of the same table with two foreign keys to that table.

在关系"表之前:

CREATE TABLE IF NOT EXISTS `Item_To_Item` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `item1_id` INT(11) NOT NULL,
  `item2_id` INT(11) NOT NULL,
  `relationship` ENUM('requires', 'mutually_requires', 'required_by', 'relates', 'excludes') NULL DEFAULT NULL,
  `description` VARCHAR(1000) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Item1_Id`
    FOREIGN KEY (`item1_id`)
    REFERENCES `Item` (`id`)
  CONSTRAINT `fk_Item2_Id`
    FOREIGN KEY (`item2_id`)
    REFERENCES `Item` (`id`)

所以在此之前在项目表上有一个双多对一引用来填充两个外键.

So before this had a double many to one reference on the item table to fill the two foreign keys.

现在需要在数据库中的表(枚举、标签、功能等)之间扩展这种关系以使其更通用.所以现在项目可以与项目相关，项目可以与标签相关，标签可以与带有枚举关系值的标签等相关.

There is a need now to expand this relationship to be more general between tables in the db (Enum, Tag, feature etc). So that now Items can relate to items, items can relate to tags, tags can relate to tags etc with the enum relationship value.

我对通用表的想法是添加一个类型表，因此可以识别每个项目，标签等，然后将关系表重组为如下所示:

What I am thinking for the general table is adding a type table, so each item, tag, etc can be identified, and then restructuring the relationship table to be something like this:

CREATE TABLE IF NOT EXISTS `Relationship` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `relater_id` INT(11) NOT NULL,
  `relatee_id` INT(11) NOT NULL,
  `relationship` ENUM('requires', 'mutually_requires', 'required_by', 'relates', 'excludes') NULL DEFAULT NULL,
  `description` VARCHAR(1000) NULL DEFAULT NULL,
  `relater_type_id` INT(11) NULL,
  `relatee_type_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Relatee_Id` (`relatee_id` ASC),
  INDEX `fk_Relater_Id` (`relater_id` ASC),
  CONSTRAINT `fk_Relater_Id`
    FOREIGN KEY (`relater_id`)
  CONSTRAINT `fk_Relatee_Id`
    FOREIGN KEY (`relatee_id`)

现在您可以通过 type_id 和 table 来识别哪些类型的项目，并且可以打开它，以便任何两个 table id 都可以进入 Relater 和 Relatee 外键列.

So that now you can identify what type of items are being related by the type_id and table and this can be opened up so any two table ids can go into the Relater and Relatee foreign key columns.

问题是我不知道如何对外键进行这样的概括.我相信他们只能引用一张表，所以我不确定如何使用通用键引用来做我想做的事.此外，我可以看到双向关系的问题，其中 A 相互需要 B 和 B 相互需要 A 是冗余数据.我可以在我的应用程序中阻止这种冗余，但我必须经常检查两侧的 A 到 B ||B 到 A.我想知道完成我想做的事情的最佳方式.谢谢.

The problem is that I do not know how to have such generality with foreign keys. I believe they can only reference one table so I am not sure how to do what I want with a general key reference. Also, I can see a problem with bidirectional relationships where A mutually Requires B and B mutually Requires A being redundant data. I could block this redundancy in my application, but I would constantly have to check for two sided A to B || B to A. I was wondering the best way to accomplish what I am trying to do. Thank you.

也许为我的(项目、功能、标签)使用某种基本类型可以帮助我?

Maybe using some kind of base type for my (item, feature, tag) could help me?

我认为答案并不像继承那么简单.至少据我所知.我的问题是，无论类型如何，我都想关联两个一般项目.我不希望 20 列必须为空，因为它不是那种特定类型.我只是希望能够将两个 id 和两个 type_id 传递到关系中，这样我就可以关联任何两个对象.

I don't think the answer is as simple as inheritance. At least from what I can tell. My problem is that I want to relate two general items no matter the type. I don't want 20 columns that have to be null because it is not that specific type. I just want to be able to pass two ids and therefore two type_ids into the relationship so I can relate any two objects.

推荐答案

一种可能的解决方案是实现 object_type 和 object_index 表:

One potential solution is to implement object_type and object_index tables:

CREATE TABLE object_type (
  `object_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_type` varchar(30) NOT NULL,
  PRIMARY KEY (`object_type_id`),
  UNIQUE (`object_type`));

CREATE TABLE object_index (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_type_id` int(11) NOT NULL,
  PRIMARY KEY (`object_id`),
  UNIQUE (`object_type_id`, `object_id`));

并仅针对该表定义您的关系.

and define your relations against that table only.

CREATE TABLE IF NOT EXISTS `Relationship` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `relater_id` INT(11) NOT NULL,
  `relatee_id` INT(11) NOT NULL,
  `relationship` ENUM('requires', 'mutually_requires', 'required_by', 'relates', 'excludes') NULL DEFAULT NULL,
  `description` VARCHAR(1000) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Relatee_Id` (`relatee_id` ASC),
  INDEX `fk_Relater_Id` (`relater_id` ASC),
  CONSTRAINT `fk_Relater_Id`
    FOREIGN KEY (`relater_id`)
    references object_index (`object_id`),
  CONSTRAINT `fk_Relatee_Id`
    FOREIGN KEY (`relatee_id`)
    references object_index (`object_id`));

接下来定义每个对象表，使其与唯一 (object_type_id, object_id) 元组上的 object_index 相关联.在这个例子中，每个表默认和检查约束 object_type_id 应该是唯一的:

Next each of your object tables are defined such they relate back to the object_index on the unique (object_type_id, object_id) tuple. In this example each tables default and check constrained object_type_id should be unique:

CREATE TABLE table1 (
  `object_id` int(11) NOT NULL,
  `object_type_id` int(11) NOT NULL DEFAULT 1 CHECK (object_type = 1),
  `col1` varchar(4),
  PRIMARY KEY (`object_id`),
  CONSTRAINT fk_t1_ob_idx
    FOREIGN KEY (`object_type_id`, `object_id`)
    REFERENCES object_index (`object_type_id`, `object_id`));

在 MySQL 5.6 及更高版本中，您可以在每个表上定义一个虚拟/计算列，以匹配来自 object_index 的 object_type_id 而不是存储的物理列.

In MySQL 5.6 and above you could define a virtual/computed column on each table to match object_type_id from the object_index instead of a stored physical column.

在 MySQL 8.0 及更高版本中，您可能能够在每个表上定义一个基于函数的索引，该索引将鉴别器 object_type_id 作为表达式而不是表中的物理或虚拟列.

In MySQL 8.0 and above you might be able to define a function based index on each table that includes the discriminator object_type_id as an expression instead of as a physical or virtual column in the table.

相关文章