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.

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.

Cheers mate that'll work a threat I think