-1

I want to store if two events are combinables. It's a n to n relation, so I need to have a pivot table with these constraints :

  • PK(event_1, event_2) = PK(event_2, event_1)
  • event_1 is a reference to id in table events
  • event_2 is a reference to id in table events

I tried to create my table with this query :

   event_id_1 INT UNSIGNED,
   event_id_2 INT UNSIGNED,
   cumulative_place INT,
   PRIMARY KEY(event_id_1,event_id_2),
   FOREIGN KEY(event_id_1) 
       REFERENCES events(id)
       ON DELETE CASCADE,
   FOREIGN KEY(event_id_2) 
       REFERENCES events(id)
       ON DELETE CASCADE
);

Following MySQL Documentation

I have some questions about that :

  1. Is it optimized for search query, for example if i want all events that are combinables with event1 whatever if event1 is in the first column or second ?
  2. Do I need to manually manage primary key for INSERT and UPDATE or MySQL do the job ? for example can I just insert cumulative place number for (event1, event2) primary key and if row is store as (event2, event1) this works as well.
Bhenscamp
  • 1
  • 1

1 Answers1

0

Sort the pair.

INSERT ...
    VALUES (
        LEAST("e1", "e2"),
        GREATEST("e1", "e2"),
        ...)

Similarly, sort the pair when doing a lookup. That way you have 1 row, not 2, and you don't have to look for both orders.

Rick James
  • 135,179
  • 13
  • 127
  • 222