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?