Repeat the foreign keys or use two tables related as a one-to-one

Question

I have two tables sessions and schedules, and I want to add a new table destinations.

The destinations table can be related to both tables, but only ONE destination can be related to a session or schedule:

• N destinations -> ONE session
• N destinations -> ONE schedule

Solution 1:

Create a destination table with two foreign keys (id_session and id_schedule) and only populate one for each destination.

Solution 2

I create destinations, schedule_destinations and sessions_destinations with the following relations:

• 1 destinations -> 1 schedule_destinations (id_destination [UNIQUE], id_schedule) -> N schedules
• 1 destinations -> 1 session_destinations (id_destination [UNIQUE], id_session) -> N sessions

Thanks!

Answer 1

To make your structure consistent with your business rules (one relationship relates with session or schedule), I would create 2 tables:

• schedule_destinations with foreign key to schedule.
• session_destination with foreign key to sessions.

And do not create third table destinations.

In the solution, you will have different sets of different destinations. Hence, you will not have one destination to session and schedule at the same time. Selects I would do from both tables if you need both types destinations in the same result.