Issue with forming correct relationship between subtype and another table in ERD

Question

The focus is on the Cashier table and the Cash Accountability table. The rule is that only Cashiers have Cash Accountability and a Cashier ideally only cashes out once per day. Before I changed the ER diagram to what it is now, the CashID was a foreign key in the Cashier table, but there was nothing in the Cash Accountability table to identify which cashier the data belonged to. So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

I am unsure of how to get the relationship between the two tables correct and I would appreciate some guidance in the right direction.

Screenshot of the ERD

Answer 1

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.

So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

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.