How to Use One General Table for Relationships

Question

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.

Before 'Relationship' 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`)

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.

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.

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

Edit: 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.

Sounds like you trying to implement [table Inheritance](https://stackoverflow.com/questions/26624138/does-mysql-support-table-inheritance) in MySQL? — Raymond Nijland, Jan 09 '19 at 15:57
Foreign keys need to refer to a specific table. If you don't specify which table it refers to, how can it check that the foreign key is valid when you try to add it? — Barmar, Jan 09 '19 at 16:03
I was kind of thinking that with my edit. Maybe having a base type for my items and then referencing this base type twice on the new relationship table? I'm not sure how I would capture each different item's id and store that in the base type. I see a similar problem with generality and not being able to reference multiple tables with a foreign key — C.Programming, Jan 09 '19 at 16:04
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jan 09 '19 at 16:05
So then the two foreign keys for the relationship table would refer to this base type table — C.Programming, Jan 09 '19 at 16:05

score 1 · Accepted Answer · answered Jan 09 '19 at 18:50

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

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

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.

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.

How to Use One General Table for Relationships

1 Answers1

Linked

Related