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!

  • 2 Contributors
  • 2 Replies
  • 263 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by randomkid73
Member Avatar for mlesniak

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.