Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Are employment_statuses relevant to Positions? Shouldn't Positions have its own Position_statuses table? –  Max Vernon Sep 16 '13 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 Sep 16 '13 at 23:19
    
Sure, but that only necessitates the employees -> employment_statuses relation. Positions don't have employment_statuses, do they? –  Max Vernon Sep 16 '13 at 23:23
3  
I don't see a circular path here. I see 2 paths from employees to employment_statuses –  ypercube Sep 16 '13 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 Sep 17 '13 at 16:48
show 4 more comments

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  mvaraujo Oct 4 '13 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 Oct 4 '13 at 18:41
    
You're welcome. Accueil to the wonderful world of modeling and administering databases. –  mvaraujo Oct 4 '13 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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