I think you'll want an EmployeeCanAuthorizeJob
table and an EmployeeCanAdministrateJob
table for a straight many-to-many representation ( right now, I think you'll find you'd be duplicating your job records with the current structure ). This way, you could tie many EmployeeID
s to JobID
s and vice versa for either of the management tasks.
That said, I would likely only end up creating an EmployeeCanManageJob
table, along with possibly a secondary look-up table ManageJobType
. The purpose of the second table would be to include the functions an employee can perform on a particular job. Today you've got two, but who knows if CanDelete
of whatever needs to be broken out later down the line?
EmployeeCanManageJob
------------------------------------------------
PK | Employee_FK FOREIGN KEY Employee.EmployeeID
PK | Job_FK FOREIGN KEY Job.JobID
(PK | ManageJobType_FK FOREIGN KEY ManageJobType.ManageJobType_PK)
This way, if ManageJobType_PK
1 was "Administrate" and 2 was "Authorize", employee 1 could administrate job 1 with a ( 1, 1, 1 )
record and authorize the same job with a ( 1, 1, 2 )
record.
As per the edit:
There at least two popular methods of change tracking: the first being an audit table, the second being some form of slowly changing dimension. To save you a little grief down the line, you are not dealing so much with a "slowly changing dimension" here as you are a "rapidly changing fact," if you will, so I expect you will find yourself looking at one of the numerous ways to implement the audit table scenario. Which method you choose will largely be subject to you and your team's comfort level with the technologies involved ( or more specifically, each options advantages and disadvantages ). I've personally always been a fan of the log trigger approach and think it would be more than suitable for your needs here, but if you have out-of-the-box capabilities available to you, like in Sql Server, perhaps you and your team would be more comfortable with that method.
There is more than one school of thought regarding the relationship between conceptual data modeling and logical data modeling. These schools of thought often result in similar practices, but differences in terminology can be confusing, especially in a formal learning situation.
Conceptual data modeling, as I learned it, follows ER modeling as first popularized by Peter Chen. If you look at the Wikipedia article on Chen you'll see a para on ER modeling and conceptual modeling. If your teacher's framework agrees with this one, then perhaps her ER diagrams depict an ER model, and not a relational model. If that's true, then normalization questions ought to be deferred until after the entities and relationships are agreed on.
If, on the other hand, your teacher's framework is the same as that of Pieter's excellent response, then Pieter's response should guide you.
Either way, you need to keep clear on the differences between analyzing the subject matter and designing useful tables that reflect that analysis.
Best Answer
I actually think your diagram is good for your rule. The only thing is that with the relationship line connecting "Cash Accountability" and "Cashier", I would remove the circle on the Cashier side, to indicate that a "Cash Accountability" has to have exactly one Cashier.
The relationship is simply saying that a cashier can cash out more than once (not necessarily more than once per day), which is technically correct.
You could add a unique constraint to the implementation of your data model between "Cash Accountability"'s EmpID and Date fields in order to enforce that a Cashier cannot cash out more than once a day. Here's how this would be done in MySQL: https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql
I'm not sure if there's a "correct" way to display this unique constraint in an ER Diagram, but I suppose you could just add a comment.