Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    11

    normalization problem

    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

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.
    Last edited by certus; 05-30-04 at 20:53.

  3. #3
    Join Date
    May 2004
    Posts
    11

    Thanks

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •