Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You could create a scalar function for this and put that into a constraint. As a rule that's a bad idea, but it may work for this. –  JNK Feb 12 at 16:07
    
I would move the term_start and term_end columns to leader table and use the country_leader table just for linking. –  KNI Feb 12 at 16:09
    
You need something similar to this Maintaining referential integrity in a booking system or this Is it OK to keep a value which updates in a table? answer. –  ypercube Feb 12 at 17:19

2 Answers 2

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)
    );
share|improve this answer
    
+1 yes, exactly. –  AlexKuznetsov Feb 13 at 0:51
    
Thanks for this, it seems very close to something that I can make work in my actual case. I'm unsure about two things though. First, is there a good way to make this work if I'm not entering the data in chronological order? Say if I want to enter current leaders, then find the first leader for each country, then later those in power during WWII, etc. Second, is it correct that the first entry has to have its own seqno and termend repeated in prevseqno and prevtermend? (Also, sorry for the long delay in my reply, this is for a side project I didn't get much time for this month.) –  Michael Underwood Feb 23 at 18:45
    
Regarding the prevseqno and prevtermend question, no, that would hapeen only when ther eis one entry for a country. As soon as you have 2 or more, it would fail the UNIQUE (countryid, prevseqno) constraint (there would be two rows with same prevseqno=0). Note that this simulates a circular linked list, so the first entry (with seqno=0) would have to have prevseqno the last/max seqno for that country. –  ypercube May 24 at 18:53

I would consider removing to a separate table the position and dates of position.

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.