I took an example ERD, but ask if what I found in it, is a flaw
I took an example ERD image on the web, used as a reference, it's a class diagram modeling a simple entities relationship for a supermarket pay database.
I ask if what I found in it is a flaw. Attached herein the model diagram.
One more point: if I'm interested in tracking shifts worked over time in the shifts table, I'm still able to do so.
Any comment would be appreciated.
Last edited by brunobiondo; 03-29-12 at 12:02.
Reason: minor corrections
I suspect the department_id in Shifts was meant to capture the department for which the shift was worked, while department_id in Users records the user's current department. This is not a flaw, but a way to preserve historical information. Perhaps more could be done to enforce validity.
I understand Reaanb, in fact,with my modified model,there is a limitation: if my Shifts table traces shifts over time (that's reasonable as you suppose), then if I want to be able to change a department for a given user ID, since the existence of referential constraints with the parent Users table (that is not a fact table), I should first set nulls or 'cascading' delete the child rows in Shifts, and then I can change the department in Users table, thus I would no longer be able to trace past shifts for that DepartmentID/UserID....
Since my maniacal inclination for using as many constraints as possible for enforcing integrity at database level, if I wanted to fix and enforce the mentioned limit at database foreign key/check level, I'm still not able to answer, I'm doing some tests without results for now. Obviously I could perform all checks programmatically, at code level....