User assignment to various roles of changing events

database-design

We have numerous events throughout the year that are supported by multiple teams. Team A will support the entire event duration while Team B will support segments (typically per week). It is common for the event start date and duration (typically 10-30 days) to shift independent of other events creating a problem tracking gaps and overlap in coverage of these events. We want to create a central schedule so each team can see how event changes creates gaps and/or overlaps in assignments.

Events

Event   StartDate   Duration (Days)
E1      someDate1   25
E2      someDate2   14
E3      someDate3   22

Teams

Team    User
A       1
A       2
A       3
B       4
B       5
B       6

Assignments

Event   User    DayStart    DayEnd
E1      A1      1           NULL
E1      A2      1           NULL
E1      B4      1           7
E1      B6      15          NULL
E2      A2      1           NULL
E2      B7      1           7
E2      B4      1           7
E2      B5      8           14
E3      B5      1           7
E3      B6      8           14

The above example data has a gap for Team B during Event 1 (days 8-14) and Team A has no coverage for Event 3.

The thought is to have a single assignment table where DayEnd being NULL signifies that the individual is covering the remainder of the event. Is this a reasonable way to structure the tables? Would it be better to have separate tables for each team (less than five total teams)? Should NULL be replaced with some sufficiently high number such as 999? Each team will also have role assignments within events, but believe that will just be an additional table and not relevant to this question.

Disclaimer: this question was asked on stackoverflow.com without response

Best Answer

First, I would expect there to be a team table:

 TeamId | TeamName
--------+----------
    1   |  Team A
    2   |  Team B

and a separate user table, that identifies the team each user is in:

 UserId |   UserName   | TeamId
--------+--------------+--------
  1001  |   JaneSmith  |   1
  1002  |   JohnJones  |   1
  1003  |   KatieGrin  |   1
  1004  |  AbeSimpson  |   2
  1005  |  BenFranklin |   2
  1006  |  BarbaraEden |   2

Then, assignment would have UserId as a foreign key referencing the user table, just like TeamId in user is a foreign key referencing team.

I would not see value in having separate tables for each team. That would actually make things harder, rather than easier. The assignment table would either need one extra column, to indicate which table the assigned user came from (complicating queries, and preventing most common means to enforce referential integrity), or it will need a unique column to tie back to each distinct team table, in which case each foreign key column would need to allow for NULLs, which can hurt your referential integrity again.

Usually, the main benefit of using an actual value instead of NULL is to avoid the need for functions in your query to adjust the NULL value to as appropriate value. However, using 999 would probably still require some sort of computation, to adjust it to the actual DayEnd.

I do wonder if you wouldn't be better off using the actual DayEnd. I assume you're doing this to avoid having to recalculate the DayEnd values for an event that's duration changes; however, consider carefully if that's really the way you want to go. With NULL (or 999) as your DayEnd, you assume the users can safely be assigned to additional days. I don't know your business rules, but it seems entirely possible that someone may be unable to cover the additional days added to the event; assuming they can cover the additional days simply doesn't seem particularly safe.

Full details on implementation would, of course, depend on your DBMS, and your full business rules.

Related Solutions

Designing a user authenication (Roles & Rights) module

Based on your stated requirements, your model is in pretty good shape.

Here are some suggestions for improvement:

You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly. This would be very bad! If you look at common authentication databases, passwords are stored in encrypted form. You often see both a password column and a password_salt column.
Your USER_LOGS table has an Event column. You aren't clear about how this is to be populated. Should there be an EVENT_TYPE table which USER_LOGS references? This might make for friendlier reporting. Typical events would include log-in, log-out, password fail, password change, password reset, lock-out, unlock, ...
Your GROUP_RIGHTS table doesn't indicate who granted the rights. For audit trail purposes, people often keep a log of who changed what record and when. That may not be an issue for you.

Here's a couple of questions around your stated business requirements, which differ from the "text book" role-based security pattern in a couple of ways:

Are you sure you want users to be in only one group? The advantage of role-based security is that the roles tend to be pretty static, whereas the people fulfilling roles come and go pretty often. Included in this is that some people often "wear two hats".
Your design is grant-only. Some systems include grant and revoke. This allows you to say that a widely available right is not available to a particular group.
You have users and accounts comingled as USERS in your design. There is often a distinction between people and user IDs. Some user IDs are for teams or machines and some people have multiple user IDs for different purposes. Is this a distinction that would be helpful to you?

Mysql – User roles and permissions

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Best Answer

Related Solutions

Designing a user authenication (Roles & Rights) module

Mysql – User roles and permissions

Related Question