Hi all,
I'm a student doing a project for coursework, but I'm also using this project for the company I work for. I am using MS Access to make a database to store employee details, the shifts, and the day they work. The outline for the database looks like this:
Lifeguard(lgID, fname, sname)
Shifts(ShiftID, startTime, endTime, shiftFrequency, weekStart)
DayWorked(lgID, ShiftID, Day)
I don't really know how to structure the DayWorked table to get the desired outcome. My problem is, Lifeguard should have a many-to-one relationship with DayWorked. Same with Shifts. However, when I click 'enforce referential integrity' on Access, it claims that it is one-to-many which is the opposite of what I need. I have tried messing about with the primary keys of DayWorked but nothing has solved my problem. I'm at my wits end and I don't know what's wrong with it.
An example of the relationship would be "a lifeguard cannot work multiple shifts on the same day, but can work multiple days and multiple shifts". Same concept with Shifts.
I'm really not sure what to do from here, I've read so many other posts but still don't understand. Any help would be massively appreciated. Thank you!