Hi, I 'm putting together an MS Access database for our employee records and I'm having a real problem designing the schedules table (or tables). I have the following three tables and I know the schedules table is deeply wrong but I can’t figure out how to design it in a good (normalized) way. Any advice would be sincerely appreciated!

(primary keys are listed first)
EMPLOYEES TABLE
Emp ID
Last Name
First Name
Street Address
City
State
Zip Code

ASSIGNMENT TABLE
Emp ID
Schedule Code

(there are 2 start and stop times for each day because some people work split shifts)
SCHEDULES TABLE
Schedule ID
Monday start time 1
Monday stop time 1
Monday start time 2
Monday stop time 2
Tuesday start time 1
Tuesday stop time 1
Tuesday start time 2
Tuesday stop time 2
.
.
.
Friday start time 1
Friday stop time 1
Friday start time 2
Friday stop time 2

Try this:

Schedule(ScheduleID, StartDate, EndDate)

Day(DayID, ScheduleID, DayName)

Shift(ShiftID,DayID, ShiftName, ShiftStart, ShiftStop)


That will give you considerably more flexibility.

You could also do this:

Schedule(ScheduleID, StartDate, EndDate)

Day(DayID, ScheduleID, DayName)

Shift(ShiftID, ShiftName, ShiftStart, ShiftStop)

DayShift(DayID, ShiftID)

In this way you could have a standard set of shifts and assign them to each day.

If you provided some more detail about how schedules are created, days assigned to them and shifts assigned to days we could narrow down which model or if another model is most suitable.

Thanks very much for the help. I think those ideas will do it.