Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Post I took an example ERD, but ask if what I found in it, is a flaw

    Hi,
    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.
    Thanks
    Attached Thumbnails Attached Thumbnails entity relationship diagram for a supermarket pay database.jpg  
    Last edited by brunobiondo; 03-29-12 at 12:02. Reason: minor corrections

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    Your proposed relationship is redundant.

    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.

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    Thank you reaanb.

    Quote Originally Posted by reaanb View Post
    .
    .
    .
    Perhaps more could be done to enforce validity.
    How could one enforce validity ?

  4. #4
    Join Date
    Feb 2012
    Posts
    76
    One could model the relationship between users and departments over time, then constrain the Shifts table accordingly.

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    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....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •