Mysql – Many-many relation with specific type

many-to-manyMySQLrelations

Sorry if this question has already been asked, but I didn't find it performing a search.

I have 2 tables (table_a and table_b), linked by a many/many relation.
The relation can be of two types, type_a and type_b.

I was wondering what was better between:

  • Setting a type column in the relation table and during queries filter on this field
  • Creating 2 relations table, one for each type and apply no filter during queries

I can see advantage in both solutions, but I can't tell which one is generally used to solve this kind of problem

Best Answer

Adapted from here; see that for rationale.

For separated tables, each should look similar to this:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

For the single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED  ***
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

A third option is to put both types in a single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, type, y_id),      -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

(*** indicates the only deviation(s) from previous version.)

Which is "best"? I don't know. And if you add FOREIGN KEYs, things get messier.

Related Question