Can I use many-to-many relation with weak entity

database-designforeign key

I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees.

I have 3 options:

1) Create weak entity – responsibility when the key is responsibility name and employee ID from employee entity.

And those entities will a have many-to-many relation .

2) Make an employee-employee relation which will hold attribute responsibility.

3) Add attribute to employee as a key(ID, responsibility)

Is first option even possible!!! And what is the correct option.

Best Answer

I'd say that this is pretty standard stuff ;

Create table employees (id int primary key, 
                        name varchar(100)
                        -- Plus some additional columns
                        ) 

Create table responsibilities (id int primary key, 
                               description varchar(100)
                               --Plus some additional columns;
                               ) 

Create table employee_responsibilities ( employee_id int foreign key references employees(id), 
                                         responsibility_id int foreign key references responsibilities(id) 
                                         --Plus some additional columns;
                                       )

You might want to have a surrogate primary key on the relationship table ;

Create table employee_responsibilities (id int primary key, 
                                        employee_id int foreign key references employees(id), 
                                        responsibility_id int foreign key references responsibilities(id) 
                                        --Plus some additional columns;
                                        )
Related Question