如果以前有人問過並回答過，請原諒我。

我正在為庫存管理系統粗略設計一個模式，以在 PostgreSQL 中實現。我們所有的產品和服務都有一個 sku。我們的大多數產品都來自製造商或經銷商，並帶有單獨的“項目編號”（無論是經銷商的目錄號、製造商的型號等）。但是，並非所有人都有這樣的數字。我們有內部製造的小型組件，通常沒有項目編號。我們的服務沒有項目編號。由於這些原因，以下 CREATE TABLE 對我來說很有意義。

情景一：

CREATE TABLE product (
  sku            text PRIMARY KEY,
  name           text UNIQUE NOT NULL, -- alternate key
  price          numeric NOT NULL CHECK (price > 0),
  quantity       numeric NOT NULL CHECK (quantity > 0),
  item_number   text -- hmmm...
);

但是，我有兩個問題。

1. 有時（可能是 3% 到 5% 的時間）item_number 實際上等於SKU。也就是說，我的一個供應商特別在他們的產品上附加了我懷疑不是全球唯一的 SKU，而是按照他們的項目編號製作的。
2. 無論是否等於 SKU，item_number（如果存在）幾乎在每種情況下都足以唯一標識我的小商店域中的產品。

我擔心將其標準化為 3NF。如果 item_number 有時為 null，則顯然不能將其聲明為備用鍵。但是，從語義上講，它是一個唯一的標識符，它存在於我能想到的每一種情況下。那麼我的上表（每當 item_number 存在時，每個屬性在功能上都依賴於非主要屬性 item_number ）是否標準化？我想不，但我當然不是專家。我想過做以下事情：

方案 B

CREATE TABLE product (
  sku            text PRIMARY KEY REFERENCES product_item_number (sku),
  name           text UNIQUE NOT NULL, -- alternate key
  price          numeric NOT NULL CHECK (price > 0),
  quantity       numeric NOT NULL CHECK (quantity > 0),
);

CREATE TABLE product_item_number (
  sku            text PRIMARY KEY,
  item_number    text
);

因為我確實不需要保留功能依賴項 item_number -> price、item_number -> quantity 等，所以場景 B 對我來說似乎有點合理。我不會有一個非主要屬性來確定任何其他非主要屬性。

我的最終想法是在 item_number 不存在的所有情況下都簡單地使用 sku 作為項目編號，但我想知道這是否是一個好習慣。

方案 C

CREATE TABLE product (
  sku            text PRIMARY KEY,
  name           text UNIQUE NOT NULL, -- alternate key
  price          numeric NOT NULL CHECK (price > 0),
  quantity       numeric NOT NULL CHECK (quantity > 0),
  item_number    text UNIQUE NOT NULL -- alternate key???
);

我對方案 C 的擔憂是，可能存在供應商回收具有不同 sku 的目錄號的情況（也許？），或者兩個製造商都製造“d57-red”或類似的情況。在這種情況下，我認為我必須以程式方式在有問題的 item_numbers 前面加上製造商名稱或類似名稱。

當然，也許我想太多了。

謝謝閱讀。根據 MDCCL 的評論，一些澄清：

• 一個 sku 在我的域中始終是唯一的（少量非全球唯一的供應商提供的 SKU 不太可能發生衝突）。
• item_number 將是一個面向公眾的屬性，客戶和有時我自己都使用它來辨識產品。例如，假設客戶跳過我的網站並打電話給我詢問我是否有 xyz-white；item_number 有助於消除歧義。根據我的經驗，項目編號是獨一無二的（也就是說，我的庫存中沒有反例），但這本身並不是一個規則。有一天我可能會發生 item_number 名稱空間衝突。也許，如果發生這種情況，我會將製造商名稱的前三個字母添加到 item_number 的前面。
• item_numbers 並不總是存在。我想我可以為那些沒有的人提供某種“替代 item_number”，但是任意 item_number 會適得其反。如上所述，如果 item_number 存在，它應該存在以幫助我和我的客戶消除產品之間的歧義。如果 item_number 是我自己編造的，他們可能會認為他們正在查看錯誤的產品。我不知道。

