What will be the relation between Employees and Players, given that some players are also Employees

database-designerd

I have a requirement where I need to store information on Employees in a company that owns(?) a sports team and also store information on Players from other teams that are not Employees of this company. The Players in the team(i.e owned by the company) are also considered Employees hired by the company.

For the design, what I have done is create two Entities called Employees and Players and have a Player id key as FK in the Employees Entity. The Employee Entity also has Employees that are just employees and not players. With the player id fk, Employees that are Players will have a player id fk, while other employees will not. That way I have information about Employees, player or just normal employees, in Employee entity. And in the Players entity I only have information specific to all the players hired by the company or just other players from other teams(not owned by the company). Is this the right way to go? I haven't shown the subclass/subtype relation between Employees and Players entity because not every player in the Players entity is an Employee. Am I in the right direction?

I know this is not the very best explanation I guess but please do ask for any clarification. Thank you

Best Answer

can you give an example of exactly why there's a N:N with unique constraints for both Emp_id and player_is?

CREATE TABLE employee (employee_id INT PRIMARY KEY, ...);
CREATE TABLE player (player_id INT PRIMARY KEY, ...);
CREATE TABLE junction (employee_id INT NOT NULL,
                       UNIQUE (employee_id),
                       FOREIGN KEY (employee_id) REFERENCES employee (employee_id),
                       player_id INT NOT NULL,
                       UNIQUE (player_id),
                       FOREIGN KEY (player_id) REFERENCES player (player_id));

Unique constraints forbid one employee to be two separate players and backward.

If a player is not an employee than junction table does not contain a row for him, and backward.

Related Question