I'm attempting to make a web-based scheduling system, but I'm having trouble figuring out how to make it work in the best manner.
First, the current table structure:
Shift_ID (pk) Event_ID (fk) Dept_ID (fk) Start End
Entry_ID (pk) Shift_ID (fk) User_ID (fk) DateAssigned Position Notes
Where pk is a primary key, fk is foreign key. Not shown is a departments table, events table, and users table where the IDs come from.
How I have it now is that a user creates an event, then adds shifts, and then is able to assign users to that shift. The app (through PHP/jQuery) then creates a new row in the shifts_assigned table for each user that was assigned - simple enough. Where I run into problems is figuring out how to edit previously entered rows.
This is the interface:
The +/- buttons add and remove rows. If a shift is selected that already has values, they are loaded into the appropriate number of rows. That's where my problem lies. From that interface, how would you track the addition/editing/deletion of rows? This aspect may be better suited in another forum, but if there are any MySQL query tricks or table restructuring I'm open to suggestions.
Thanks in advance!