Mapping many-to-many relationship

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?

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;

StackExchange Database Administrators Q#151904, answer score: 16