1

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.

2

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;
                                        )
  • 1
    Thank You I will use yours first solution (I am new to designing databases so for me this is not so obvious) – Panteleev Dima Dec 17 '14 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.