falian92

Thank you ChristianF

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?

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

I did not understand this part 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

Thank you for the response as well Ignace.

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. 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. Each department has it's own group. Each person is in one group only. Yes a real date (meaning a day) 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 am unfamiliar with a cron could you please elaborate? 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? 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.

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.