Database design for dynamic oncall list

[falian92]

I have decided to create a new website for my group at work.  The previous one is 12 years old and is outdated.  I thought it would be a great idea to have a dynamic on call list, based on today's date or a date selected by a user.  I am new to PHP and have a little experience with MySQL.  I have programmed some in other languages as well and I am confident that I can get this to work but I am having trouble with the design of the database itself.

The background:

There are four groups on call each week, one person from each group.

Three of the groups follow a straight forward rotation, however there are different number of employees in each group.  i.e. 1,2,3,4,5,1,2,3,4,5....etc

The fourth group follows a different rotation but it is constant.  i.e. 1,2,3,4,5,2,1,3,5,4,1,2,3,4,5,2,1,3,5,4....etc

I want to be able to remove or add users to a group without breaking end result. i.e. removing a user would change forward queries but historical data would be preserved.

Some weeks users may switch rotations to cover vacations.

These are my current tables but I was thinking about creating a temp table for vacation changes and a user table with first and last name, cell and home number, a start and end date and a userId.  If the rotations never changed, i.e. people getting hired or fired, no vacations it would be fairly straight forward but...life is not that simple.  Additionally I want this to eventually be able to be updated by any user in the group without them having to understand PHP or MySQL.

 

 

mysql> describe department;

+--------------+------------+------+-----+---------+----------------+

| Field          | Type      | Null  | Key | Default | Extra          |

+--------------+------------+------+-----+---------+----------------+

| deptName  | char(30) | YES  |      | NULL    |                    |

| deptId        | int(11)  | NO  | PRI | NULL    | auto_increment |

+---------------+----------+------+-----+---------+----------------+

2 rows in set (0.02 sec)

 

mysql> describe lan;

+----------+----------+------+-----+---------+----------------+

| Field          | Type    | Null | Key | Default | Extra          |

+----------+----------+------+-----+---------+----------------+

| memberId | int(11)  | NO  | PRI | NULL    | auto_increment |

| groupid    | int(11)  | YES  |    | NULL    |                |

| first          | char(20) | YES  |    | NULL    |                |

| last          | char(30) | YES  |    | NULL    |                |

| home        | char(15) | YES  |    | NULL    |                |

| cell          | char(15) | YES  |    | NULL    |                |

+----------  +----------+------+-----+---------+----------------+

6 rows in set (0.01 sec)

 

All four groups are currently set up like this one.

 

mysql> describe memberschedule;

+----------+---------+------+-----+---------+-------+

| Field          | Type    | Null | Key | Default | Extra |

+----------+---------+------+-----+---------+-------+

| week        | int(11) | YES  |    | NULL    |      |

| deptId      | int(11) | NO  |    | 0      |      |

| memberId | int(11) | YES  |    | NULL    |      |

 

 

Thank you in advance to all those that read this book and an extra thanks to anyone brave enough to try and help.

[ignace]

 

group (grp_id, grp_name, ..)
person (prsn_id, prsn_fname, ..)

 

 

Straight forward tables to hold groups and persons. Does each department have it's own groups? Or are group names shared, but depending on department filled by other persons?

 

 

person_in_group (prsn_id, grp_id)

 

 

This table will hold which person is in which group. A person may be part of multiple groups, if this is untrue, then add grp_id to the persons table and create a unique key as a reference point for schedule to verify a person is indeed part of a specific group.

 

 

schedule (week, year, grp_id, prsn_id)

 

 

This is the harder part, (week, year, grp_id) is the primary key of this table so that the same group id can't be assigned twice for the same week, as a bonus this means only 1 person from a specific group per week. The (grp_id, prsn_id) is a reference to person_in_group (or person table depending on your setup) and must match a row there.

 

 

I thought it would be a great idea to have a dynamic on call list, based on today's date or a date selected by a user.

 

 

A real date (meaning a day) or a week?

 

 

There are four groups on call each week, one person from each group.

 

 

This is addressed in the schema unless multiple persons from the same group may be in rotation for a specific week.

 

 

Three of the groups follow a straight forward rotation, however there are different number of employees in each group.  i.e. 1,2,3,4,5,1,2,3,4,5....etc

The fourth group follows a different rotation but it is constant.  i.e. 1,2,3,4,5,2,1,3,5,4,1,2,3,4,5,2,1,3,5,4....etc

 

 

I need some more information what you mean by these numbers. I think this should be filled with a cron or something into the schedule table.

 

 

I want to be able to remove or add users to a group without breaking end result. i.e. removing a user would change forward queries but historical data would be preserved.

 

 

This should be handled in your application so that all forward queries no longer have this person assigned. Historical data remains untouched.

 

 

Some weeks users may switch rotations to cover vacations.

 

 

Simply change the prsn_id in the schedule table. As long as there is only 1 user from each group assigned during a week.

 

 

 

 

This is just a first draft, there is still a lot of fog concerning what you want to do and how. Explaining clearly what you want to do and how and why will help us to better help you.

[falian92]

I will do my best to answer the questions and give a better understanding of what I want to accomplish.  If you need more clarification please let me know.

 

Explaining clearly what you want to do and how and why will help us to better help you.

 

