CODEWITHSUNDEEP
database-designrelational-database
Sam Stephenson
Sam Stephenson

Reputation: 5190

can an attribute have multiple relationships

Maybe this is not the best way to go about this ... But if it is I want to check as I have not seen any EER models showing this.

Say you have the entities Employees, Managers and Workers

An employee is either a manager or a worker

employees(Username PK, password, name)

manager(manager_ID PK, shift, department)

workers(worker_ID PK, hour_per_week, manager_ID FK)

Can I insert ID as a attribute to employees which is the primary key of either manager or worker.

Or do I add username as a foreign key to both managers and workers

Basically how do I link employees to both workers and managers depending on who they are?

Upvotes: 0

Views: 64

Answers (1)

aneroid
aneroid

Reputation: 15962

Can I insert ID as a attribute to employees which is the primary key of either manager or worker.

No. A primary key of '1' in the employee column refers to which? A manager ID or worker ID? Both tables probably have entries with an ID of 1, so that's not a differentiator unless you add another column like is_manager.

Or do I add username as a foreign key to both managers and workers.

Yes - the employee PK should be the FK for manager and employees. In fact, you then won't need an additional/separate PK for the manager or employee since you can re-use the employee PK.

Also, even if you are using 'username' as a PK, if it can ever be changed, it's better to keep the PK as a number.

Upvotes: 1

Related Questions