Database structure suggestions needed
Posted 08 June 2006 - 12:55 AM
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.
Posted 09 June 2006 - 06:04 AM
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.
Posted 09 June 2006 - 12:17 PM
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).
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users