I have two tables:

  1. Employee Table with columns employee_id (primary key) and employee_name.
  2. 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?

1 Answer 1


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: er diagram

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,
    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;
  • Also I need to add the hours of Sunny visiting the same company twice to get the max hours,what do you suggest?
    – user2594
    Commented Oct 10, 2016 at 23:02
  • When work_hour_start & work_hour_end columns added to primary key, that problem is also solved. Check the updated CREATE TABLE company_employee query.
    – ukll
    Commented Oct 10, 2016 at 23:25
  • The work hours do not describe the relationship, they describe a particular work shift. Your description of a cross (aka intersection) table is valid -- until you designed it to have an entry for every work shift. The PK should be the employee id and company id. One entry for each relationship. The hours worked each day should be maintained is a different table altogether. You can have other data in the intersection table, but it must describe the relationship. The date, for example, the employee started working for the company.
    – TommCatt
    Commented Oct 12, 2016 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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