Database Design For a Roster System

[nqaul]

Hi,

I'm building a sub-system that will auto generate
staff rosters, so far I've got the following tables:

Staff (sid, Name, etc...)
Roster(rid,startdate,enddate)
Date(dateid,date)
StaffDay(sid,rid,dateid,starttime,endtime,duty)

sid,rid,dateid in the StaffDay table will be enforced
as a composite unique key.

Each staff member can have a different start/end times
on any given day and be working any day in any week.
I don't like using the composite key(just doesn't feel
right) or that I'll be uniquely identifying dates, seeing
as how a date is already unique but I'm at a loss of how else to do it.

 

[ceh4702]

Looks like the roster is like a time card program. You may also want to know the building and room number. What are these staff rosters for is it a school or something. We put the instructor ID in the Course File at our school. Since the coures is already associated with a time and location that works.



If you do not like my post feel free to point out your opinion or my errors.