Database structure suggestions needed

[AndyB]

I've a 'relatively simple' problem and no good idea as to how to make it a manageable database structure.

The business has 50+ part-timers. The business operates 3 shifts/day most days and occasionally has a fourth shift. Each part-timer can offer to work as many shifts as they want and for whichever days of next month they want to work. Then the part-timer has to submit their offer before the middle of the month (for work next month). The manager then looks at who can work each particular shift and accepts offers from as many people as he needs from those who offered to work and post the next month's schedule.

I'm toying with a table structure that looks like this for storing the 'offers to work':
id - auto-increment
date (of the day of the month)
shift1 - varchar
shift2 - varchar
shift3 - varchar
shift4 - varchar
shift4_true - enum (Yes/No)

Each user has a unique user_id and I'm thinking that as they add their offer for any shift/day the appropriate shift# field gets |user_id added to its contents, i.e the value of shift# is a pipe-separated string of user_ids who've offered to work that shift for that day.

logged-in users need to be able to make selections, change their minds, save their choices, etc. until they 'make their final offer', i.e. their offers are editable.

This approach looks OK on paper, but I'm afraid it'll be pretty clunky to use especially when users want to edit their offer before finalizing. Having separate records for each day for each user seems cumbersome as well, especially when I want to display (for management) all work offers made for the whole month.

I'm open to suggestions as to how to organize the data so it's easily retrievable for a single user (for editing) or for management to see the overall picture.

[fenway]

Sounds like you need more tables -- namely, one that stores a series of records tying dates and shifts (making it easy to have 3, 4 or N per day). Then you have another table that it your offers table, where to pull the date_shift_uid and the user_uid. That should be sufficient to make everything relatively easy to query and edit. Having delimited fields is a nightmare, so you shouldn't even consider that option if you ever want to manipulate the data stored in these fields (which you do).

Hopefully that points you in the right direction (or, more importantly, away from the wrong one ) -- I can be more precise with my suggestions in need be. Ultimately, tables are free, it's just another JOIN or two, and it'll be worthwhile in the end.

[AndyB]

Thank you. Being pointed away from the wrong direction and towards the right one was just what I was hoping for. I'd been considering the gymnastics that the delimited data might require in the retrieval and editing steps and having some brain overloads so I *knew* there had to be a better way.

I'll plot out your suggestion and see if it brings me a clearer picture of how this could all work. Then I'll be back with a better concept (and probably a request for more help).

[fenway]

Sounds good -- always nice to help out a fellow Torontonian.