0

I searched for answers to the following, but got answers mostly related to whether or not to use a surrogate at all, not about whether or not to then use compound keys on children of join tables.

I have a table attendees and a table events.

The join table is event_attendees, containing two foreign keys attendee_id and event_id, and also has a surrogate primary key id

let's now say attendees in an event can have schedule_items.

I would normally simply use the columns event_attendee_id, schedule_item_id in the table event_attendee_schedule_items.

What drawbacks, if any, would using the following compound foreign key set up have over the above?

attendee_id, event_id, schedule_item_id (compound key instead of a direct link to the event_attendees table)?

Squadrons
  • 2,467
  • 5
  • 25
  • 36
  • 1
    The second solution, using the coumpound key, sounds more logical to me. As you have one person at one place at one time... You're linking three tables together... – Martin Verjans Mar 24 '16 at 15:34

1 Answers1

1

I would recommend the first approach if:

  • the table event_attendees is still required. Because if you have two join tables event_attendees and event_attendee_schedule_items (which is basically what you get if you use the second approach) you can enter inconsistent data. Even if you can guarantee that the two tables are in sync (for example by the external program which is writing the data in), I wouldn't to this.

  • you have ideas of other extensions in the same style, like a table event_attendee_invoice_parts (which would link to invoice data) you can put common data into event_attendees

  • event_attendees has its own properties like entry_badge_id which will be used in the context of event_attendee_schedule_items. For this queries, you have to join with event_attendees anyways.

The last reason gives us an additional hint: it depends on amount of data in the tables and the queries you make! In some cases your performance will benefit from composite keys (and composite indexes), in other cases not. See this question for a detailed discussion.

Community
  • 1
  • 1
ventiseis
  • 3,029
  • 11
  • 32
  • 49
  • This is what I ended up doing. I've used the pattern you described as best before, and it worked well. The big difference, and a good point I didn't think of was the idea of storing common data on that event_attendees table. Thanks for the reply. – Squadrons Mar 25 '16 at 19:00