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.

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community.

Please consider the diagram:

enter image description here

I would like to add a unique constraint such that only one User can have only one Role per Event (Which is stored in the UserEventRoles table). Not sure how this can be solved? Perhaps with different design? Also the goal is not have a scenario where keys can be "out of sync", such as users role would point to an event where that role does not exist.

EventRoles table describes which Roles are valid at each Event.

I am sure this problem has come up before with other database designs and would like know what the best approach may be.

Also here is a link to sql fiddle to play around schema and some sample data.

sqlFiddle

share|improve this question
    
Do you mean that any user cannot have more than one role per event? (that's @Willem's assumption and answer) –  ypercube Aug 16 '13 at 20:21
    
Or that in any event there cannot be more than one users with same role? (this seems weird as you could not have more than one Attendees in an Event.) –  ypercube Aug 16 '13 at 20:21
    
@ypercube: A single user may only have a single role at any event. –  Michael Irey Aug 16 '13 at 20:45
add comment

2 Answers

up vote 3 down vote accepted

This is fairly simple to accomplish by making some changes to your primary keys.

http://sqlfiddle.com/#!2/8dcdd/1

I changed the primary key of the UserEventRoles table to be a composite key of user_id and event_id. This forces each User to only ever have at most one Role per Event.

I also modified the EventRoles table to use composite primary key. The surrogate key provided no value and made the resulting query more complex by forcing you to join into the EventRoles table if you wanted to preserve the foreign key constraint between that table and UserEventRoles.

With this change, you make sure you never define a role twice at an event, and you can directly join the columns in the UserEventRoles table to the descriptive tables that actually contain data you want.

share|improve this answer
    
Composite keys - FTW! This exactly what I was looking for. Solves the problem perfectly. –  Michael Irey Aug 16 '13 at 20:46
add comment

IMHO having both EventRoles and UserEventRoles are too much.

I'd propose to

  1. move user_id column to EventRoles
  2. create a unique constraint on (event_id, user_id)
  3. optionally make user_id nullable to be able to lay out roles for event without assigning them to particular persons
  4. get rid of UserEventRoles

Now to get your list

SELECT e.name event_name, 
       e.date event_date,
       r.name role, 
       u.name user_name
  FROM eventroles er JOIN events e
    ON er.event_id = e.id JOIN roles r
    ON er.role_id = r.id LEFT JOIN users u
    ON er.user_id = u.id

Note that INNER JOINs are used to join eventroles with ref tables events and roles. LEFT JOIN is only used to connect to users and only because I made user_id nullable.

Sample output:

| EVENT_NAME |                       EVENT_DATE |     ROLE | USER_NAME |
------------------------------------------------------------------------
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Attendee |       Bob |
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |    (null) | -- not assigned yet
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |    (null) | -- not assigned yet
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |       Joe |
|  RailsConf | September, 15 2013 00:00:00+0000 |    Staff |     Sally |
|  RailsConf | September, 15 2013 00:00:00+0000 | Security |       Joe |

Here is SQLFiddle demo.
You can try to uncomment the last two insert statements and see that a defined unique constraint will prevent them from being successfully completed

share|improve this answer
1  
But it allows to insert VALUES (1,4,1) so Joe would be both "Bartender" and "Security" at the same "Beer Fest" event. –  ypercube Aug 16 '13 at 19:41
    
@ypercube Fair enough. Thanks for the input. It's fixed. Answer and sqlfiddle updated. –  peterm Aug 16 '13 at 22:28
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.