前提是Sku和ItemNumber將始終暗示唯一值

我認為您已經通過發現從概念上講ItemNumber是一個可選屬性找到了答案。即，當您確定它不適用於**Product實體類型的每一個事件（由邏輯級行表示）時。因此，正如您正確指出的那樣，該item_number列不應在表中聲明為 ALTERNATE KEY（為簡潔起見，AK） 。product

在這方面，您的場景 B 是非常合理的，正如以下概念級別的表述所示：

• 產品可能有也可能沒有商品編號。

換句話說，在Product和ItemNumber之間存在**一對零或一(1:0/1) 的基數比。

然後，是的，您應該引入一個新表來處理可選列，我同意這product_item_number是一個非常具有描述性的名稱。該表應該被sku約束為它的主鍵（PK），以確保不超過一行具有相同sku值的插入，就像你做的那樣。

同樣重要的是要提到，它也product_item_number.sku應該是一個受約束的外鍵（FK），它引用product.sku.

這是一個範例 SQL-DDL 邏輯級設計，說明了前面的建議：

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define
-- the most convenient INDEXing strategies.

CREATE TABLE product ( 
   sku      TEXT    NOT NULL, 
   name     TEXT    NOT NULL, 
   price    NUMERIC NOT NULL, 
   quantity NUMERIC NOT NULL,
   --
   CONSTRAINT product_PK        PRIMARY KEY (sku), 
   CONSTRAINT product_AK        UNIQUE      (name), -- AK.
   CONSTRAINT valid_price_CK    CHECK       (price > 0),
   CONSTRAINT valid_quantity_CK CHECK       (quantity > 0)
); 

CREATE TABLE product_item_number ( 
   sku         TEXT NOT NULL, -- To be constrained as PK and FK to ensure the 1:0/1 correspondence ratio between the relevant rows.
   item_number TEXT NOT NULL, 
   --
   CONSTRAINT product_item_number_PK            PRIMARY KEY (sku),
   CONSTRAINT product_item_number_AK            UNIQUE      (item_number), -- In this context, ‘item_number’ is an AK. 
   CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (sku) 
       REFERENCES product (sku)  
);

*在這個 db<>fiddle*的 PostgreSQL 11 上測試。

此外，還有另一個概念公式可以指導塑造上述數據庫設計：

• 如果存在，則產品的ItemNumber必須是唯一的。

因此，item_number實際上應該將列聲明為 AK 的位置就在表中，因為只有在提供相關值時product_item_number，所述列才需要唯一性保護，因此必須相應地配置 UNIQUE 和 NOT NULL 約束。

缺失的價值觀和“封閉世界的解釋”

前面描述的邏輯 SQL-DDL 安排是處理缺失值的關係方法的一個範例，儘管它不是最流行的（或通常的）。這種方法與“封閉世界解釋”或“假設”有關。採用這一立場，(a) 記錄在數據庫中的資訊始終被視為真實，並且 (b) 未記錄在數據庫中的資訊始終被視為虛假。通過這種方式，一個人只保留已知的事實。

在目前的業務場景中，當使用者提供包含在product表中的所有數據點時，您必須插入相應的行，並且當且僅當使用者使item_number數據可用時，您還必須插入product_item_number對應的行。如果該item_number值未知或根本不適用，則不要插入product_item_number一行，就是這樣。

使用這種方法，您可以避免在基表中保留 NULL 標記/標記——以及我將在下一節中詳述的邏輯級後果——但您應該知道，這是數據庫管理範圍內的一個“有爭議的”主題。在這一點上，您可能會發現 Stack Overflow 問題的答案很有價值：

