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)


Entry_ID (pk)
Shift_ID (fk)
User_ID (fk)

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!

5 Years
Discussion Span
Last Post by randomkid73

If I was designing something like this, I would do it the other way around. By which I mean that, rather than inserting a row with empty columns, I would only insert rows that have data in each column, especially columns that are keys. When each new row is inserted you can query the database table to re-display the current rows then prompt for a new row's details (either at the bottom or at the top of the returned results).
I wasn't quite sure, from your interface image, how the number of blank rows was determined. Perhaps it was a static page rather than a dynamic one.


Well, the screen shown was for adding records, so if I understand correctly your idea doesn't work. For editing records, only displaying rows with records makes sense though, and that is how I planned on doing it.

The number of form rows is determined by the user. Only 1 row is shown initially, and then they add however many rows they need with the "+" icon.

I think I have a solution, however the current interface won't work. Thanks for your help though!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.