Mysql

規範化/組合具有相似數據的多個表

  • October 27, 2018

我正在根據第三方供應商的數據建立一個車輛記錄數據庫。有 3 種型號：車輛、變速箱和適配器。

車輛和變速箱都是 1:n 適配器。從技術上講，變速箱只是帶有附加變速箱柱的車輛，而車輛除了車輛柱外還包含燃油系統柱。我無法確定將我的數據組合成一個標準化集合的最佳方式。

以下是我的一些數據範例：

傳輸表

傳輸表

車輛表 在此處輸入圖像描述

數據透視表

在此處輸入圖像描述

如您所見，變速箱和車輛在trans_type/ fuel_system columns（分別）及以後基本相同。例如，我突出顯示的車輛與使用 engine_code 顯示的第二個傳輸相同LML。

理想情況下，我希望最終只得到一張車輛數據表。例如，如果我要合併突出顯示的記錄，我應該只得到 2 條記錄2013 GMC Sierra 3500 HD：一條帶有 6.0L V8 發動機，另一條帶有 6.6L V8 發動機，以及相應的燃油系統和變速箱列每個。

數據透視表也將被組合，這意味著我需要以某種方式用組合數據中的新 ID 替換舊 ID。

以下是我對如何解決此問題的想法：

  1. 規範化相似的列（品牌、型號、發動機、變速箱）
  2. 只需將fuel_systemandtrans_type列拆分到它們自己的表中（但這對我合併沒有任何幫助，它只是讓事情更易於管理，直到我弄清楚如何合併）
  3. 製作一個包含兩個表中所有列的新表，並插入來自 Vehicles 和 Transmissions 的數據，填寫空白（即更新記錄匹配的fuel_system，反之亦然），然後開始清理重複項。

我為這篇冗長的文章道歉，但我還沒有在我的搜尋中找到任何真正概述這個過程可能是什麼樣子的東西。歡迎任何意見或建議，並提前感謝您。

功能依賴和規範化

為了進行涉及第二種和更多範式的規範化練習——根據E. F Codd 博士的數據關係模型——首先必須知道屬性之間的相關函式依賴關係（為簡潔起見，FD）是什麼（通常描繪為列）的一個適應的數學關係（通常描繪為一個表）。這種練習屬於數據庫的邏輯抽象層次。這就是為什麼，為了解決您聲明的獲得標準化集的意圖，我通過評論請求有關適用 FD 的資訊。

例如，一個涉及假設屬性Foo和Bar的 FD可以描述為Foo → Bar，這又可以讀作“屬性 Foo 確定屬性 Bar”或“屬性 Bar 由屬性 Foo 確定”。通過這種方式，人們可以區分 (a) 一個或多個屬性，或者一個或多個屬性組合，它們是關係的一個或多個鍵，以及 (b) 區分不是或不屬於關係的屬性。 , 一個或多個鍵。

關於您描述的場景，讓我們假設名為“傳輸”的網格是數學關係的具體表示。關於標記為submodel、body_code、engine_code、engine、desplacement、汽缸、trans_type、trans_code和qualifier的列的值，可以說：

  • 一些（engine_code和trans_code）似乎是由稱為關係的鍵的值決定的，比如說，Vehicle — 即*(model, make, year)*的組合。
  • 有些（transmission_type和qualifier）似乎是由稱為TransmissionAdaptor的關係的鍵的值決定的，即trans_code。
  • 一些（engine_type、displacement和cylinders）似乎是由稱為 Engine 的關係的鍵的值決定的，比如說，Engine - 即engine_code。

但這些都是簡單的假設，基於我對上述網格中包含的資訊的個人解釋，顯然我對所討論的業務環境一點也不熟悉。因此，為了擺脫那些不必要和有問題的假設，您必須採訪業務專家，他們將幫助您辨識 FD，進而指導您正確執行以數據庫管理專業所需的精度規範化和佈局數據庫結構。如果沒有業務專家可以求助，那麼您將不得不深入研究數據集，觀察數據的使用和含義，並仔細分析感興趣的資訊之間的相互聯繫，以自行確定重要的 FD。

標準化集合

擁有規範化的數據庫有助於避免在考慮的關係（表）的屬性（列）之間存在*不希望的依賴關係時最終出現的更新/修改異常（影響 INSERT、UPDATE 和 DELETE 操作）。*例如，如果關係的屬性 (i) 依賴於非鍵屬性，或者 (ii) 依賴於復合鍵的一部分，即多屬性鍵，則設計者必須分解關於兩個或多個的關係。

