映射多對多關係

我有兩張桌子：

1. 包含employee_id（主鍵）和employee_name 列的員工表。
2. 包含 company_id（主鍵）和 company_name 列的公司表。

這些公司允許其員工為其他公司工作。所以一個員工可以在很多公司工作，一個公司可以有很多員工（多對多關係）。

假設我有 3 名員工和他們工作的公司，一天的開始和結束時間各不相同。

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

• 我應該如何設計數據庫？
• 如何找到在給定公司工作時間最長的員工？

您將同時擁有員工表和公司表來儲存員工和公司資訊。但是您需要另一個表來表示該關係，因為它是多對多關係。

同樣在這裡，工作時間資訊是一個關係屬性。在員工開始為公司工作之前，它不存在。

ER圖將簡單如下：

當您映射此關係時，您將有一個表 company_employee(employee_id, company_id, work_hours)

您的表的 SQL 程式碼：

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

在 company_employee 表中，您也可以將工作時間儲存在單個列中，具體取決於您的需要。

要查看列，

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;

最後，這將顯示誰在哪個公司工作了多少小時：

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;

引用自：https://dba.stackexchange.com/questions/151904