Many to many link tables with history? While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to:


enable customers to subscribe to multiple services
each service can have multiple domain names per customer
a domain name must be unique per customer per timestamp


Example customers:

1 | Sun Microsystems (USA) Inc.
2 | Sun Microsystems (UK) Ltd.


Example domains:

1 | sun.com
2 | sun.co.nz
3 | sun.co.uk


If Sun USA subscribes with the .com &amp; .nz domains in January, then decides in April to have the offshore UK office manage these services instead, along with the .uk domain. Therefore the customer ID changes at a timestamp for billing in the different currency.

A domain name must be live (have no deleted_at timestamp) for only 1 customer per time. A customer may delete a domain &amp; re-add it later, or another customer may add it later. For billing, I need to know which customer had what domains active on what dates.

How do I manage the link between domains &amp; subscriptions, and maintain a history?

CREATE TABLE subscription
(
  id serial NOT NULL,
  customer integer NOT NULL,
  service integer NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  suspended timestamp with time zone,
  ceased timestamp with time zone,
  CONSTRAINT subscription_pk PRIMARY KEY (id),
  CONSTRAINT subscription_customer_fk FOREIGN KEY (customer)
      REFERENCES customer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscription_service_fk FOREIGN KEY (service)
      REFERENCES service (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

CREATE TABLE dns_domain
(
  id serial NOT NULL,
  name character varying(256) NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT dns_domain_pk PRIMARY KEY (id),
  CONSTRAINT dns_domain_uk UNIQUE (name)
)


This simple link table works, but without maintaining any history:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)


If I want to allow multiple instances of the same subscribed domain on different dates (only 1 having a NULL deleted_at timestamp), how do I do that? This doesn't work:

DROP TABLE subscribed_dns_domain;
CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  deleted_at timestamp with time zone,
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, added_at),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)


Should I revert to the first subscribed_dns_domain table, and also have a subscribed_dns_domain_history table, with timestamps populated by triggers on the simple subscribed_dns_domain table? Not sure how to populate customer reference/ticket numbers ...

How about a partial unique index or EXCLUDE USING gist () on the above table? A quote from the manual:


  Note: The preferred way to add a unique constraint to a table is ALTER
  TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
  constraints could be considered an implementation detail that should
  not be accessed directly.
From Erwin's suggestion above about using a tstzrange exclusion constraint, this seems to have the desired effect:

Firstly, as the admin user postgres I had to execute this CREATE EXTENSION btree_gist on the target database (PostgreSQL 9.2.3 on OpenBSD).

Then as the database user:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  active_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone, '[)'::text),
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, active_period),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_single_active_ek EXCLUDE 
  USING gist (dns_domain WITH =, active_period WITH &amp;&amp;)
)


New subscriptions to domains are easily added:

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 4);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 5);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (3, 6);


With it baulking on duplicate domain names with overlapping subscription time ranges (as desired):

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 6);

ERROR:  conflicting key value violates exclusion constraint "subscribed_dns_domain_single_active_ek"
DETAIL:  Key (dns_domain, active_period)=(6, ["2013-10-10 20:43:55.891139+01",)) conflicts with existing key (dns_domain, active_period)=(6, ["2013-10-10 20:11:48.54009+01",)).
********** Error **********


Subscriptions are easily ended:

UPDATE subscribed_dns_domain
SET
    active_period = tstzrange(lower(active_period), now())
WHERE
    subscription = 2 AND
    dns_domain = 4 AND
    upper(active_period) IS NULL


Active subscriptions are easily found:

SELECT
    subscription,
    dns_domain
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NULL

 subscription | dns_domain
--------------+------------
            2 |          5
            2 |          6
(2 rows)


As is historical data:

SELECT
    subscription,
    dns_domain,
    lower(active_period) AS added_at,
    upper(active_period) AS deleted_at
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NOT NULL

 subscription | dns_domain |           added_at            |          deleted_at
--------------+------------+-------------------------------+-------------------------------
            3 |          6 | 2013-10-10 20:10:14.269551+01 | 2013-10-10 20:11:33.467619+01
            2 |          4 | 2013-10-10 20:10:09.611487+01 | 2013-10-10 20:48:15.64681+01
(2 rows)