Mysql – Relation to original tables or to existing linking table

database-designMySQL

In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for each event he is linked to.

Every guest which has settings is are allready linked to the events for other reasons so there is an existing event_guest table with the necessary links.

So I'm not exactly sure about how I should link the settings table with the others.

Option 1

I link the settings with the table event_guest which links guests and events.

enter image description here

Option 2

I link the settings with the "original" tables guests and events.

enter image description here

Spontaneous I would go with option 1 but I'm a little bit confused about it…

My concern with option 1 is, that if I have a lot of deep relations, maybe even another table after settings, I need more complex sql queries to get for example data from settings, guests and events. Could this become a performance issue?

Which is the better solution and what are its advantages and disadvantages?

Best Answer

Andriy already said most of it, so I'll just be short.
After your description, settings come to existance upon the condition that a guest and an event are already linked, therefore option 1 would be choice, for that model will convey that concept to anyone viewing it.
There is some talk about surrogate vs composite keys. Anyway, Smaller PK => smaller indexes, but as in your case you'll be going with int surrogate keys for guests and events tables, then the composite key will also be small, meaning that performance loss potential against surrogate key is small, especially if you'll be searching by guest/event. The composite key, on the other hand, will give more meaning to the key.

Related Question