因此，大多數時候，一個規範化集合由各種關係組成，每個關係都意味著在其元組（行）中只包含一種特定類型的事實。相反，非規範化和非完全規範化的集合由一個或多個關係組成，這些關係在其元組中包含一種以上的事實。

從抽象的概念級別開始的數據庫設計

另一方面，您可以從不同的角度設計相關數據庫，首先從純粹概念的角度分析感興趣的事物類型的結構和關聯（或關係），而不考慮關係（表）、屬性（列）、約束和規範化。當然，這種方法還需要數據庫設計人員和業務專家之間的密切溝通，或者在沒有業務專家的情況下，數據庫設計人員需要深入了解業務環境的資訊需求和特徵。

範例業務規則

因此，我將整理一些假設的業務規則，這些規則將有助於創建一個說明性概念模式，僅基於對您問題中包含的資訊的假設。

車輛是：

  • 主要由Model , Make和Year的一種組合來辨識
  • 僅由一個引擎移動
  • 僅配備一個傳輸適配器
  • …

引擎：_

  • 主要由一個EngineCode 標識
  • 僅由一種EngineType編目
  • 正好有一個位移
  • 僅容納一種類型的氣缸
  • 僅提供一個FuelSystem
  • 安裝在零、一輛或多輛車輛上
  • …

傳輸適配器是：

  • 主要由一個TransmissionCode標識
  • 僅由一種傳輸類型分類
  • 安裝在零輛、一輛或多輛車輛上
  • …

燃油系統：

  • 主要由一個名稱標識
  • 僅由一種FuelSystemType分類
  • 正好有一個壓力
  • 安裝在零、一輛或多輛車輛上
  • …

正如所證明的，（a）可能的相關實體類型的**屬性之間的假設關聯類型，以及（b）可能的實體類型本身之間的假設關聯類型，已經以相對清晰的方式進行了說明。

顯然，這些規則只是一種媒介，用於闡述您可以遵循的設計數據庫的方法。作為個人的解釋，它們當然應該根據真實的商業環境特徵進行確認、駁斥或改編。

有時，數據庫設計人員會繪製一個描繪概念模式定義的圖表，以便提供一個圖形工具，幫助所有感興趣的（技術和非技術）方之間進行交流。

當這一階段的結果穩定到一定程度時，人們就可以開始更自信地思考用邏輯層次的構造來表示這些概念方面了。

a順便提一下，概念關係與邏輯**關係非常不同。

說明性 SQL-DDL 邏輯級設計

隨後，我創建了以下四個表格，代表上面闡述的概念級公式：

CREATE TABLE TransmissionAdaptor ( 
   TransmissionAdaptorCode CHAR(10) NOT NULL,
   TransmissionType        CHAR(30) NOT NULL,
   --
   CONSTRAINT TransmissionAdaptor_PK PRIMARY KEY (TransmissionAdaptorCode)
);

CREATE TABLE FuelSystem ( 
   Name           CHAR(6)  NOT NULL,
   FuelSystemType CHAR(10) NOT NULL,
   Pressure       CHAR(10) NOT NULL,
   FuelSystemName CHAR(30) NOT NULL,
   --
   CONSTRAINT FuelSystem_PK PRIMARY KEY (Name)
);

CREATE TABLE MyEngine ( 
   EngineCode     CHAR(3) NOT NULL,
   EngineType     CHAR(8) NOT NULL,
   Displacement   CHAR(3) NOT NULL,
   Cylinders      CHAR(3) NOT NULL,
   FuelSystemName CHAR(6) NOT NULL,
   --
   CONSTRAINT Engine_PK               PRIMARY KEY (EngineCode),
   CONSTRAINT Engine_to_FuelSystem_FK FOREIGN KEY (FuelSystemName)
       REFERENCES FuelSystem (Name)
);

CREATE TABLE Vehicle ( 
   Model                   CHAR(20) NOT NULL,
   Make                    CHAR(10) NOT NULL,
   MyYear                  SMALLINT NOT NULL,
   EngineCode              CHAR(3)  NOT NULL,
   TransmissionAdaptorCode CHAR(10) NOT NULL,
   --
   CONSTRAINT Vehicle_PK                 PRIMARY KEY (Model, Make, MyYear),
   CONSTRAINT Vehicle_to_Engine_FK       FOREIGN KEY (EngineCode)
       REFERENCES MyEngine (EngineCode),
   CONSTRAINT Vehicle_to_Transmission_FK FOREIGN KEY (TransmissionAdaptorCode)
       REFERENCES TransmissionAdaptor (TransmissionAdaptorCode)
);

