Primary key for link/junction table with time-dependent data

database-designdateforeign keyprimary-key

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change.

As an minimal example, suppose I want to list the leaders of various countries in a database that tracks many politicians, including those in positions other than 'leader'. I can create a table of politicians, and a table of countries, like so:

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)
);

Now, if every country had only a single leader forever, I could just link these with a table such as:

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
);

However, this situation obviously isn't the case, because existing leaders are sometimes re-elected (possibly even with some time between two different terms in office), and when they aren't then new leaders take over. So I think that a better link table would look something like this:

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 ???,
  ....
);

So my question is, what is the best way to fill in the missing PK and constraint information in the junction table country_leader? Or should I be using a different approach to solve this problem?

Best Answer

Assuming that:

  • every country has at most 1 leader in a given point in time (no overlaps).
  • there may be gaps in a country's leadership (if you don't want to allow that, change the scond constraint to prevtermend = termstart)
  • termstart < termend, i.e. every term lasts at least 1 day (if that is not the case, you can alter the constraints below, either by making that <= and/or converting the date columns to datetime.)
  • you want to enforce all these constraints declaratively.

then I think the following (rather complicated) way will do - by simulating a circular linked list for every country):

    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)
    );
Related Question