How to model schedule and keep good constraints? 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.
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