每位員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，並且要能夠紀錄員工的職務名稱，部門要能夠紀錄誰是部門經理、課長、主任等主管的關聯，並且要允許員工可以擔任多個部門的主管，例如某員工是技術部經理，並兼任管理部資訊長。其他未述明的需求，請自行合理化的假設。
(1) 請根據以上需求，畫出這個系統的實體關係圖 (Entity Relationship Diagram)，
請以陳品山博士提出的模式來繪製。
陳品山的實體關係圖模式可以參考這篇"實體關係模型(Entity-relationship model)"
從以上的需求，可以明顯看出有兩個實體 : 員工、部門。
並且由於員工異動歷程也要記錄，因此需要再透過一個任職的關聯 (這個關聯也會形成資料表)。再來還有一個需求，允許員工可以擔任多個部門的主管，這個部分也可以放在任職的關聯上，開個職務資料表，與任職以編號欄位來對應。
並且因為員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，員工可以擔任多個部門的主管，所以可以設計如下 :
當然以上的實體關係圖，不是唯一的標準答案，只要讓看的人正確理解即可。
(2) 請寫出建置資料表結構的MySQL語法。
我們把相關資料給 ChatGPT，如下圖 :
我們來檢查看看 ChatGPT 的結果是否正確。
-- 建立資料庫語法
CREATE DATABASE IF NOT EXISTS company_hr
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
-- 開始使用 company_hr 資料庫
USE company_hr;
-- 員工主檔
CREATE TABLE employees (
employee_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_no VARCHAR(10) NOT NULL,
employee_name VARCHAR(100) NOT NULL,
employment_status ENUM('在職', '留職停薪', '離職') NOT NULL DEFAULT '在職',
hire_date DATE NOT NULL,
resign_date DATE NULL,
UNIQUE KEY uk_employee_no (employee_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 部門主檔
-- parent_department_id 用來表示部門組織架構，例如：資訊部底下有系統課、網路課
CREATE TABLE departments (
department_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
department_code VARCHAR(30) NOT NULL,
department_name VARCHAR(100) NOT NULL,
parent_department_id BIGINT UNSIGNED NULL,
department_status ENUM('啟用', '停用') NOT NULL DEFAULT '啟用',
UNIQUE KEY uk_department_code (department_code),
KEY idx_parent_department_id (parent_department_id),
CONSTRAINT fk_departments_parent
FOREIGN KEY (parent_department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 職務主檔
-- is_supervisor_role 用來判斷該職務是否屬於主管職，例如經理、課長、主任、資訊長
CREATE TABLE job_titles (
job_title_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
job_title_name VARCHAR(100) NOT NULL,
is_supervisor_role TINYINT(1) NOT NULL DEFAULT 0,
job_level INT UNSIGNED NULL,
job_description VARCHAR(255) NULL,
UNIQUE KEY uk_job_title_name (job_title_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工電話
-- 將 ERD 中的多值屬性「電話」拆成獨立資料表
CREATE TABLE employee_phones (
phone_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
phone_type ENUM('公司', '手機', '住家', '其他') NOT NULL DEFAULT '手機',
phone_number VARCHAR(30) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_phone_employee_id (employee_id),
CONSTRAINT fk_phone_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 將 ERD 中的多值屬性「電話」拆成獨立資料表
CREATE TABLE employee_phones (
phone_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
phone_type ENUM('公司', '手機', '住家', '其他') NOT NULL DEFAULT '手機',
phone_number VARCHAR(30) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_phone_employee_id (employee_id),
CONSTRAINT fk_phone_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工電子郵件
-- 將 ERD 中的多值屬性「電子郵件」拆成獨立資料表
CREATE TABLE employee_emails (
email_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
email_type ENUM('公司', '個人', '其他') NOT NULL DEFAULT '公司',
email VARCHAR(255) NOT NULL COMMENT '電子郵件',
is_primary TINYINT(1) NOT NULL DEFAULT 0 ,
UNIQUE KEY uk_email (email),
KEY idx_email_employee_id (employee_id),
CONSTRAINT fk_email_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工地址
-- 將 ERD 中的多值屬性「地址」拆成獨立資料表
CREATE TABLE employee_addresses (
address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
address_type ENUM('戶籍', '通訊', '公司宿舍', '其他') NOT NULL DEFAULT '通訊',
postal_code VARCHAR(10) NULL,
city VARCHAR(50) NULL,
district VARCHAR(50) NULL,
address_line VARCHAR(255) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_address_employee_id (employee_id),
CONSTRAINT fk_address_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 將 ERD 中的多值屬性「電子郵件」拆成獨立資料表
CREATE TABLE employee_emails (
email_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
email_type ENUM('公司', '個人', '其他') NOT NULL DEFAULT '公司',
email VARCHAR(255) NOT NULL COMMENT '電子郵件',
is_primary TINYINT(1) NOT NULL DEFAULT 0 ,
UNIQUE KEY uk_email (email),
KEY idx_email_employee_id (employee_id),
CONSTRAINT fk_email_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工地址
-- 將 ERD 中的多值屬性「地址」拆成獨立資料表
CREATE TABLE employee_addresses (
address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
address_type ENUM('戶籍', '通訊', '公司宿舍', '其他') NOT NULL DEFAULT '通訊',
postal_code VARCHAR(10) NULL,
city VARCHAR(50) NULL,
district VARCHAR(50) NULL,
address_line VARCHAR(255) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_address_employee_id (employee_id),
CONSTRAINT fk_address_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工、部門、職務的任職關係表
-- 這張表對應 ERD 裡面的「任職」菱形
-- 可記錄員工在哪個部門、擔任什麼職務、任職起訖日期
-- 也可支援一位員工同時擔任多個部門主管
CREATE TABLE employee_department_job_history (
assignment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
department_id BIGINT UNSIGNED NOT NULL,
job_title_id BIGINT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NULL,
is_primary_department TINYINT(1) NOT NULL DEFAULT 0,
change_reason VARCHAR(100) NULL,
note TEXT NULL,
KEY idx_employee_history (employee_id, start_date, end_date),
KEY idx_department_current (department_id, end_date),
KEY idx_job_title_current (job_title_id, end_date),
UNIQUE KEY uk_employee_department_job_start (
employee_id,
department_id,
job_title_id,
start_date
),
CONSTRAINT fk_history_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_job_title
FOREIGN KEY (job_title_id)
REFERENCES job_titles(job_title_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 這張表對應 ERD 裡面的「任職」菱形
-- 可記錄員工在哪個部門、擔任什麼職務、任職起訖日期
-- 也可支援一位員工同時擔任多個部門主管
CREATE TABLE employee_department_job_history (
assignment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
department_id BIGINT UNSIGNED NOT NULL,
job_title_id BIGINT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NULL,
is_primary_department TINYINT(1) NOT NULL DEFAULT 0,
change_reason VARCHAR(100) NULL,
note TEXT NULL,
KEY idx_employee_history (employee_id, start_date, end_date),
KEY idx_department_current (department_id, end_date),
KEY idx_job_title_current (job_title_id, end_date),
UNIQUE KEY uk_employee_department_job_start (
employee_id,
department_id,
job_title_id,
start_date
),
CONSTRAINT fk_history_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_job_title
FOREIGN KEY (job_title_id)
REFERENCES job_titles(job_title_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
(3) 請寫出插入範例資料的MySQL語法。
USE company_hr;
-- =========================
-- 1. 新增部門資料
-- =========================
-- 第一層部門
INSERT INTO departments
(department_code, department_name, parent_department_id, department_status)
VALUES
('D001', '管理部', NULL, '啟用'),
('D002', '技術部', NULL, '啟用'),
('D003', '業務部', NULL, '啟用');
-- 第二層部門
INSERT INTO departments
(department_code, department_name, parent_department_id, department_status)
VALUES
(
'D101',
'人資課',
(SELECT department_id FROM departments WHERE department_code = 'D001'),
'啟用'
),
(
'D201',
'系統課',
(SELECT department_id FROM departments WHERE department_code = 'D002'),
'啟用'
),
(
'D202',
'網路課',
(SELECT department_id FROM departments WHERE department_code = 'D002'),
'啟用'
);
-- =========================
-- 2. 新增職務資料
-- =========================
INSERT INTO job_titles
(job_title_name, is_supervisor_role, job_level, job_description)
VALUES
('資訊長', 1, 1, '負責公司資訊策略與資訊系統規劃'),
('經理', 1, 2, '負責部門管理與決策'),
('課長', 1, 3, '負責課級單位管理'),
('主任', 1, 4, '負責基層單位管理'),
('工程師', 0, 5, '負責系統開發、維護與技術支援'),
('專員', 0, 5, '負責行政、業務或專案執行'),
('業務代表', 0, 5, '負責客戶開發與業務推廣');
-- =========================
-- 3. 新增員工資料
-- =========================
INSERT INTO employees
(employee_no, employee_name, employment_status, hire_date, resign_date)
VALUES
('E0001', '王大明', '在職', '2022-03-01', NULL),
('E0002', '李小華', '在職', '2023-05-15', NULL),
('E0003', '陳志強', '在職', '2021-08-10', NULL),
('E0004', '林美玲', '在職', '2024-01-08', NULL),
('E0005', '張雅婷', '留職停薪', '2020-11-20', NULL),
('E0006', '黃俊傑', '離職', '2019-06-01', '2024-12-31');
-- =========================
-- 4. 新增員工電話
-- =========================
INSERT INTO employee_phones
(employee_id, phone_type, phone_number, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'手機',
'0912-345-678',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'公司',
'02-2345-1001',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'手機',
'0922-222-222',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'手機',
'0933-333-333',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'公司',
'02-2345-1004',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'手機',
'0955-555-555',
1
);
-- =========================
-- 5. 新增員工電子郵件
-- =========================
INSERT INTO employee_emails
(employee_id, email_type, email, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'個人',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0006'),
'公司',
1
);
-- =========================
-- 6. 新增員工地址
-- =========================
INSERT INTO employee_addresses
(employee_id, address_type, postal_code, city, district, address_line, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'通訊',
'100',
'台北市',
'中正區',
'忠孝東路一段 1 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'戶籍',
'220',
'新北市',
'板橋區',
'文化路一段 88 號',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'通訊',
'403',
'台中市',
'西區',
'公益路 200 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'通訊',
'700',
'台南市',
'中西區',
'民生路二段 50 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'通訊',
'802',
'高雄市',
'苓雅區',
'三多二路 99 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'戶籍',
'640',
'雲林縣',
'斗六市',
'中山路 123 號',
1
);
-- =========================
-- 7. 新增員工、部門、職務任職紀錄
-- =========================
-- 王大明：技術部經理，並兼任管理部資訊長
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
(SELECT department_id FROM departments WHERE department_code = 'D002'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '經理'),
'2023-01-01',
NULL,
1,
'升任技術部經理',
'主要負責技術部管理'
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
(SELECT department_id FROM departments WHERE department_code = 'D001'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '資訊長'),
'2024-01-01',
NULL,
0,
'兼任管理部資訊長',
'跨部門兼任主管職'
);
-- 李小華：人資課課長
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
(SELECT department_id FROM departments WHERE department_code = 'D101'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '課長'),
'2023-06-01',
NULL,
1,
'任職人資課課長',
'負責人事管理與招募流程'
);
-- 陳志強：曾任系統課工程師，後來升任主任
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
(SELECT department_id FROM departments WHERE department_code = 'D201'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '工程師'),
'2021-08-10',
'2024-02-29',
1,
'初任系統課工程師',
'負責內部系統維護'
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
(SELECT department_id FROM departments WHERE department_code = 'D201'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '主任'),
'2024-03-01',
NULL,
1,
'升任系統課主任',
'負責系統課日常管理'
);
-- 林美玲：網路課工程師
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
(SELECT department_id FROM departments WHERE department_code = 'D202'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '工程師'),
'2024-01-08',
NULL,
1,
'新進員工任職',
'負責網路設備維護'
);
-- 張雅婷：業務部專員，目前留職停薪
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
(SELECT department_id FROM departments WHERE department_code = 'D003'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '專員'),
'2020-11-20',
NULL,
1,
'任職業務部專員',
'目前留職停薪'
);
-- 黃俊傑：已離職，保留歷史任職紀錄
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0006'),
(SELECT department_id FROM departments WHERE department_code = 'D003'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '業務代表'),
'2019-06-01',
'2024-12-31',
1,
'離職',
'保留歷史任職資料'
);
將以上結構建立在 InfinityFree 的資料庫上，如下圖 :
(4) 列出員工範例資料，包含員工姓名、員工編號、員工電子郵件、所屬部門名稱、職稱。
MySQL 語法如下 :
SELECT
e.employee_name AS 員工姓名,
e.employee_no AS 員工編號,
COALESCE(em.email_list, '') AS 員工電子郵件,
COALESCE(ph.phone_list, '') AS 員工電話,
COALESCE(ad.address_list, '') AS 員工地址,
COALESCE(jh.department_job_list, '') AS 所屬部門與職稱
FROM employees e
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(email_type, '：', email)
ORDER BY is_primary DESC, email_type
SEPARATOR '；'
) AS email_list
FROM employee_emails
GROUP BY employee_id
) em
ON e.employee_id = em.employee_id
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(phone_type, '：', phone_number)
ORDER BY is_primary DESC, phone_type
SEPARATOR '；'
) AS phone_list
FROM employee_phones
GROUP BY employee_id
) ph
ON e.employee_id = ph.employee_id
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(
address_type, '：',
IFNULL(postal_code, ''), ' ',
IFNULL(city, ''),
IFNULL(district, ''),
address_line
)
ORDER BY is_primary DESC, address_type
SEPARATOR '；'
) AS address_list
FROM employee_addresses
GROUP BY employee_id
) ad
ON e.employee_id = ad.employee_id
LEFT JOIN (
SELECT
h.employee_id,
GROUP_CONCAT(
CONCAT(
d.department_name, '／',
j.job_title_name,
'（', h.start_date, ' ~ ', IFNULL(h.end_date, '目前'), '）'
)
ORDER BY h.start_date
SEPARATOR '；'
) AS department_job_list
FROM employee_department_job_history h
JOIN departments d
ON h.department_id = d.department_id
JOIN job_titles j
ON h.job_title_id = j.job_title_id
GROUP BY h.employee_id
) jh
ON e.employee_id = jh.employee_id
ORDER BY e.employee_no;
輸出結果如下圖 :
最後再補個資料結構的 UML Class Diagram，請 ChatGPT 給我 Planttext UML，得到如下圖 :
職員與部門資料表，之間並不直接關聯，如下圖 :
而是透過 employee_department_job_history 表單來建立職員與部門之間的關聯，如下圖 :
另外職員的三個多值的電話、電子郵件、地址，也都各自形成資料表，如下圖 :
如果你對以上內容有疑問，不妨問問 ChatGPT，我也幫你問了 :
茲將 ChatGPT 的解釋摘錄於後 :
這篇文章本質上是一個 MySQL 資料塑模（Data Modeling）實作練習，主題是「公司員工與部門資料管理系統」。它不是單純教你建立幾張資料表，而是在示範：如何把一段模糊的企業需求，轉成 ERD（Entity Relationship Diagram，實體關係圖）、MySQL 資料表、範例資料，以及最後可查詢的報表。
文章一開始給出的需求是：企業要管理員工基本資料、部門組織架構、員工與部門的隸屬關係、員工異動歷程、聯絡資訊，並且要能做查詢、報表與統計分析。更重要的是，每位員工可以有多筆電話、多筆電子郵件、多筆地址，也可能同時擔任多個部門的主管，例如某人是技術部經理，也兼任管理部資訊長。 這篇文章最核心的觀念是：員工跟部門不要直接硬接在一起，而是透過「任職紀錄」這張關聯資料表來連接。
也就是說，資料表設計不是這樣想： 「員工表裡放 department_id 就好了。」
而是要這樣想： 「員工可能在不同時間屬於不同部門，也可能同時有多個部門職務，所以要用一張任職歷程表來記錄員工、部門、職稱、起訖日期。」
因此文章設計了 employee_department_job_history 這張表。它對應 ERD 裡面的「任職」關係，可以記錄某位員工在哪個部門、擔任什麼職務、從什麼時候開始、什麼時候結束，也能支援一位員工同時擔任多個部門主管。
整篇文章可以拆成六個重點來看。
第一個重點是 員工主檔 employees。這張表只放員工最基本、最穩定的資料，例如員工編號、姓名、在職狀態、到職日、離職日。員工編號用 UNIQUE KEY 限制，避免同一個員工編號重複。
第二個重點是 部門主檔 departments。這張表除了部門代碼、部門名稱之外，還有 parent_department_id。這個欄位是用來做部門階層，例如「技術部」底下可以有「系統課」、「網路課」。也就是說，部門表自己關聯自己，這叫做自我關聯（Self-referencing Relationship）。
第三個重點是 職務主檔 job_titles。文章沒有把「經理、課長、主任、工程師」直接寫死在員工表裡，而是獨立成一張職務資料表。這樣做的好處是職務可以被重複使用，也可以額外紀錄職務是否為主管職，例如 is_supervisor_role 用來判斷某個職務是否屬於主管角色。
第四個重點是 電話、電子郵件、地址都拆成獨立資料表。因為一位員工可能有多筆電話、多筆電子郵件、多筆地址，所以文章沒有在 employees 表裡設計 phone1、phone2、email1、email2 這種欄位，而是建立 employee_phones、employee_emails、employee_addresses 三張表。這就是正規化（Normalization）的做法，可以避免欄位重複、資料難以擴充的問題。
第五個重點是 任職歷程表 employee_department_job_history。這張表是整個設計最重要的地方，因為它同時連接員工、部門、職務三者。它裡面有 employee_id、department_id、job_title_id、start_date、end_date、is_primary_department 等欄位，所以可以表達「王大明從 2023 年開始是技術部經理，從 2024 年開始又兼任管理部資訊長」這種複雜情境。文章的範例資料也正是用這種方式示範一人多職、一人跨部門兼任的情況。
第六個重點是 最後的查詢報表。文章最後用 SELECT 搭配 LEFT JOIN、子查詢與 GROUP_CONCAT()，把員工姓名、員工編號、電子郵件、電話、地址、所屬部門與職稱整理成一份比較容易閱讀的清單。
這裡的 GROUP_CONCAT() 很重要，因為一位員工可能有多筆電子郵件、電話、地址，所以查詢時要把多筆資料合併成同一欄顯示。
這篇文章真正想教的不是「背 SQL 語法」，而是需求分析如何轉成資料表結構。
你可以把它想成這樣： employees 是「人」。 departments 是「組織單位」。 job_titles 是「職稱」。 employee_department_job_history 是「某人在某段時間，於某部門擔任某職務」。
employee_phones、employee_emails、employee_addresses 是「員工的多筆聯絡資訊」。 所以資料庫不是只回答「這個員工現在在哪個部門」，而是可以回答更細的問題，例如： 某員工目前在哪些部門任職？ 某員工曾經在哪些部門工作過？ 某部門目前有哪些人？ 誰是主管職？ 某員工是否兼任多個部門職務？ 某員工有哪些聯絡方式？
從資料庫設計角度來看，這篇文章有三個教學價值。
第一，它示範了 多值屬性（Multi-valued Attribute）要拆表。電話、電子郵件、地址這種資料，不適合直接塞在員工主檔裡，因為數量不固定。
第二，它示範了 多對多關係（Many-to-many Relationship）要透過中介表處理。員工可以屬於多個部門，部門也可以有多位員工，所以不能只靠一個 department_id 解決。
第三，它示範了 歷史資料要有時間欄位。如果只記錄目前狀態，就無法知道員工過去的異動歷程；所以 start_date 與 end_date 是這個系統能不能追蹤歷史的關鍵。
不過如果要更嚴謹一點，這篇文章還可以再補幾個進階討論。 例如，is_primary_department 雖然可以表示主要部門，但目前資料庫沒有強制限制「一位員工同一時間只能有一個主要部門」。如果要嚴格控制，可能要加上額外的唯一性約束或用程式邏輯檢查。
另外 is_supervisor_role 只能表示某個職稱是否為主管職，但它不一定能完整表達「某部門的實際主管是誰」。如果企業需求更嚴謹，例如要查「技術部現任部門主管」，可能還需要設計更明確的主管任命規則，或在任職表中加入更細的角色類型。
整體來說，這篇文章很適合作為 MySQL 資料表設計、ERD、正規化、JOIN 查詢、GROUP_CONCAT 報表輸出 的綜合練習。它的重點不是把員工資料做出來而已，而是讓學生理解：當需求開始出現「多筆」、「歷程」、「兼任」、「組織階層」時，資料表就不能再用單一大表硬塞，而要透過正規化與關聯表來設計。
0 留言