方案審查/規範化級別/查詢優化

所以我正在設計一個相對複雜的數據庫——基本上是完整的內部會計設置。在一個部分中，我擔心我過度規範化；“目錄” - 我將以員工部分為例，但對於客戶、供應商等來說，基本內容相同。

基本上directory儲存與所有類型相關的資訊，無論是客戶、員工還是 x，員工可以有任意數量的地址、電子郵件地址等。

使用 MySQL 5.6.12，PHP/PDO（不是我從中訪問它的真正問題的問題）

相關的表創建呼叫：

CREATE TABLE IF NOT EXISTS `directory` (
 `directory_id` int(11) NOT NULL AUTO_INCREMENT,
 `name_display` varchar(255) NOT NULL,
 `type` tinyint(2) unsigned NOT NULL COMMENT '0: human, 1: unincorporated, 2: incorporated',
 `visibility` varchar(255) NOT NULL,
 `notes` text NOT NULL,
 PRIMARY KEY (`directory_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `directory_addresses` (
 `directory_id` int(11) NOT NULL,
 `address_id` int(11) NOT NULL AUTO_INCREMENT,
 `type` int(11) NOT NULL,
 `country` int(11) NOT NULL,
 `region` int(11) NOT NULL,
 `city` varchar(255) NOT NULL,
 `postalcode` varchar(10) NOT NULL,
 `line1` varchar(255) NOT NULL,
 `line2` varchar(255) NOT NULL,
 `version` int(11) NOT NULL,
 `primary` tinyint(1) NOT NULL,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`address_id`),
 KEY `fk_type` (`type`),
 KEY `fk_country` (`country`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `directory_email` (
 `directory_id` int(11) NOT NULL,
 `email_id` int(11) NOT NULL AUTO_INCREMENT,
 `primary` tinyint(1) NOT NULL,
 `email` varchar(255) NOT NULL,
 PRIMARY KEY (`email_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `directory_employee` (
 `directory_id` int(11) NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `employee_id` varchar(10) NOT NULL,
 `start_date` date NOT NULL,
 `end_date` date DEFAULT NULL,
 `department` int(11) NOT NULL,
 `notes` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `directory_employee_payrate` (
 `employee_id` int(11) NOT NULL,
 `payrate_id` int(11) NOT NULL AUTO_INCREMENT,
 `start_date` date NOT NULL,
 `end_date` date DEFAULT NULL,
 `rate` decimal(10,2) NOT NULL,
 PRIMARY KEY (`payrate_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin

對於員工列表概覽頁面，會導致以下查詢：

Select
   directory_employee.directory_id,
   directory_employee.employee_id,
   directory_employee.department,
   directory_employee.start_date,
   directory_employee.end_date,
   directory_employee.notes,
   directory_employee_payrate.rate,
   directory.name_display,
   directory_email.email,
   directory_phone.phone,
   directory_addresses.line1,
   directory_addresses.line2,
   directory_addresses.city,
   directory_addresses.region,
   directory_addresses.postalcode,
   directory_addresses.country
FROM directory_employee
LEFT JOIN directory_employee_payrate ON
   directory_employee_payrate.employee_id = directory_employee.id
   AND directory_employee_payrate.end_date IS NULL
LEFT JOIN directory ON
   directory.directory_id = directory_employee.directory_id
LEFT JOIN directory_addresses ON
   directory_addresses.directory_id = directory_employee.directory_id
   AND directory_addresses.primary = 1
LEFT JOIN directory_email ON
   directory_email.directory_id = directory_employee.directory_id
   AND directory_email.primary = 1
LEFT JOIN directory_phone ON
   directory_phone.directory_id = directory_employee.directory_id
   AND directory_phone.primary = 1

誠然，這有點長！

所以我的問題基本上可以歸結為：

• 這看起來像一個合理的模式嗎？
• 是否過度標準化？（如果是這樣，您會建議做什麼 - 序列化數組？）
• 如果這是一個合理的模式，是否有任何優化適用於該Select語句？
• 除了這個問題的要點之外，您還推薦什麼架構改進？（我確實有 FK - 我只是沒有將它們全部設置/複製定義語句。）

在大多數情況下，對於會計類型系統的內部來說，這是一個很好的模式設計。規範化在這種情況下為您提供了很大的靈活性，並且您所做的似乎是合適的。

也就是說，我確實質疑目錄表的選擇。在某些情況下，這樣的事情可能是合適的，但在這些情況下，通常存在某種公共執行緒 - 很多公共欄位，或者經常需要聚合所有類型的數據（即，多種類型的例如，所有通過公共系統下訂單的客戶）。如果我只看你在這裡展示的一小段摘錄，我會說它被過度規範化了。但是，您還提到您對客戶、供應商等有類似的結構，並且鑑於它是一個會計系統，我猜您會將所有這些連結到某種總分類帳模式。如果該分析正確，您的目錄表將成為系統中所有各種類型的收款人之間的公共連結，這是必要的，

（我為漫無邊際的答案道歉，但規範化是一門藝術。我希望在答案中傳達我的思維過程。）

您還詢問了其他優化。在任何 RDBMS 中幫助關係引擎的最重要的事情就是包含好的索引。看起來你有一個好的開始，也許更多 - 你確實有主鍵，我假設它伴隨著索引。您還提到了外鍵，它至少可以幫助某些引擎優化查詢。我的專長不是 MySQL，所以我不能直接談論優化其引擎的技術要點，但總的來說，你可能會沒事的。只需注意查詢的性能，檢查執行計劃/解釋計劃，並根據需要添加其他索引。

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