• “我怎樣才能避免我的數據庫中出現 NULL，同時又表示失去的數據？”

流行的做法

然而，我猜想，流行的（或常見的）程序將是擁有一個product包含item_number列的單個表，而該列又將設置為 NULLable，同時使用 UNIQUE 約束進行定義。在我看來，這種方法會使您的數據庫和適用的數據操作操作不那麼優雅（如圖所示，例如，在這個出色的 Stack Overflow 答案中），但這是一種可能性。

請參閱範例此操作過程的連續 DDL 語句：

CREATE TABLE product ( 
   sku         TEXT    NOT NULL, 
   name        TEXT    NOT NULL, 
   price       NUMERIC NOT NULL, 
   quantity    NUMERIC NOT NULL, 
   item_number TEXT    NULL, -- Accepting NULL marks. 
   --
   CONSTRAINT product_PK        PRIMARY KEY (sku), 
   CONSTRAINT product_AK1       UNIQUE      (name), -- AK.
   CONSTRAINT product_AK2       UNIQUE      (item_number), -- Being ‘NULLable’, this is not an AK. 
   CONSTRAINT valid_price_CK    CHECK       (price > 0),
   CONSTRAINT valid_quantity_CK CHECK       (quantity > 0)
);

*在這個 db<>fiddle*的 PostgreSQL 11 上測試。

因此，已建立item_number一個可以包含 NULL 的列，從邏輯上講，它是一個 AK 是不正確的。此外，您將儲存模棱兩可的 NULL 標記——它們不是值，無論 PostgreSQL 文件是否以這種方式標記它們——因此可以說該表不能正確表示經過調整的數學關係，規範化規則不能應用於它。

由於 NULL 表示列值是 (1) unknown或 (2) inapplicable，因此不能正確地說明所述標記屬於item_number值的有效域。如你所知，這種標記說明了一個真實值的“狀態”，但它本身並不是一個值，它自然不會表現得如此——順便提一下，值得一提的是NULL 在不同的 SQL 數據庫管理系統中表現不同，甚至在同一數據庫管理系統的不同版本之間也有不同的行為。

那麼，如果 (i) 某個列的值域和 (ii)由於包含 NULL ，該列所承載的含義並不完全清楚：

• 如何評估和定義相關的功能依賴？
• 如何辨識並聲明為 PRIMARY 或 ALTERNATE KEY（如 的情況item_number）？

儘管理論和實踐（例如關於數據操作）都涉及在數據庫中保留 NULL 標記，但這是處理缺失數據的方法，您會在絕大多數基於 SQL 平台的數據庫中找到，因為它允許將可選值的列附加到重要的基表中，並且作為效果，避免創建 (a) 補充表和 (b) 相關任務。

決定

我已經介紹了這兩種選擇，以便您自己確定哪一種更適合實現您的目標。

---

假設Sku和ItemNumber值最終可以重複

你的問題中有一些以一種特殊的方式引起了我的注意，所以我列出了它們：

• 有時（可能是 3% 到 5% 的時間）item_number 實際上等於 SKU。也就是說，我的一個供應商特別在他們的產品上附加了我懷疑不是全球唯一的 SKU，而是按照他們的項目編號製作的。
• $$ … $$在某些情況下，供應商回收具有不同 sku 的目錄號（也許？），或者兩個製造商都製造“d57-red”或類似的情況。在這種情況下，我認為我必須以程式方式在有問題的 item_numbers 前面加上製造商名稱或類似名稱。
• 一個 sku 在我的域中始終是唯一的（少量非全球唯一的供應商提供的 SKU 不太可能發生衝突）。

這些觀點可能會產生顯著影響，因為它們似乎表明：

• ItemNumber值最終可能會重複，當這種情況發生時，您可能會評估將具有不同含義的兩條不同資訊組合在同一列中*。*
• 畢竟，Sku值很可能會重複（即使是少量重複的Sku實例）。

