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.
Best 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
useridas the primary key. Rather, create a surrogate primary key for the employee and makeuserida nullable foreign key.