How to model schedule and keep good constraints

database-designdatenormalization

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.

Best Answer

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

Related Solutions

MySQL – Fix Extremely Long Query Time with Nested WHEN-INs

Let's start with your original query

   SELECT *  
    FROM reports 
    WHERE report_name IN ( 
        SELECT report_name  
        FROM reports  
        WHERE region IN ( 
            SELECT distinct region  
            FROM macroregions 
            WHERE macroregion IN ( 
                SELECT distinct macroregion 
                FROM users  
                WHERE callsign = '$thisuser' 
            ) 
        )     
    )

You can gather keys only (in stages), then join the keys with the reports table. Here is my new proposed query

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        USING (macroregion)
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    USING (region)
) reportnamekeys
INNER JOIN reports
USING (report_name);

If you are not comfortable with the USING clause here is my new propsed query without using the USING clause:

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        ON users.macroregion = regions.macroregion
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    ON regionkeys.region = rpts.region
) reportnamekeys
INNER JOIN reports
ON reportkeys.report_name = reports.report_name;

You will need some indexes to support the subqueries

ALTER TABLE users   ADD INDEX callsign_macroregion_ndx (callsign,macroregion);
ALTER TABLE regions ADD INDEX macroregion_region_ndx (macroregion,region);
ALTER TABLE reports ADD INDEX region_report_name_ndx (region,report_name);
ALTER TABLE reports ADD INDEX report_name_ndx (report_name);

The first 3 indexes are called covering indexes. They are called such because the subqueries calls for only those exact columns. Therefore, no need to read from the table. The data is only fetched from the index.

You should also study how to refactor queries to gather keys, perform WHERE clauses early, and perform JOINs last. Here is great YouTube Video on how to do this: http://youtu.be/ZVisY-fEoMw (Based on this book : Refactoring SQL Applications)

Give it a Try !!!

Sql-server – Unique column values between tables

If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:

enter image description here

By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person to have multiple roles. By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role. You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact. You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team. I haven't tried it though.

Best Answer

Related Solutions

MySQL – Fix Extremely Long Query Time with Nested WHEN-INs

Sql-server – Unique column values between tables

Related Question