MySQL - unique constraints design

Question

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:

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

Answer 1

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.

Answer 2

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