2

I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will be scheduled for the same store two days in a row, but I have to ensure that they are scheduled for no more than 2 days in a given period (or "cycle").

A spreadsheet representation of the data would look like this.

Cycle Team    Store   Date
1     1       1   1-Dec
1     1       1   2-Dec
1     1       2   3-Dec
1     1       2   4-Dec
1     1       3   8-Dec
1     1       3   9-Dec
1     1       4   10-Dec
1     1       4   11-Dec
1     2       10  1-Dec
1     2       10  2-Dec
1     2       11  3-Dec
1     2       11  4-Dec
1     2       12  8-Dec
1     2       12  9-Dec
1     2       13  10-Dec
1     2       13  11-Dec

I already have a Team/Store cross reference that works pretty well, but getting down to this level of granularity is new. It would be easy if I could just put a composite primary key on Team/Store/Date, but I obviously can't do that, or I would only be able to store one of the two scheduled days.

I've considered using a date table with a one to many relationship from Team/Store to date, but that doesn't seem to fix the uniqueness issue and adds what feel like silly overhead.

Does anyone have any advice on how to model this?

The tag doesn't seem appropriate, as this is really an abstract design question that I'm asking, but I am using SQL Server 2008.

  • Will there always be exactly two days, and will they always be a consecutive 48-hour period? – 200_success Dec 1 '14 at 18:20
  • There could possibly be one or two days and they will not always be consecutive. – RubberDuck Dec 1 '14 at 18:21
5

I think this can be done with an additional column (say Day) that will be allowed to hold only 2 possible values (1 and 2).

Assuming a team cannot be in 2 or more stores in the same date, then you could have a UNIQUE constraint on (Team, Date) and another on (Cycle, Team, Store, Day).

The constraints would be:

UNIQUE CONSTRAINT uq_1
  (Team, Date),
UNIQUE CONSTRAINT uq_2
  (Cycle, Team, Store, Day),
CHECK CONSTRAINT ck_max_2_days_per_team_store_cycle
  (Day in (1,2))

and the data:

Cycle Team Store Date   Day
1     1     1    1-Dec   1
1     1     1    2-Dec   2
1     1     2    3-Dec   1
1     1     2    4-Dec   2
1     1     3    8-Dec   1
1     1     3    9-Dec   2
1     1     4   10-Dec   1
1     1     4   11-Dec   2
1     2    10    1-Dec   1
1     2    10    2-Dec   2
1     2    11    3-Dec   1
1     2    11    4-Dec   2
1     2    12    8-Dec   1
1     2    12    9-Dec   2
1     2    13   10-Dec   1
1     2    13   11-Dec   2
  • That just might work. I like that the constraint could easily be changed if business needs change. – RubberDuck Dec 1 '14 at 18:41
  • I think it needs some correction. It would allow a team to be in 2 or more stores in the same date. – ypercubeᵀᴹ Dec 1 '14 at 18:43
  • A unique constraint on Team/Date should fix that, right? – RubberDuck Dec 1 '14 at 18:45
  • Yes, edited the answer. – ypercubeᵀᴹ Dec 1 '14 at 18:47
  • 1
    This definitely works, but I'm going to hold off on the check for now. I'm hoping someone has another solution. It's going to be burdensome to handle the Day field on the application side. Thank you for your help. – RubberDuck Dec 1 '14 at 21:21

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.