Database tables design problem

birksy30

Hello,

I am trying to design a web based staff rota for my office. I'm going mad trying to design and relate the tables to one another.

I've attached a picture of what I am trying to achieve and the tables I've come up with so far. I can relate 'employees' to the 'shift patterns' tables by the 'id' column in each table, but I cannot work out how to relate the 'employees' & 'shift patterns' tables to the 'date' table.

For info: The entries 'N' 'E' & 'L' relate to the shift the employee is working on the specified date. (N=Night shift, E=Early Shift, L=Late Shift).

If anybody can help or offer suggestions I'd be soooooo grateful!!

I'm still quite new to PHP & MYSQL, so please forgive my ignorance.

Roger_Ramjet

Not a lot of use posting your table layouts if you think they are wrong. 😃

You are having trouble 'normalising' your data. If you want help then you need to tell us what the data is in the real world as well as what you have come up with so far. Then we can help you to fix your layouts.

Questions that need asking
1) is the shift designed to give 24/7 coverage
2) how are individual's shift rotations worked out
3) is there the same staffing level on all shifts
4) what is the normal rotation pattern eg: 4 nights, 2 off, 4 days, 1 off, 3 evenings, 3 off - repeat: or whatever it is
5) are all staff on the same rotation pattern
6) how are holidays/sickness covered

Write it in english as a series of simple statements, hopefully in some sort of logical order. With the right info we can help you come up with a design/app that will work for ever - or until the real world changes the shift patterns.

birksy30

Hello RR,

Thanks for the reply. You're right, my table layouts aren't much use if they are wrong. I thought maybe it would help explain what my problem is.

I've attached a mock up of what I am trying to achieve with a little explanation included. I hope it all makes sense. 😕

To be honest I think I may have bitten off more than I can chew. The more I think about this the more complicated it gets.

Have a look and see what you think. I'll understand if you see it, take a gasp and never reply to me again 🙂

(I've just realised something I missed of the mock up. On the tables showing the 6 week pattern I've got some entries that are 'E/L', this purely means that every other 6 weeks the staff would either do an 'Early' or a 'Late' Shift on that day.
Also, Sickness would be reflected on the rota with an 'S', Training would be a 'T')

Kind regards,
James

birksy30

Sorry forgot attachment!!! :queasy: