Mysql – Mapping many-to-many relationship

database-designmany-to-manyMySQL

I have two tables:

Employee Table with columns employee_id (primary key) and employee_name.
Company Table with columns company_id (primary key) and company_name.

The companies allows its employee to work for other companies. So an employee can work in many companies and a company can have many employees(MANY TO MANY relation).

Say I have 3 employees and the companies they work for with the respective start and end time for a day.

employee_name | company_name | hours they work |
Akash            A               09:00 - 11:00                            
                 B               12:00 - 02:00                       
                 C               04:00 - 07:00  

Sunny            D               09:00 - 11:00
                 C               12:00-  04:00
                 D               05:00 - 07:00 

Vishal           B               09:00 - 12:00 
                 A               12:00 - 05:00

How should I design the database?
How do I find the employee who worked the most hours for a given company?

Best Answer

You will have both employee table and company table to store employee and company info. But you need another table for the relation since it is a many-to-many relationship.

Also here, the work hours info is a relation attribute. It does not exist until an employee starts to work for a company.

The ER diagram will simply be as the following:

When you map this relation, you will have a table company_employee(employee_id, company_id, work_hours)

Your SQL code for the tables:

CREATE TABLE employee (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE company (
    company_id INTEGER PRIMARY KEY,
    company_name VARCHAR(300) NOT NULL
);

CREATE TABLE company_employee (
    employee_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    work_hour_start TIME NOT NULL,
    work_hour_end TIME NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (company_id) REFERENCES company (company_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (employee_id, company_id, work_hour_start, work_hour_end)
);

In the company_employee table, you can store work hours in a single column too, depending on your needs.

To view the columns,

SELECT e.employee_name, c.company_name, ec.work_hour_start, ec.work_hour_end
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id;

Lastly, this will show who worked how many hours for what company:

SELECT c.company_name, e.employee_name, MAX(ec.work_hour_end - ec.work_hour_start) AS max_hours
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id
GROUP BY c.company_name, e.employee_name
ORDER BY c.company_name;

Related Solutions

MySQL Workbench – question about creating FK relationships

It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).

An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.

Identifying relationship

Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.

What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.

A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.

Non-identifying relationship

Technically this should resolve your problems, but there are still potential issues with the underlying database design.

For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.

This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.

If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.

(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)

Another Identifying Relationship

I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.

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

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;
                                        )

Best Answer

Related Solutions

MySQL Workbench – question about creating FK relationships

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

Related Question