在這方面，值得注意的是數據建模練習的兩個最重要的目標是（1）確定每個單獨的重要數據和（2）防止在同一列中保留多個數據。例如，這些因素有助於描述穩定且通用的數據庫結構，並有助於避免重複資訊——這有助於通過相應的約束保持數據值與業務規則一致——。

處理Sku重複的替代方法：在場景中引入manufacturer表格

因此，如果可以在不同的Manufacturers之間共享相同的Sku值，您可以在表中使用複合PK 約束，它將由 (i) 製造商 PK 列和 (ii) 組成。例如：product``sku

CREATE TABLE manufacturer (
   manufacturer_number INTEGER  NOT NULL, -- This could be something more meaningful, e.g., ‘manufacturer_code’.
   name                TEXT NOT NULL,
   --
   CONSTRAINT manufacturer_PK PRIMARY KEY (manufacturer_number), 
   CONSTRAINT manufacturer_AK UNIQUE      (name) -- AK.
);

CREATE TABLE product (
   manufacturer_number INTEGER NOT NULL, 
   sku                 TEXT    NOT NULL,
   name                TEXT    NOT NULL, 
   price               NUMERIC NOT NULL,
   quantity            NUMERIC NOT NULL,
   --
   CONSTRAINT product_PK                 PRIMARY KEY (manufacturer_number, sku), -- Composite PK.
   CONSTRAINT product_AK                 UNIQUE      (name), -- AK.
   CONSTRAINT product_TO_manufacturer_FK FOREIGN KEY (manufacturer_number)
       REFERENCES manufacturer (manufacturer_number),
   CONSTRAINT valid_price_CK             CHECK       (price > 0),
   CONSTRAINT valid_quantity_CK          CHECK       (quantity > 0)
);

並且，如果ItemNumber**在適用時要求保持唯一性，則該product_item_number表可以構造如下：

CREATE TABLE product_item_number (
   manufacturer_number INTEGER NOT NULL,  
   sku                 TEXT    NOT NULL,
   item_number         TEXT    NOT NULL,
   --
   CONSTRAINT product_item_number_PK            PRIMARY KEY (manufacturer_number, sku), -- Composite PK.
   CONSTRAINT product_item_number_AK            UNIQUE      (item_number), -- AK.  
   CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (manufacturer_number, sku)
       REFERENCES product (manufacturer_number, sku)  
);

*在這個 db<>fiddle*的 PostgreSQL 11 上測試。

如果ItemNumber不需要防止重複，您只需刪除為此類列聲明的 UNIQUE 約束，如下面的 DDL 語句所示：

CREATE TABLE product_item_number (
   manufacturer_number INTEGER NOT NULL,  
   sku                 TEXT    NOT NULL,
   item_number         TEXT    NOT NULL, -- In this case, ‘item_number’ does not require a UNIQUE constraint.
   --
   CONSTRAINT product_item_number_PK            PRIMARY KEY (manufacturer_number, sku), -- Composite PK.
   CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (manufacturer_number, sku)
       REFERENCES product (manufacturer_number, sku)  
);

另一方面，假設ItemNumber實際上確實需要避免專門針對關聯的Manufacturer的重複值，您可以設置一個複合 UNIQUE 約束，該約束由manufacturer_numberand組成item_number，如下面的程式碼行所示：

CREATE TABLE product_item_number (
   manufacturer_number INTEGER NOT NULL,  
   sku                 TEXT    NOT NULL,
   item_number         TEXT    NOT NULL,
   --
   CONSTRAINT product_item_number_PK            PRIMARY KEY (manufacturer_number, sku),         -- Composite PK.
   CONSTRAINT product_item_number_AK            UNIQUE      (manufacturer_number, item_number), -- Composite AK.
   CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (manufacturer_number, sku)          -- Composite FK.
       REFERENCES product (manufacturer_number, sku)  
);

