如何強制執行矩形陣列數據的結構約束？

（我對將以下問題視為問題特例的答案特別感興趣：RDBMS 應如何強制執行比“一對多”和“多對多”更具體的結構約束？）

生物醫學研究中的許多實驗數據都收集在矩形排列的“孔”的“板”中。這些孔陣列板是市售的幾種標準尺寸：2 × 3、4 × 6、8 × 12、16 × 24 和 32 × 48。

考慮以下兩種用於在 RDB 中儲存來自 2 × 3 板孔的測量值的替代方法：

-- alternative 1
CREATE TABLE measurement_foo (
   plate_id FOREIGN KEY REFERENCES plate(plate_id),
   plate_row CHAR(1),
   plate_column INTEGER,
   value REAL
);

-- alternative 2
CREATE TABLE measurement_foo (
   plate_id FOREIGN KEY REFERENCES plate(plate_id),
   a1 REAL,
   a2 REAL,
   a3 REAL,
   b1 REAL,
   b2 REAL,
   b3 REAL
);

我的直覺是選擇備選方案 1：它適用於任何尺寸的板，並且可以以直接的方式進行修改，以記錄每個孔的多個不同測量值，如

CREATE TABLE measurement (
   plate_id FOREIGN KEY REFERENCES plate(plate_id),
   plate_row CHAR(1),
   plate_column INTEGER,
   foo FLOAT,
   bar FLOAT,
   baz FLOAT
);

相反，為了使備選方案 2 適應 16 × 24 孔格式（例如），需要定義一個具有 1 + 384 列的表：plate_id, a01, …, a24, b01, … , b24, …, p01, 。 .., p24. 此外，需要為每種類型的測量定義不同的此類表。

我什至考慮替代方案 2 的主要原因是它單獨強制每個板包含正好 6 個孔的約束；備選方案 1 沒有。

（此外，備選方案 2 中的表的一條記錄將包含與備選方案 1 中的最多 6 條記錄一樣多的資訊，這讓我想知道備選方案 1 中的表是否已完全規範化。）

對於備選方案 1，我可以想像一些方法來強制執行一個板最多包含6 個孔的較弱約束，即通過對 (plate_id、plate_row、plate_column) 施加唯一性約束，並將列 plate_row 和 plate_column 限制為域 (‘a ‘, ‘b’) 和 (1, 2, 3)。但是數據中仍然可能缺少油井。

數據庫應該如何強制執行板和孔之間的結構關係？

目前所有 RDBMS 的表都可以有CONSTRAINTSon 列。每次將數據插入表時都會檢查這些約束。它還可以對照其他表檢查數據。

我們知道每種板類型都有一定數量的行和列。我們可以列舉每個板類型的所有行和列。因此，當插入數據時，數據庫可以檢查給定板類型是否存在某個行/列組合。

讓我們創建一組表：

create table Plate_Types (
Plate_Type_id int,
Plate_Size int,
Plate_row int,
Plate_col int)

此表包含每個板尺寸的描述，如下所示：

Id     Size   Row     Col
1       6       1       1    -- 2x3
1       6       1       2
...
1       6       2       3
5       1536    1       1    -- 32x48
...
5       1536    32      32

然後，在備選方案 1 的主表中，我們引入了一個外鍵 - 一個指向另一個表的“連結”，以檢查行和列對於該板尺寸是否有效。

create table MyTable (
well_id int,
plate_id int, 
plate_size int,
row_id int,
col_id int,
value real);

ALTER TABLE MyTable  
ADD CONSTRAINT FK_Plate_SizeCheck 
  FOREIGN KEY (Plate_size_id, Row_id, Column_id)
   REFERENCES Plate_Types (Plate_type_id, Plate_row, Plate_col);

此處的此約束執行以下操作：對於每個插入的行 DB 轉到表 Plate_Types 並查找 和 的Plate_size_id, Plate_row組合Plate_col。換句話說，它會檢查此 Plate Size 是否可以包含第 I 行和第 J 列。如果不匹配，則 DB 會觸發錯誤。

請注意，對於您的範例，這是實施數據完整性的幾種可能解決方案之一。醫療數據通常大量出現，這種特殊設計的性能是不同的問題。

PS。這是對非開發人員的簡短解釋。程式碼和表格設計僅用於概念說明。

我會向您推薦備選方案 1。

就像將單元格作為列 a01、a02 等一樣，正規化很差，測量值也是 m01、m02 等。在您的範例中，您通過稱它們為 foo、bar 和 baz 來掩飾這一點，但它們就是這樣。為了解決這個問題，你稱之為measurement更有用的是Well：

CREATE TABLE Well (
   plate_id FOREIGN KEY REFERENCES plate(plate_id),
   plate_row CHAR(1),
   plate_column INTEGER,
);

並且會有新表：

CREATE TABLE MeasurementType (
   measurement_id CHAR(3)  -- values "foo", "bar" and "baz"
);

測量值在這兩者的交集處：

CREATE TABLE measurement (
   -- Primary key of Well
   plate_id,
   plate_row CHAR(1),
   plate_column INTEGER,
   -- Primary key of MeasurementType
   measurement_id CHAR(3),
   -- the value measured from this well
   measured_value FLOAT
);

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