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