This is non-specific to a particular Database system, my question has to do with how a table or a series of table might be setup I am decidedly, undecided on how to best set up this/these tables.
I am attempting to implement within a current RDB a way that people can be scheduled as in it is for scheduling, the reason it is to be in the database is to pull an hours report that is updated as the schedule is updated. Additionally the database contains all the employees therefore the forms use lookups from the employee tables to make scheduling easier for the scheduler.
I know the information that needs to be in this and I get the general idea of how it needs to be setup.
We need to have 7 days for the week and the vehicle and shift information (i.e the hours the shift is scheduled) so if we looked at it flatly on paper it would be a grid, where the shifts are veertical and the days are horizontal and by following the grid horizontally from a shift you find out who is in the shift for a given day, basically I think you get the idea of what it looks like. The data that is contained in each grid spot is as follows:
Comment for emp1 i.e. if they are leaving early/coming in late
Comments about specific shift i.e. It is for a special event
Then for a shift on the weekly basis ( for the entire week) we need a comments field so that for instance a comment may be made that on sat and sun the shift is not 0800-1600 it is 0900-1500 hours.
As I had it originally setup as it was all I could think of it initially is that each record is a shift for the entire week i.e. 0800-1600 shift would have 21 fields for emp named monemp1, monemp2, monemp3, tueemp1, tueemp2, ... and so on for the entire week. which seemed way too convoluted to me, but it worked, except I was never able to pull data properly to create an hours report. This was of no great consequence as the scheduler refused to use the DB to schedule and stuck to Excel spreadsheet. But there is a new scheduler who wants to use it, but I had to implement some changes anyways to extend how far out he could schedule therefore I thought it best to recify the haphazard table setup. Problem is I still can't figure out how to do it right.
Should I make a table for each day of the week and each record is the shift and employee info? (This doesn't seem to be logical for me because they need to add delete shifts on a weekly basis (i.e. for everyday of the ween and they are not going to want to have to do it each individual day)
Should there be one table for one week that contains all the data and each record be the shift and then the employee info for the entire week?
Should there be a sperate entry with shift info for each employee with the table setup as above in the previous example except that instead of three entries per record for employees there only be one, with duplication of the shift info for each record? (Seems this wouldn't work so well either due to similar problems as in question one above)
Should it be setup where there is a table of days, then a table of shifts, and the table of shift is one grid with the employee infor and then the shift/vehicle info with a field that corresponds to a day and have each. (Seems an awful lot like the first solution that I didn't think would work due to the need to add/remove shifts)
Is the way I have it setup now the mostideal for this situation?\
Thanks in advance for any help, it will grreatly be appreciated.