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)?
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.