由於現在要處理邏輯級元素，表中明確聲明了基於清晰概念模式的鍵，因此需要進行適當的範式評估練習，以測試設計的可靠性。

就個人而言，我發現上面描述的設計順序，即

  • (1)概念公式 ⟷ (2) 邏輯表示 ⟷ (3) 通過範式進行測試

比直接使用“孤立的”功能依賴更自然，因為它幫助設計人員從不同抽象級別以徹底的方式理解場景。

進一步的考慮

系統分配的代理

如您所見，我沒有將額外的Id列（通常添加以包含系統生成的代理鍵值）附加到任何這些基表中，因為這樣做只會阻礙（概念）建模和（邏輯）規範化任務。一旦有了具有相應約束的穩定結構，就適合評估添加該非數據工件有益的特定情況。

列數據類型

沒有詳細討論的一個重要因素是與每列相關的特定*域。*因此，當您繼續設計數據庫時，您必須確定最合適的數據類型和每列的大小。

數據推導

具有上述邏輯佈局，您必須使用派生表（例如，通過 SELECT 操作聲明的表，這些表從一個或多個基表或 - 其他 -派生表中收集列）來獲取所示資訊，例如，在您的傳輸網格中。

當然，可以將派生表定義為可以進一步查詢的視圖，以便例如方便編寫未來數據操作操作的程式碼。

研究這一點可能很有用，看看您是否可以實現您的既定目標，理想情況下，最終只需要一個車輛數據表。

物理層面的方面

抽象的物理級別是另一個需要注意的點，因為最方便的索引的配置與數據庫的最佳功能直接相關（例如，讀寫速度、可伸縮性）。不用說，在這方面您必須考慮數據操作操作（或查詢）的趨勢。通常，您修復支持所涉及的列的索引，例如，在 WHERE 和 JOIN 子句中。

MySQL 作為數據庫管理系統的眾多缺點和缺點之一是它不提供對“索引”或“物化”視圖的內置支持，所以如果你想建構數據庫，這個因素絕對值得考慮有效地工作。

其他重要的物理級方面是，例如，以最佳方式設置所涉及的硬體（硬碟驅動器、記憶體、處理器等）、數據庫管理系統、作業系統、網路頻寬等。

附錄

在最近的評論互動中出現了在設計數據庫時充分了解相關業務領域的重要性的證據，其中@Rick James在您的場景中提出了一些實體類型、它們相應的關聯類型和基數比率的相關考慮：

看起來傳輸：適配器是多：多，而不是 1：多。請澄清。

……您的回復如下：

是的，這是正確的。變速箱有自己的適配器，FuelSystems 有自己的適配器。一個傳輸屬於零個、一個或多個傳輸適配器，並且一個傳輸適配器可以屬於零個、一個或多個傳輸。

因此，如您所知，有必要對這些概念實體類型進行建模，即：

  • 變速箱，變速箱適配器，燃料系統和燃料系統適配器，

以及連接關聯類型，即：

  • Transmission-TransmissionAdaptor和FuelSystem-FuelSystemAdaptor ,

以便它們以所需的精度反映您的業務領域特徵。

一旦所有相關的方面都用相應的表（為每個實體/關聯類型放置一個）、列、數據類型和約束在邏輯級 DDL 設計中表示出來，您可能希望評估所涉及的功能依賴關係以提供強大的、規範化, 系統.

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

相關問答

Mysql

庫存系統中訂單表非規範化組的數據庫模式重新設計策略

  • July 12, 2019
檢視問答
Database-Design

如何對有時（但不總是）有小節的數據進行建模？

  • March 15, 2022
檢視問答
Mysql

如何在 MySQL 中設計 Person 表？

  • March 8, 2022
檢視問答
Mysql

是否應該將頻繁更新的計數器列儲存在單獨的表中？

  • March 3, 2022
檢視問答
Mysql

儲存各種類型的參數

  • January 22, 2022
檢視問答
Mysql

對沒有父母的孩子進行分組的資料結構

  • December 9, 2021
檢視問答