關於具有可能為空的第二個元素的建模對

編輯：我已經大大縮短了文章。我希望它現在更容易消化。

---

考慮以下架構：

CREATE TABLE X (
     x TEXT PRIMARY KEY
);

CREATE TABLE Y (
     y TEXT PRIMARY KEY
   , x TEXT NOT NULL
   , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
     data_id INTEGER PRIMARY KEY
   , y TEXT NOT NULL
   , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE VIEW mydata AS

 SELECT X.x, Y.y

   FROM X JOIN Y     USING (x)
          JOIN _DATA USING (y);

此模式對mydata視圖的行實施以下約束：

1. 沒有兩行可能具有相同的y-value 但不同的x-value；
2. x-value 永遠不會為空；
3. y-value 永遠不會為空；

以下輸出列表將與此模式一致：

清單 1

SELECT * FROM mydata;
x | y
---+---
a | h
b | i
b | j
c | k
d | l
a | h

現在，假設我們有以下三個約束，而不是上面的三個約束：

  1’。沒有兩行可以具有相同的非空 y值但不同的值x；

  2’。x-value 永遠不會為空；

（約束 2’ 與約束 2 相同；約束 1’ 需要將限定符“非空”添加到約束 1。）

使用這些新約束，以下列表（缺少y-values NULL）將變得有效：

清單 2

SELECT * FROM mydata;
x | y
---+---
a | h
b | i
b |
c | k
d |
a | h

但是對於上面顯示的模式，這樣的列表是不可能的。即使NOT NULL從列的定義中刪除了約束_DATA.y，我們得到的最接近的是

清單 3

SELECT * FROM mydata;
x | y
---+---
a | h
b | i
c | k
a | h

…因為無法確定x那些行的適當值y是NULL。

我正在尋找一個規範化的模式，它將強制執行新的約束集（從而允許像清單 2中所示的數據）。

---

我的“非解決方案”

我對這個問題的“非解決方案”是一個難以維護的黑客。我在這裡發布它有三個原因：

1. 澄清上述問題中描述的情況；
2. 提供表初始化程式碼，如果響應者願意，他們可以使用它來測試他們的提案（實際上這是我用來生成清單 2的程式碼）；
3. 舉一個我試圖避免的難以維護的黑客的例子。

這種“非解決方案”包括定義一些y可以以某種方式辨識為的不同值NULL，並重新定義mydata以利用這些資訊。例如：

INSERT INTO X VALUES
   ('a')
 , ('b')
 , ('c')
 , ('d')
;

INSERT INTO Y VALUES
   ('h', 'a')
 , ('i', 'b')
 , ('NULL_b', 'b')
 , ('k', 'c')
 , ('NULL_d', 'd')
;

INSERT INTO _DATA VALUES
   (1, 'h')
 , (2, 'i')
 , (3, 'NULL_b')
 , (4, 'k')
 , (5, 'NULL_d')
 , (6, 'h')
;


CREATE TEMP VIEW mydata AS
 SELECT x,
        CASE WHEN y LIKE 'NULL_%'
             THEN NULL
             ELSE y
        END AS y
   FROM X JOIN     Y USING (x)
          JOIN _DATA USING (y);

然後

SELECT * FROM mydata;
x | y 
---+---
a | h
b | i
b | 
c | k
d | 
a | h
(6 rows)

醜得像罪孽，但拋開審美不談，也很難維持。

我看到的其他選項：

A）將列添加x到表中_data。還需要進行一些更改：

CREATE TABLE X (
     x TEXT PRIMARY KEY
);

CREATE TABLE Y (
     y TEXT PRIMARY KEY
   , x TEXT NOT NULL
   , FOREIGN KEY (x) REFERENCES X(x),
   , UNIQUE (x, y)                    -- required for the modified FK below
);

CREATE TABLE _DATA (
     data_id INTEGER PRIMARY KEY
   , x TEXT NOT NULL
   , y TEXT
   , FOREIGN KEY (x, y) REFERENCES Y(x, y)    -- modified FK
   , FOREIGN KEY (x) REFERENCES X(x)          -- additional FK
);

視圖也必須修改：

CREATE VIEW mydata AS
 SELECT X.x, Y.y
   FROM X JOIN Y     USING (x)
          JOIN _DATA USING (y)
 UNION ALL
 SELECT X.x, NULL
   FROM X JOIN _DATA USING (x) 
 WHERE _DATA.y IS NULL ;

例子：

INSERT INTO X (x) VALUES
   ('a')
 , ('b')
 , ('c')
 , ('d')
;

INSERT INTO Y (y, x) VALUES
   ('h', 'a')
 , ('i', 'b')
 , ('k', 'c')
;

INSERT INTO _DATA (data_id, x, y) VALUES
   (1, 'a', 'h')
 , (2, 'b', 'i')
 , (3, 'b', NULL)
 , (4, 'c', 'k')
 , (5, 'd', NULL)
 , (6, 'a', 'h')
;

SELECT x, y FROM mydata;
x | y 
---+---
a | h
b | i
c | k
a | h
b | 
d | 
(6 rows)

---

B) 分離沒有y關聯到另一個表的值的數據。不需要對現有表進行任何更改，僅針對視圖：

CREATE TABLE X (
     x TEXT PRIMARY KEY
);

CREATE TABLE Y (
     y TEXT PRIMARY KEY
   , x TEXT NOT NULL
   , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
     data_id INTEGER PRIMARY KEY
   , y TEXT NOT NULL
   , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE TABLE _DATA_X (
     data_x_id INTEGER PRIMARY KEY
   , x TEXT NOT NULL
   , FOREIGN KEY (x) REFERENCES X(x)
);

視圖修改：

CREATE VIEW mydata AS
 SELECT X.x, Y.y
   FROM X JOIN Y     USING (x)
          JOIN _DATA USING (y)
 UNION ALL
 SELECT X.x, NULL 
   FROM X JOIN _DATA_X USING (x) ;

例子：

INSERT INTO X (x) VALUES
   ('a')
 , ('b')
 , ('c')
 , ('d')
;

INSERT INTO Y (y, x) VALUES
   ('h', 'a')
 , ('i', 'b')
 , ('k', 'c')
;

INSERT INTO _DATA (data_id, y) VALUES
   (1, 'h')
 , (2, 'i')
 , (3, 'k')
 , (4, 'h')
;

INSERT INTO _DATA_X (data_x_id, x) VALUES
   (1, 'b')
 , (2, 'd')
;

SELECT x, y FROM mydata;
x | y 
---+---
a | h
b | i
c | k
a | h
b | 
d | 
(6 rows)

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