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.