In the end I want a table to be populated automatically based on the current date with all four departments' on call person for that given day.  The week starts on Monday and I know how to get the week of the year based on the given day of the year.  I have a javascript calender on the webpage right now that auto populates with the current date.  I also want a user to be able to select any day of the current year and the table will repopulate with the on call information for that day/week.  I want a user that does not know anything about programming to be able to add users, delete users and make temporary changes to the on call schedule when someone takes a vacation and the normal rotation is changed, all without effecting future or past rotations.  So if John from group A gets fired during the 18th week of the year someone could remove him by filling out a form.  All queries before week 18 would show John as on call if that was his week but all queries after week 18 would be based on the current amount of users in the group.

Ultimately in the end I want to create a form that will allow a user to recreate the database if the on call rotation policy changes.  But that will be a final stage, a year from now and password protected.  I only say this in case that changes the design of the database somehow.

I am trying to think forward to when I am not in this department and/or company.  I don't want all this work to go to waste because no one knows how to update or change the database.

 

Does each department have it's own groups? Or are group names shared, but depending on department filled by other persons?

 

Each department has it's own group. Each person is in one group only.

 

A real date (meaning a day) or a week?

 

Yes a real date (meaning a day)

 

I need some more information what you mean by these numbers.

 

If group A has members Bob, Joe, Mary, Frank and John they will always be on call in the same order.  Bob then Joe then Mary then Frank then John then Bob again....

This is true for three of the four groups.  The fourth group has nine employee's and the rotation repeats every 18 weeks. so using numbers in place of the name for this example:

1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9,8,7,1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9,8,7,1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9 would be the whole rotation for the year for this group.

 

I think this should be filled with a cron or something into the schedule table.

 

I am unfamiliar with a cron could you please elaborate?

 

This should be handled in your application so that all forward queries no longer have this person assigned. Historical data remains untouched.

 

This will be a dynamic web page.  So today someone could log onto the web page and want to know who was on call Jan 18, 2012.  If someone was added to the group since then how would the query know not to include them when calculating the on call person?

Simply change the prsn_id in the schedule table. As long as there is only 1 user from each group assigned during a week.

So you would suggest that the schedule table holds 208 entries, four entries for each week of the year.  I had not thought of that.

[falian92]

Thank you for the response as well Ignace.

[falian92]

I did not understand this part

 

This is the harder part, (week, year, grp_id) is the primary key of this table so that the same group id can't be assigned twice for the same week, as a bonus this means only 1 person from a specific group per week. The (grp_id, prsn_id) is a reference to person_in_group (or person table depending on your setup) and must match a row there.

 

until I read this article.  It is a great explanation of composite primary keys, and why we should use them.  Thank you for introducing them to me

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

[ignace]

Each department has it's own group. Each person is in one group only.

 

Own group or groups? 1 department has 1 group or 1 department has many groups? If the former then you can just add a dpt_id to the groups table (or a grp_id to the departments table, either way works). If it is the latter then you'll need a department_has_group table with (dpt_id, grp_id) as columns.

 

If group A has members Bob, Joe, Mary, Frank and John they will always be on call in the same order.  Bob then Joe then Mary then Frank then John then Bob again....

This is true for three of the four groups.  The fourth group has nine employee's and the rotation repeats every 18 weeks. so using numbers in place of the name for this example:

1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9,8,7,1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9,8,7,1,2,3,4,5,6,7,8,9,5,6,2,3,4,1,9 would be the whole rotation for the year for this group.

 

You will have to populate this from your application and store it in the schedule. Afterwards you can just query the schedule to find the person's on call for that week.

 

I am unfamiliar with a cron could you please elaborate?

 

http://en.wikipedia.org/wiki/Cron

 

This will be a dynamic web page.  So today someone could log onto the web page and want to know who was on call Jan 18, 2012.  If someone was added to the group since then how would the query know not to include them when calculating the on call person?

 

Because he won't be in the schedule table in the week of 18 jan 2012. If you want to be able to make these specific lookups the schedule table will need to be modified to use a DATE instead of (week, year). I don't have the time now but I'll create a new schema matching this, this evening.

 

So you would suggest that the schedule table holds 208 entries, four entries for each week of the year.  I had not thought of that.

 

Yes, for each year. Using a cron you will populate the schedule every week for the next week/month/year so that everyone knows there schedule in advance. Are there only 4 people on call each week? Know that when this changes you'll need to create a new group to be able to assign a 5th person.

 

After the things you told me so far I think the current schema does not match this, when I get home I'll have another look at this and have a stab at a better schema.

 

[falian92]

Own group or groups? 1 department has 1 group or 1 department has many groups?

 

There are a total of four departments and a total of four groups, one department per group.

 

After the things you told me so far I think the current schema does not match this, when I get home I'll have another look at this and have a stab at a better schema.

 

Thank you

[falian92]

I did some research on cron.  Thank you for the link.  This application will be running on a Windows Server though.  Cron seemed to be a Linux/Unix job scheduler.  Will this make a difference?

[Christian F.]

The only difference is that you can't use cron, but Windows Scheduler instead. Other than that, it will not matter at all to the PHP script (which doesn't care from where it's called).

[falian92]

Thank you ChristianF

[Christian F.]

You're welcome.

[ignace]

department (dpt_id, dpt_name, ..)
group (grp_id, dpt_id, grp_name, ..)
person (psn_id, grp_id, ..)

schedule (sch_id, sch_date, psn_id, sch_comment, ..)

 

This schema is much easier to work with and does not impose the same constraints as the previous would. Meaning that you will have to enforce these in your app. instead.