MySQL - Link two elements from the same table in another table

I have created a table in MySQL, and i would like to make connections between those elements.

It should be a many-to-many relationship, and what i've come up with is the following:

• Create a table with the elements:

  table Medicine

  | id | name | description |

• Create a table with th开发者_运维问答e links:

  table Incompatibilities

  | medicine_id_1 | medicine_id_2 |

So when i want to create an incompatibility between medicine 1 and medicine 2, i should do:

INSERT INTO incompatibilities VALUES 1,2

And when i would like to retrieve all the medicine that are incompatible with medicine ID = 1 i should do:

SELECT * FROM incompatibilities WHERE medicine_id_1=1 OR medicine_id_2=1

Is this the correct way to do it? Are those queries work as intended? Is there a more efficient way to do what i am after?

thank you in advance

Since incompatibility is a symmetric irreflexive relationship, you should always store the medicines in order: that with a least id in medicine_id_1, that with the greatest id in medicine_id_2.

You should create the unique indexes:

CREATE UNIQUE INDEX ux_incompatibility_1_2 ON (medicine_id_1, medicine_id_2)
CREATE UNIQUE INDEX ux_incompatibility_2_1 ON (medicine_id_2, medicine_id_1)

, insert the values:

INSERT
INTO    incompatibility (medicine_id_1, medicine_id_2)
VALUES   (LEAST(@med1, @med2), GREATEST(@med1, @med2))

This way, you store the relationships in both ways within a single record, and the UNIQUE constraints work properly.

To select all incompatible medicines:

SELECT  CASE 1 WHEN medicine_id_1 THEN medicine_id_2 ELSE medicine_id_1 END
FROM    incompatibility
WHERE   medicine_id_1 = 1
        OR medicine_id_2 = 1