6

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?
10

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,
    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;
  • Also I need to add the hours of Sunny visiting the same company twice to get the max hours,what do you suggest? – user4946127 Oct 10 '16 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 Oct 10 '16 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 Oct 12 '16 at 0:40

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.