我是否必須（有時）犧牲規範化以實現數據完整性？

我經常遇到以下挑戰，並最終對我的表設計進行反規範化以強制執行數據完整性。我很想知道是否有另一種對數據進行建模的方法，既可以標準化又可以保證完整性。

這是一個典型的（簡化的）範例：

create table [ProductType]
(
   [ProductTypeId] INT identity(1,1) not null,
   [ProductTypeName] nvarchar(100) not null, 
   constraint [PK_ProductType] primary key ([ProductTypeId])
)

create table [Product]
(
   [ProductId] INT identity(1,1) not null,
   [ProductTypeId] int not null,
   [ProductName] nvarchar(100) not null,
   constraint [PK_Product] primary key ([ProductId]), 
   constraint [AK_Product] unique ([ProductId], [ProductTypeId]),
   constraint [FK_Product_ProductType] foreign key ([ProductTypeId]) references [ProductType]([ProductTypeId])
)

create table [ProductTypeProperty]
(
   [PropertyId] INT identity(1,1) not null,
   [ProductTypeId] int not null,
   [PropertyName] nvarchar(100) not null,
   constraint [PK_ProductTypeProperty] primary key ([PropertyId]), 
   constraint [AK_ProductTypeProperty] unique ([PropertyId], [ProductTypeId]),
   constraint [FK_ProductTypeProperty_ProductType] foreign key ([ProductTypeId]) references [ProductType]([ProductTypeId])
)

create table [ProductPropertyValue]
(
   [ProductId] INT not null,
   [PropertyId] INT not null,
   [ProductTypeId] int not null,
   [PropertyValue] nvarchar(100) not null,
   constraint [PK_ProductPropertyValue] primary key ([ProductId], [PropertyId]),
   constraint [FK_ProductPropertyValue_ProductTypeProperty] foreign key ([PropertyId], [ProductTypeId]) references [ProductTypeProperty]([PropertyId], [ProductTypeId]), 
   constraint [FK_ProductPropertyValue_Product] foreign key ([ProductId], [ProductTypeId]) references [Product]([ProductId], [ProductTypeId])
)

SET IDENTITY_INSERT [dbo].[ProductType] ON
INSERT [dbo].[ProductType] ([ProductTypeId], [ProductTypeName]) VALUES (1, N'Clothing')
INSERT [dbo].[ProductType] ([ProductTypeId], [ProductTypeName]) VALUES (2, N'Drink')
SET IDENTITY_INSERT [dbo].[ProductType] OFF

SET IDENTITY_INSERT [dbo].[ProductTypeProperty] ON
INSERT [dbo].[ProductTypeProperty] ([PropertyId], [ProductTypeId], [PropertyName]) VALUES (1, 1, N'Colour')
INSERT [dbo].[ProductTypeProperty] ([PropertyId], [ProductTypeId], [PropertyName]) VALUES (2, 1, N'Size')
INSERT [dbo].[ProductTypeProperty] ([PropertyId], [ProductTypeId], [PropertyName]) VALUES (3, 2, N'Volume')
SET IDENTITY_INSERT [dbo].[ProductTypeProperty] OFF

SET IDENTITY_INSERT [dbo].[Product] ON
INSERT [dbo].[Product] ([ProductId], [ProductTypeId], [ProductName]) VALUES (1, 1, N'T-shirt')
INSERT [dbo].[Product] ([ProductId], [ProductTypeId], [ProductName]) VALUES (2, 2, N'Milk')
SET IDENTITY_INSERT [dbo].[Product] OFF

INSERT [dbo].[ProductPropertyValue] ([ProductId], [PropertyId], [ProductTypeId], [PropertyValue]) VALUES (1, 1, 1, N'Red')
INSERT [dbo].[ProductPropertyValue] ([ProductId], [PropertyId], [ProductTypeId], [PropertyValue]) VALUES (1, 2, 1, N'XL')
INSERT [dbo].[ProductPropertyValue] ([ProductId], [PropertyId], [ProductTypeId], [PropertyValue]) VALUES (2, 3, 2, N'1 pint')

