2

My question is related to this one: How to keep foreign key relations consistent in a "diamond-shaped" system of relationships

I have a very similar problem: A venue contains many seats. A venue also hosts many events. I need to keep track of which seats are booked in each event. This information is kept in the event_seat table, which has identifying relationships with both event and seat (i.e. its primary key identifies both an event and a seat).

One non-identifying relationship

I want to enforce the restriction that each row in event_seat must refer to an event and to a seat that belong to the same venue. I considered converting event -> venue into an identifying relationship by making venue_id part of the primary key for event, like so:

Only identifying relationships

Doing so would allow me to define two foreign keys for event_seat as follows:

(venue_id, event_id) references event(venue_id, event_id)
(venue_id, seat_id) references seat(venue_id, seat_id)

This definition would guarantee the desired restriction (the event must take place in the same venue to which the seat belongs). However, venue_id is actually a function of event_id, so (venue_id, event_id) is not a minimal superkey. Doesn't this violate some fundamental principle about primary keys? What other options do I have?

Community
  • 1
  • 1
Ron Inbar
  • 2,044
  • 1
  • 16
  • 26

1 Answers1

2

The seat_id is not unique by itself and is not functionally dependent on venue_id (nor vice-verse). That's why you should probably name it something like seat_no. Ditto for event_id.

If you want a "simple" field that is unique by itself, you can always introduce a surrogate key in addition to the composite key produced by the identifying relationships.

Alternatively, you can make the seat_id alone a key, but introduce a redundant UNIQUE constraint covering {venue_id, seat_id}, solely for the purpose of being referenced by the "bottom" of the diamond.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Thanks for the quick answer! You are right about `seat_id`. I should probably rename it. You are wrong about `event_id`, though: `event_id` _is_ unique by itself and should be enough to identify the event. If `venue_id` was independent of `event_id`, I wouldn't have any problem with the bottom diagram. The problem I have is with the relationship between `event` and `venue`, not between `seat` and `venue`. – Ron Inbar Dec 02 '13 at 17:03
  • @RonInbar I doesn't _have_ to be unique, based on your model alone. If there is some other business rule I'm not aware of forcing it to be unique, then just make it a surrogate key and use a separate field (e.g. `event_no`) for the purpose of correctly modelling the diamond-shaped dependency. Or use the alternative solution (redundant UNIQUE). – Branko Dimitrijevic Dec 02 '13 at 17:08