Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you want/need settings on a particular guest/event pair without their being an event_guest entry? That, in my opinion, should be the first thing to affect the decision. If you go with the first option, you could consider foregoing the event_guest.id surrogate primary key and using (events_id, guests_id) as a compound primary key instead. –  Andriy M May 28 '13 at 9:27
    
If there are settings, there will always be an existing event_guest entry. You mean that I just use the events_id and guests_id in the settings table, so that I can choose if I want to join the linking event_guest table, which may contain additional information about the link, or just join the two tables guests and events directly. So basically this would be option 2... –  Marcel May 28 '13 at 14:49
    
Well, yes. I did mean to suggest using the two columns (in settings) as a compound foreign key referencing the event_guest's compound PK, simply to make sure a settings entry cannot exist without an event_guest one, but if you have other means of ensuring that, you could easily go with the option 2, of course. I must admit, though, that I can't advise you on which way of formal referencing would be better in terms of performance. That might depend on how often you needed to join settings to guests and events in comparison to joining it to just event_guest, but I'm not sure. –  Andriy M May 29 '13 at 8:47
    
@AndriyM I'm not sure if I understand, can you maybe pack your suggestion into an answer with maybe an example and further explenation? –  Marcel May 29 '13 at 15:29
1  
I've got no answer essentially and therefore will post none, sorry. I was only commenting on your options. But as an illustration to what I was talking about, here's a SQL Fiddle with the schema I had in mind. –  Andriy M May 29 '13 at 15:55
add comment

3 Answers 3

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.

share|improve this answer
add comment

It looks like event_guest is a many-to-many relationship table. The id field is redundant, you only need a compound primary key (guest_id, event_id). That will guarantee that all guest/event pairs are unique. Then put an index on event_id so you can do a reverse lookup (from event to guests).

If there is always an event_guest entry for each settings entry, why not combine them? Simply have a event_guest table with a compound primary key (guest_id, event_id) and a settings field.

If you must have them separated into two tables, go with option 2 but leave off the id field and give the tables a true 1:1 relationship by assigning the same (guest_id, event_id) primary key to both records when you create them. That's called a "horizontal split" of the event_guest table by breaking off the settings field into another table.

share|improve this answer
add comment

I see here 2 questions:

What key constraints to enforce? Guest and Event make up a Guest_Event entity, that may (potentially) exist without a Setting. So in Guest_Event table you'll enforce constrains on 2 columns Guest_ID and Event_ID.

One Guest_Event object may have several Setting objects. So this link should be enforced as well.

Another question is How to define keys for Guest_Event and Settings objects: as 2-3 separate columns or a single column?

It depends on how the application will be using these tables. Single column ID for Guest_Event and Setting will do if you'll be extracting these IDs and then reusing them in Where clause.

However if you need to get a Setting and application always knows Guest_ID and Event_ID, then getting individual Setting ID is an unnecessary step.

Covering indexes (ones including all key columns in all combinations) will help your performance, if you have enough space to store them and time to update them.

Your schema has no object specific data in Guest_Event table: if Guest_Event table has only keys, then it is not needed: you can use Settings table to track Guest_Event by using Setting like "G_E Instance Number ## Exists".

I suggest looking at this "Full schema" and removing parts, that your application will not use.

Order of the columns represent how often it will be used in filters: more often - more to the left.

CREATE TABLE guests (guest_id int PRIMARY KEY);

CREATE TABLE events (event_id int PRIMARY KEY);

CREATE TABLE guest_event (
  guest_id int,
  event_id int,
  guest_event_id int,
  guest_event_data varchar (200),
  PRIMARY KEY (guest_id, event_id, guest_event_id),
  FOREIGN KEY (event_id) REFERENCES events (guest_id),
  FOREIGN KEY (guest_id) REFERENCES guests (event_id),
  KEY (event_id, guest_id, guest_event_id),
  KEY (guest_event_id, guest_id, event_id)
);

CREATE TABLE settings (
  guest_id int,
  event_id int,
  guest_event_id int
  setting_id int,
  setting_data varchar(200),
  PRIMARY KEY (guest_id, event_id, setting_id),
  FOREIGN KEY (guest_event_id) REFERENCES guest_event (guest_event_id),  KEY (event_id, guest_id, setting_id)
  KEY (event_id, guest_id, setting_id)
);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.