0

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

2

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.

| improve this answer | |
  • Thank for the answer; I found the paragraph regarding DayEnd to be especially helpful. – manotheshark Apr 24 '18 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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