使用通常唯一標識行但有時為空的欄位對錶進行規範化
如果以前有人問過並回答過,請原諒我。
我正在為庫存管理系統粗略設計一個模式,以在 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... );
但是,我有兩個問題。
- 有時(可能是 3% 到 5% 的時間)item_number 實際上等於SKU。也就是說,我的一個供應商特別在他們的產品上附加了我懷疑不是全球唯一的 SKU,而是按照他們的項目編號製作的。
- 無論是否等於 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 問題的答案很有價值:
流行的做法
然而,我猜想,流行的(或常見的)程序將是擁有一個
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_number
and組成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) );
我強烈建議在大量數據負載的情況下進行大量測試,以確定哪些鍵更方便——從物理上講——始終考慮到整體數據庫特性(所有表的列數、類型和大小)列、約束和基礎索引等)。
類似的場景
您感興趣的業務環境與這些文章中處理的場景有一定的相似之處,因此您可能會發現一些討論的要點具有相關性。