-- NOTE THAT THE FKS ON [PRODUCTPROPERTYVALUE] MEAN YOU CANNOT RUN EITHER OF THESE
-- WHICH TRY TO ASSIGN A PROPERTY TO A PRODUCT THAT DOESN'T BELONG TO ITS TYPE
INSERT [dbo].[ProductPropertyValue] ([ProductId], [PropertyId], [ProductTypeId], [PropertyValue]) VALUES (1, 3, 1, N'Red')
INSERT [dbo].[ProductPropertyValue] ([ProductId], [PropertyId], [ProductTypeId], [PropertyValue]) VALUES (2, 2, 1, N'XL')

這是我要建模的內容：

1. 產品具有產品類型（例如“服裝”、“飲料”等）
2. 一個產品類型有許多屬性（例如“衣服”有“顏色”和“尺寸”）
3. 產品具有屬於其類型的任何/所有屬性的屬性值
4. 產品不能具有不屬於其類型的屬性的屬性值

第 4 點是引發挑戰的點。為了創建外鍵來實現這一點（

$$ FK_ProductProperty_ProductTypeProperty $$和$$ FK_ProductPropertyValue_Product $$）我已經去規範化並添加了“不必要的”獨特約束：

1. 添加$$ ProductTypeId $$到$$ ProductPropertyValue $$
2. 添加了一個唯一的約束$$ PropertyId $$,$$ ProductTypeId $$到$$ ProductPropertyValue $$
3. 添加了一個唯一的約束$$ ProductId $$,$$ ProductTypeId $$到$$ Product $$

然而，如果沒有這些，似乎不可能實現上述所有 4 點……是嗎？

當我的設計偏離簡單的分支“雪花”並且關係中需要存在“循環”時，似乎出現了這個挑戰：

P ---------> PT
^            ^
|            |
|            |
PPV ------> PTP

注意：這是一個有 4 個表的簡單範例。對於具有多級相關表的實際設計，問題變得更加嚴重（多列上的唯一約束，每個表有多個“不必要的”唯一約束等）。

我經常遇到以下挑戰，並最終對我的表設計進行反規範化以強制執行數據完整性。

這聽起來很矛盾。規範化通常會強制執行完整性。在我看來，你有一個誤解。你沒有去規範化你的設計。它被標準化就好了。

我很想知道是否有另一種對數據進行建模的方法，既可以標準化又可以保證完整性。

具體問題 - 我稱之為菱形 - 經常出現，儘管許多人沒有意識到或認為這是一個問題，並且沒有強制執行您在設計中強制執行的約束。

而且我認為沒有其他方法，忽略一些細節。“祖父”表（PropertyType在本例中）的唯一鍵必須是“父”表（此處為 ）的唯一約束的一部分，Product並且必須像您所做的那樣Property出現在“子”表（ ）中。ProductPropertyValue

第 4 點是引發挑戰的點。為了創建外鍵來實現這一點（

$$ FK_ProductProperty_ProductTypeProperty $$和$$ FK_ProductPropertyValue_Product $$）我已經去規範化並添加了“不必要的”獨特約束：

我不同意“不必要的”。需要它們，首先FOREIGN KEY要定義約束並使其起作用。

您的唯一約束很好，並且出於您所描述的完整性原因而需要：

確保 aProductPropertyValue與 a 相關Product並且 aProperty兩者（Product和Property）都屬於同一個PropertyType。

Product設計中的冗餘是對和的標識列的主鍵約束Property。它們可以被刪除而不會失去任何完整性。我知道自動遞增的（identity在 SQL Server 中）列也有UNIQUEorPRIMARY KEY約束是很常見的。但它並不真正需要它。不過，出於性能原因，保持該約束可能是好的。您也可以將該列用作其他表中的外鍵，其中與屬性類型沒有聯繫（因此PropertyTypeId沒有必要）。但從邏輯設計和規範化的角度來看，不存在規範化問題。這只是一個實現，物理設計細節。

