Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Table design for roster system

    Hello there.

    I'm designing a database to be used in a roster system.
    The tables will need to hold the following info.

    roster_id(0,1,2....)
    staff_id
    roster_startdate/enddate
    staff_hours (unique to each staff member, one per day)
    assigned duty

    I was thinking along this sort of line:


    Roster_Table
    r_id,
    sdate
    edate

    Staff_Hours_Table
    staff_id
    date
    hours
    duty



    I can do the query to pull the info i want out but the tables just
    don't feel right. Its a while since I've done any DB work so maybe
    I'm missing something but should there not be a forigen key relationship
    between these. Also the staff_id and date in Staff_hours jointly form
    the primary key I'd rather not have this either.

    Any help will be very greatly appriciated.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You need something like the following

    Staff(StaffID, StaffName)

    Roster(RosterID, StartDate, EndDate)

    Date(DateID, DateName) This gives you a Date Dimension.

    DutyRoster(RosterID, StaffID, DateID, Duty, Hours)

    You would have to enforce a composite unique key on the three IDs which is kosher.

  3. #3
    Join Date
    Nov 2004
    Posts
    2

    Thanks

    Cheers mate that'll work a threat I think

Posting Permissions

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