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

Question

I have a entity for example employe(key employe ID) and every employe can have multiple responsibility, while every responsibility can be tasked to multiple employes.

I have 3 options:

1. create weak entity - responsibility when the key is responsibility name and employe ID from employe entity. And those to entities will have many-to-many relation
2. make a relation employe-employe when the relation will hold attribute responsibility.
3. add attribute to employe as a key(ID,responsibility)

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

Answer 1

Usual caveat: assuming I understand the problem correctly ...

@GordonLinoff is correct. You already have an Employee table. If each employee can have multiple responsibilities, then you need a Employee_Responsibility table, with one record for each combination of employee and responsibility. You most likely also need a Responsibility table, and Employee_Responsibility then consists of two foreign keys: employee_id and responsibility_id.

I don't know what data you keep on "responsibility". I'd guess at a minimum there is some sort of text to describe it. Such text does not make a good key, as it tends to potentially be long and may be changed from time to time, so you should have some sort of responsibility_id, a short code or an arbitrary integer identifier or some such, and so then you need a Responsibility table to relate the code to the text. Likely there is other data.

RE your suggested solutions, let me take them in reverse order:

If I'm understanding your number 3, you can't add responsibility to the employee record because an employee can have multiple responsibilities. If an employee has 2 responsibilities, that would force you to create 2 employee records for him, and duplicate all the other data.

Number 2 won't work because there is no other employee associated with a responsibility. If Bob has a responsibility of "shoe department clerk", who would be the other employee in the relationship? I suppose you could link him to another shoe department clerk, let's call him Charlie. But if Charlie quits, does Bob then cease to be a shoe department clerk? Probably not. That said, if the nature of a "responsibility" is that it DOES involve another employee -- like "responsibility" means "manager over this set of employees", that would be different. I'm assuming that's not what you had in mind.

RE number 1, it's not a weak entity because you said that responsibilities are not unique to an employee. A weak entity is when you have an entity that is dependent on another entity. Like if you have orders, you might have an order header that identifies the order number, date the order was placed, name and address of the customer, method of payment, etc. Then you would have order items, a set of the individual things ordered. Order items hang off the order. You cannot have an order item without an order, and a given order item can only be part of one order. (Multiple orders might include the same product, but that's different. An order item record would probably have some sort of product id as part of the record, but it would also have data not applicable to other orders, like a quantity.) But in this case, a responsibility is not unique to an employee. Many employees can have the same responsibility.

Answer 2

You have a many-to-many relationship. The canonical solution is to use three tables:

• Employees
• Responsibilities
• EmployeeResponsibility

The last table is a junction table that has one row per employee and per responsibility.