如何使用一个通用表来处理关系

olqngx59  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(353)

我现在看到的是一个表,它是“硬编码”的,在同一个表的两个表之间有一个关系,并且有两个外键指向该表。
“关系”表之前:

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`)

因此在此之前,item表上有一个双重多对一引用来填充两个外键。
现在需要将这种关系扩展到数据库中的表(enum、tag、feature等)之间更通用的关系。因此,现在项可以与项相关,项可以与标记相关,标记可以与标记相关,等等,使用enum relationship值。
我对general表的想法是添加一个类型表,这样就可以标识每个项、标记等,然后将关系表重组为如下所示:

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\u id和table来确定哪些类型的项是相关的,并且可以打开它,这样任何两个table id都可以进入relater和relatee外键列。
问题是我不知道如何对外键有这样的通用性。我相信他们只能引用一个表,所以我不知道如何使用一个通用键引用来做我想做的事情。另外,我可以看到双向关系的一个问题,其中a相互需要b,b相互需要a是冗余数据。我可以在我的应用程序中阻止这种冗余,但我必须经常检查双面a到b | | b到a。我想知道怎样才能完成我想做的事情。谢谢您。
编辑:也许对我的(项目、特性、标签)使用某种基本类型可以帮助我?
编辑:我不认为答案像继承那么简单。至少从我所知道的来看。我的问题是,我想联系两个一般项目,无论类型。我不希望20列必须为null,因为它不是那种特定类型。我只想能够传递两个ID,因此两个类型的ID到关系中,这样我就可以关联任意两个对象。

zdwk9cvp

zdwk9cvp1#

一种可能的解决方案是实现对象类型和对象索引表:

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`));

并仅针对该表定义关系。

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`));

接下来定义每个对象表,使它们与unique(object\u type\u id,object\u id)元组上的object\u索引相关联。在本例中,每个表的默认值和检查约束对象\u type \u id应该是唯一的:

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\索引中的object\ type\ id,而不是存储的物理列。
在mysql 8.0及更高版本中,您可以在每个表上定义一个基于函数的索引,该表将鉴别器对象类型id定义为表达式,而不是表中的物理或虚拟列。

相关问题