我使用片語“反規範化我的表設計”來表示“將列添加到其他相關表中已經存在的表中”（即，給定行的值可以從相關行中得出）。規範化的一個方面不是刪除冗餘列嗎？

是的。但是你必須從實體、屬性和功能依賴開始。考慮到 DBMS 提供了唯一性的額外功能，當您添加（在此之前）代理鍵時，您很想在任何地方將它們用作外鍵列。因此，您可能會錯過使用其他一些獨特的列或組合。比如，(ProductTypeId, ProductName)可能也能辨識產品，所以它可能有一個唯一的約束，我不知道你的業務規則的細節。如果是，則可以將其用作ProductPropertyValue. 同樣對於Property.

這是否更有效是另一回事，因為像這樣的長列nvarachar(100)不是最適合索引的。因此，即使您有這種獨特的約束，出於效率原因，您最終也可能會使用您最初選擇的內容。（考慮 4+4+4=12 vs 4+200+200=404，如果我們有兩個 nvarchar 來自Product和Property。）在 12 字節和 404 字節索引之間進行選擇並不是困難的選擇。我認為所有尺寸都將採用 12 尺寸，即使 ProductPropertyValue技術上不在 3NF 中。

我說“技術上”並再次強調，ProductpropertyValue看起來好像不在 3NF 中的唯一原因是因為 DBMS 提供的標識列（ProductId和PropertyIdforProduct和Property）具有唯一性的很好的額外功能（實際上在SQL Server。您可以IDENTITY_INSERT ON/OFF在其他 DBMS 中設置或一些類似的屬性，並推送在標識列中不唯一的值。唯一性僅由您擁有的唯一約束來保證。）

當我的設計偏離簡單的分支“雪花”並且關係中需要存在“循環”時，似乎出現了這個挑戰：

P ---------> PT
^            ^
|            |
|            |
PPV ------> PTP

這就是為什麼我稱它為“鑽石形狀”！

請注意，那裡沒有循環！

• 只需從您喜歡的任何地方開始，按照箭頭。你能回到你開始的地方嗎？

• 沒有。所以沒有循環。

注意：這是一個有 4 個表的簡單範例。對於具有多級相關表的實際設計，問題變得更加嚴重（多列上的唯一約束，每個表有多個“不必要的”唯一約束等）。

嗯，這是個問題。當規則復雜並且涉及多個層次的多個實體時，結果也是一個複雜的模型，具有多列唯一約束和多列外鍵。這可能會影響性能，並且許多 DBMS 不太喜歡多列約束（例如，索引更寬，優化器可能表現不佳或根本無法辨識多列外鍵並錯過查詢重寫規則，等等。）。並且讓我們不要談論 ORM，他們中的大多數人都希望id在所有表中都有一個名為主鍵的列……

您對此無能為力。您可以使用通常很複雜的模型強制執行約束，或者從設計中刪除一些約束並簡化它。

您還可以使用不同級別的缺失規則、平衡性能、易用性和開發以及強制約束來測試多個設計。但至少你會知道你在哪裡簡化了，哪些約束沒有在數據庫級別強制執行。

---

看到一個類似的問題，在另一個問題中討論：Best data modeling approach to handleredundant foreign keys in relational model，@MDCCL 有一個很好的答案，其中包含許多細節，並且他建議與您的設計相同的解決方案。僅通過查看圖表就可以明顯看出相似之處：

       PropertyType                     Survey
          /   \                          /   \
         /     \                        /     \
        /       \                      /       \          
   Product     Property       PersonSurvey   QuestionSurevy
        \       /                      \       /
         \     /                        \     /
          \   /                          \   /
   ProductPropertyValue                 Response

如果我們檢查兩個問題的列和約束，細節也匹配。不同之處在於 MDCCL 從邏輯設計開始identity，在我們完成邏輯設計和規範化之後，您將看不到任何或其他應在物理設計的下一階段考慮的此類實現細節。

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