Shift Booking System

[JRHughes]

Hey All,

I'm struggling with a project set to me that involves allocating shifts to staff that work within an Event Venue. Basically, staff only work when there are events on.

Therefore, I have a database that contains tables showing:
- Staff
- Events
- Shifts (for the above events)

The above are all linked by relationships in the database.

However, I am bewildered as to how I can have say 15 staff needed to do the same shift.

For example,

Elton John is playing on May 1st 2010 and requires 20 staff to work from 6pm until 11pm. What would be the best way to store/allocate this?

Thanks

[jwenting]

if you can't store all the data you need to store given your current datamodel you need to change that datamodel.

Without knowing the actual use and structure of the tables though that question is impossible to answer.
Were I to have created this database I'd have included a table linking staff to specific shifts for example.

[Ionelul]

The relationship between Staff and Events tables is many to many, so you have to create an intermediate table, StaffXEvents with structure(StaffXEventsId int, StaffId int, EventId int) - StaffId and EventId being foreign keys to Staff and Events tables.
This way you associate to an EventId (Elton John's concert) all the staff you need.
I think an event may have multiple shifts, but
case 1: the shifts would be defined by the user for each event, so it would be enough for every shift introduced to alocate the EventId key
case 2: shifts are pre-defined and are standard(any new combination that user needs is introduced in db). In this case the relationship is again many to many and you have to do the same as for Event and Staff tables.

Cheers,
Ionut

[JRHughes]

Thanks to both of you for your responses.

I have linked my database tables (with an intermediate) in the way suggested above, however, I am struggling as to how to present this within Delphi.

The main user (staffing manager aka shift booker!) (ideally) should:
- Take phone call from staff member
- Type in staff member's name
- See shifts available to that staff member
- Allocate x amount
and eventually produce a 'rota' with all allocated shifts (to everyone)

This has been the most stressful project I have yet completed!

Cheers,
Jay