1

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

  • Can an item from table a and an item from table b be related via both types? Or always via just one type? – ypercubeᵀᴹ Jul 27 '16 at 9:46
  • Not it shouldn't be possible – darkheir Jul 27 '16 at 9:49
  • Then you need one table, to enforce this constraint. – ypercubeᵀᴹ Jul 27 '16 at 9:50
  • And performance wise, it won't be a lot worse ? – darkheir Jul 27 '16 at 9:50
  • Indexing will be a little more complicated but not much of a problem. – ypercubeᵀᴹ Jul 27 '16 at 9:51
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.