I built this ER diagram in MySQL Workbench and it will run and generate the database. My concern is that the relationship between the employees, departments, positions, and employment_statuses is a circular relationship.

Because I know people will ask, this is something I was working on in a class. I'm not asking for help with homework; this is something we were working on in class and I took a different route than the professor. I'm asking for clarification about database modelling theory.

enter image description here

  • Are employment_statuses relevant to Positions? Shouldn't Positions have its own Position_statuses table?
    – Hannah Vernon
    Commented Sep 16, 2013 at 23:16
  • Every time the employee would be promoted or their salary changed or or if they resign a new row would be created in employment_statuses for each change to keep a historical record.
    – acarbonaro
    Commented Sep 16, 2013 at 23:19
  • Sure, but that only necessitates the employees -> employment_statuses relation. Positions don't have employment_statuses, do they?
    – Hannah Vernon
    Commented Sep 16, 2013 at 23:23
  • 3
    I don't see a circular path here. I see 2 paths from employees to employment_statuses Commented Sep 16, 2013 at 23:29
  • 1
    I find it interesting that you identified as suspect the tables you did, but not the relationships between employees, employees_projects and projects -- it's the same situation with one less table. (There's nothing wrong with the design, I'm just giving you food for thought.)
    – Jon Seigel
    Commented Sep 17, 2013 at 16:48

1 Answer 1


There's nothing wrong with your model. Au contraire - it's pretty advanced!

employment_statuses provide data in a time-lined, historical, fashion, whilst departments identifies the actual manager.

These entities are very different both in purpose and use (queries on employment_statuses will serve different scenarios than those on manager).

I have to congratulate you, as I repeatedly work with peers that just can’t abstract this kind of scenario (the majority just see the now, or, at maximum, a single then - not the natural sequence of thens leading to the as today).

A remark: you should move high/low_salary from positions to another time-lined entity.

  • One more thing. My models always consider end_date = 2100/jan/01 as from now on. This provide simpler, more straightforward, queries: ... WHERE TODAY BETWEEN start_date AND end_date. The natural way of setting end_date = NULL leads to ... WHERE start_date <= TODAY AND (end_date >= TODAY OR end_date IS NULL). Of course, for input controls at the user interfaces, the code has to interpret 2100/jan/01 as blank, and vice-versa. Commented Oct 4, 2013 at 3:38
  • I'll mark this as correct after I have a discussion with my professor about the model again. Thanks a bunch!
    – acarbonaro
    Commented Oct 4, 2013 at 18:41
  • You're welcome. Accueil to the wonderful world of modeling and administering databases. Commented Oct 4, 2013 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.