Member Avatar for randomkid73

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:

shifts

Shift_ID (pk)
Event_ID (fk)
Dept_ID (fk)
Start
End

shifts_assigned

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:
schedule_dialog

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!

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.

Member Avatar for randomkid73

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.