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.

There is a table users with autoincrement primary key id.

There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not autoincrement.

employees.userid should be a foreign key for users.id.

Which kind of relationship in Workbench it should be?

As far as I understand:

  • It cannot be 1:1 relationships because for a given user the corresponding employee may not exist.

  • It is not advisable to be 1:n relationship because n can be only 0 or 1.

  • It should be an identifying relation because a user is determined by an employee.

Please advise me what is the best relationship in this case.

share|improve this question
    
It is an identifying relation because an Employee cannot exist in the absence of a corresponding Person. It will also be a (0,1) to 1 relationship, as you described. –  Pieter Geerkens Apr 26 '13 at 12:59
add comment

1 Answer

I think it would be best that you learned about relationship types without using an IDE.

If every employee is a user, then it should be a straight 1:1 relationship.

If every employee is not a user, then you should not be using userid as the primary key. Rather, create a surrogate primary key for the employee and make userid a nullable foreign key.

share|improve this answer
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.