具有時間相關數據的連結/聯結表的主鍵

我想以這樣的方式連結兩個表，即在任何給定的時間段內，關係都是一對一的，但隨著時間的推移，關係會發生變化。

作為一個最小的例子，假設我想在一個跟踪許多政治家的數據庫中列出各個國家的領導人，包括那些擔任“領導人”以外的職位的人。我可以創建一張政客表和一張國家表，如下所示：

CREATE TABLE politician (
 id integer unsigned NOT NULL,
 name varchar(45) NOT NULL,
 PRIMARY KEY (id)
);

CREATE TABLE country (
 id integer unsigned NOT NULL,
 name varchar(45) NOT NULL,
 PRIMARY KEY (id)
);

現在，如果每個國家永遠只有一個領導人，我可以將這些與以下表格聯繫起來：

CREATE TABLE country_leader (
 countryid integer unsigned NOT NULL,
 leaderid integer unsigned NOT NULL,
 PRIMARY KEY (countryid, leaderid),
 KEY fk_countryleader_country_idx (countryid),
 KEY fk_countryleader_politician_idx (leaderid),
 CONSTRAINT fk_countryleader_country FOREIGN KEY (countryid) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT fk_countryleader_politician FOREIGN KEY (leaderid) REFERENCES politician (id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

然而，這種情況顯然不是這樣的，因為現有的領導人有時會重新當選（甚至可能在兩個不同的任期之間有一段時間），而當他們不是時，新的領導人就會接任。所以我認為一個更好的連結表應該是這樣的：

CREATE TABLE country_leader (
 countryid integer unsigned NOT NULL,
 leaderid integer unsigned NOT NULL,
 termstart date NOT NULL,
 termend date NOT NULL,
 PRIMARY KEY (???),
 CONSTRAINT ???,
 ....
);

所以我的問題是，在聯結表中填寫缺少的 PK 和約束資訊的最佳方法是什麼country_leader？還是我應該使用不同的方法來解決這個問題？

假如說：

• 每個國家在給定時間點最多有 1 個領導人（沒有重疊）。
• 一個國家的領導力可能存在差距（如果您不想允許這種情況，請將第二個約束更改為prevtermend = termstart）
• termstart < termend，即每個學期至少持續 1 天（如果不是這種情況，您可以更改下面的約束，方法是進行該約束<=和/或將date列轉換為datetime。）
• 您想以聲明方式強制執行所有這些約束。

那麼我認為以下（相當複雜的）方式可以做到——通過模擬每個國家的循環鍊錶）：

   CREATE TABLE country_leader (
     countryid integer unsigned NOT NULL,
     seqno integer NOT NULL,
     leaderid integer unsigned NOT NULL,
     termstart date NOT NULL,
     termend date NOT NULL,
     prevseqno integer NOT NULL,
     prevtermend date NOT NULL,
     PRIMARY KEY (countryid, seqno),
     UNIQUE (countryid, prevseqno),
     UNIQUE (countryid, seqno, termend),
     FOREIGN KEY (countryid) 
       REFERENCES country (countryid),
     FOREIGN KEY (leaderid) 
       REFERENCES politician (politicianid),
     FOREIGN KEY (countryid, prevseqno, prevtermend) 
       REFERENCES country_leader (countryid, seqno, termend),
     CHECK (termstart < termend),
     CHECK (prevtermend <= termstart OR seqno = 0),
     CHECK (prevseqno = seqno - 1 OR seqno = 0)
   );

我會考慮將職位和職位日期移到單獨的表格中。

Tables
------
Person
Country
Person_Role      FK to Person, listing all current and previous positions
Current_Leader   FK to Person and Country (may or may not want to have this)

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