當*Sku值始終唯一但可以在不同製造商之間共享特定ItemNumber*值時**

如果您可以保證Product.Sku永遠不會暗示重複*，但ItemNumber可能被不同的Manufacturers**使用*，您可以將數據庫配置為此處公開：

CREATE TABLE manufacturer (
   manufacturer_number INTEGER NOT NULL, 
   name                TEXT    NOT NULL,
   --
   CONSTRAINT manufacturer_PK PRIMARY KEY (manufacturer_number), 
   CONSTRAINT manufacturer_AK UNIQUE      (name) -- AK.
);

CREATE TABLE product ( 
   sku      TEXT    NOT NULL, 
   name     TEXT    NOT NULL, 
   price    NUMERIC NOT NULL, 
   quantity NUMERIC NOT NULL,
   --
   CONSTRAINT product_PK        PRIMARY KEY (sku), 
   CONSTRAINT product_AK        UNIQUE      (name), -- AK. 
   CONSTRAINT valid_price_CK    CHECK       (price > 0),
   CONSTRAINT valid_quantity_CK CHECK       (quantity > 0)
); 

CREATE TABLE product_item_number ( 
   sku                 TEXT    NOT NULL,
   manufacturer_number INTEGER NOT NULL,
   item_number         TEXT    NOT NULL,
   --
   CONSTRAINT product_item_number_PK                 PRIMARY KEY (sku, manufacturer_number),  
   CONSTRAINT product_item_number_AK                 UNIQUE      (manufacturer_number, item_number), -- In this context, ‘manufacturer_number’ and ‘item_number’ compose an AK. 
   CONSTRAINT product_item_number_TO_product_FK      FOREIGN KEY (sku)
       REFERENCES product (sku),  
   CONSTRAINT product_item_number_TO_manufacturer_FK FOREIGN KEY (manufacturer_number) 
       REFERENCES manufacturer (manufacturer_number)  
);

在這個 db<>fiddle的 PostgreSQL 11 上測試。

---

物理層面的考慮

我們沒有討論product.sku列的確切類型和大小，但是，如果它在字節方面是“大”的，那麼它最終可能會破壞系統的數據檢索速度——由於抽象的物理級別的各個方面，相關的例如，索引的大小和磁碟空間使用情況—。

以這種方式，您可能希望評估 INTEGER 列的合併，它可以提供比可能的“重”TEXT 更快的響應——但這一切都取決於比較列的精確特徵。正如預期的那樣，它很可能是product_number代表記錄集的序列中的一個數值products。

包含這一新元素的說明性安排如下：

CREATE TABLE product ( 
   product_number INTEGER NOT NULL,
   sku            TEXT    NOT NULL, 
   name           TEXT    NOT NULL, 
   price          NUMERIC NOT NULL, 
   quantity       NUMERIC NOT NULL,
   --
   CONSTRAINT product_PK        PRIMARY KEY (sku), 
   CONSTRAINT product_AK        UNIQUE      (name), -- AK. 
   CONSTRAINT valid_price_CK    CHECK       (price > 0),
   CONSTRAINT valid_quantity_CK CHECK       (quantity > 0)
); 

CREATE TABLE product_item_number 
( 
   product_number INTEGER NOT NULL,
   item_number    TEXT    NOT NULL,
   --
   CONSTRAINT product_item_number_PK            PRIMARY KEY (product_number),  
   CONSTRAINT product_item_number_AK            UNIQUE      (item_number), -- AK.
   CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (product_number)
      REFERENCES product (product_number)   
);

我強烈建議在大量數據負載的情況下進行大量測試，以確定哪些鍵更方便——從物理上講——始終考慮到整體數據庫特性（所有表的列數、類型和大小）列、約束和基礎索引等）。

---

類似的場景

您感興趣的業務環境與這些文章中處理的場景有一定的相似之處，因此您可能會發現一些討論的要點具有相關性。

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