概括潛在參數化狀態的模式

假設我有一些實體：

create table entities (
 id serial primary key,
 foo text
);

…我想給它分配一些狀態，它有 N 個可能的值。一些上述狀態還帶有一個參數。例如：

• 富
• 酒吧
• 10 歲
• 20 歲
• qux, 100

我最初將其設置為一個單獨的表，其中狀態和參數由應用程序在執行時填充。然後這只是簡單地在狀態表中引用：

create table status (
 entity integer references entities(id),
 state integer references states(id),
 primary key (entity, state)
);

這樣做的問題是狀態的語義現在失去了，因此由應用程序來理解事物。

作為替代方案，我創建了一個列舉類型的狀態和一個可選的參數鍵：

create table status (
 id serial primary key,
 entity integer references entities(id),
 state state_enum not null,
 param integer default null,
 unique (entity, state),
 unique (entity, state, param)
);

我還沒有在這裡寫出來，但我還設置了一個精心設計的check約束，param以確保它僅在state“quux”時不為空。

（而不是使用列舉，我可以有一個將狀態作為硬編碼記錄的查找表；而不是參數，因為它們具有特定於應用程序的含義。無論哪種方式，它都是等價的。）

現在狀態由模式明確列舉，不必依賴應用程序來定義語義。但是，在我看來，這真的很混亂。

有沒有更優雅的方式來設計這個？

（如果它有所不同，就特性而言，我使用的是 PostgreSQL。）

定義StatusType將遷移到 a並為約束StatusLevel提供機制的 a 。CHECK

DML（我是徒手做的，可能潛伏著語法錯誤）：

CREATE TABLE StatusType
(
 StatusTypeCd  TEXT     NOT NULL
,Description   TEXT     NOT NULL
,CONSTRAINT PK_StatusType PRIMARY KEY (StatusTypeCd)
)
;
INSERT INTO StatusType VALUES ('N','Non-parameterized'),('P','Parameterized');

CREATE TABLE Status
(
 StatusCd      TEXT  NOT NULL
,StatusTypeCd  TEXT  NOT NULL
,Description   TEXT  NOT NULL
,CONSTRAINT FK_Status_Classified_By_StatusType FOREIGN KEY (StatusTypeCd) REFERENCES StatusType (StatusTypeCd)
,CONSTRAINT PK_Status PRIMARY KEY (StatusCd,StatusTypeCd)
,CONSTRAINT AK_Status UNIQUE (StatusCd)
)
;
INSERT INTO Status VALUES ('Foo','N','Foo description'),('Bar','N','Bar description'),('Quux','P','Quux description');

CREATE TABLE StatusLevel
(
 StatusCd      TEXT  NOT NULL
,Level         INT   NOT NULL
,StatusTypeCd  TEXT  NOT NULL
,CONSTRAINT FK_StatusLevel_Level_Of_Status FOREIGN KEY (StatusCd, StatusTypeCd) REFERENCES Status (StatusCd, StatusTypeCd) 
,CONSTRAINT PR_StatusLevel PRIMARY KEY (StatusCd,Level)
,CONSTRAINT CK_StatusLevel_Is_Valid CHECK (StatusTypeCd = 'P' OR Level = 0)
)
;
INSERT INTO StatusLevel VALUES ('Foo','0','N'),('Bar','0','N'),('Quux',10,'P'),('Quux',20,'P'),('Quux',30,'P');

從那裡您將StatusCd,Level直接遷移到entity或遷移到可選屬性entity_status：

筆記：

1. 名稱/描述應該有唯一的限制。
2. 三張桌子？是的。但它可以防止你做一些愚蠢的事情，並迫使你思考你在做什麼。
3. / /沒有Id列，因為它們是不必要的。StatusType``Status``StatusLevel

我認為這更好：

create table status (
 id serial primary key,
 entity integer references entities(id),
 state state_enum not null,
 unique (id, state)
);

create table quux_values (
 status integer primary key references status(id),
 state state_enum default 'warned' check (state = 'warned'),
 param integer not null,
 foreign key (status, state) references status(id, state)
);

即，quux參數存在於一個單獨的表中，其中存在一個“虛擬”狀態欄位，該欄位被強制為quux，並且ID和狀態的元組用作外鍵status（即，因此只能 quux將狀態記錄添加到quux_values）。

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