Computer Science Normalisation Help?Watch
In your example, it sounds like there are a couple of different entities, with one entity being your worker, and the other entity being the shift they work.
e.g. -- Keep in mind that SQL only supports 3 different possible direct relationships (Constraints) between tables, which of the following makes most sense in real-world terms?
- Many employees work one shift (Seems very odd to me, the employer is only going to be paying one person for that shift once, so multiple employees should probably not be allowed to work one person's shift, that doesn't really make sense to the real world, so it doesn't make sense in a database either)
- One employee can work one shift (Also seems odd, unless no employee can ever last more than one shift in their job)
- One employee can work many shifts (Makes sense - an employee might work hundreds of shifts in their job, so that also makes sense in the database)
You didn't really mention anything about the actual data, but from a real-world perspective, an employee would usually have some kind of Employee number or identifier (Maybe that would be their payroll number, or just an autonumber ID in a database, it doesn't really matter which - as long as there's a way to identify them). You mention that employees have a fixed wage rate (By which I mean each employee potentially has their own hourly rate, so that sounds to me like their hourly rate depends upon who they are, and nothing else -- or from a database perspective, the rate depends on their employee ID/payroll number). Obviously an hourly rate wouldn't be a key field either.
On the other hand, Each employee would usually work many shifts during their job; the hours for each shift depends upon who they are, but it also depends upon the day as well (I'd guess that a real payroll system would store datetime fields for the start and end of each shift, because SQL can easily calculate the number of hours between two datetime fields. That also solves problems like how to store overnight shifts, and what happens if someone works 2 shifts in the same day, etc).
However, using anything to do with the date/time as part of a primary key is probably a bad idea and difficult to work with, so for the sake of keeping things simple I'd expect there'd probably be an autonumber as a primary key for the shifts that they work too
(You usually can't go too far wrong if every table in your database uses an autonumber ID field as its primary key in any case - even if there is an obvious natural key to be found, it doesn't really hurt.)