Many to many link tables with history? [closed]

Question

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 & .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 & 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 & 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.

Answer 1

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

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)

Answer 2

In the past I have found it useful to maintain two sets of date ranges in a history table.

The first date range is the "applies to period of time" range. This would indicate when, for your example, the linkage between a customer service and domain is made. You would use this range to determine what gets billed to who and for when. This range is set by your application probably based on some kind of direct action by your users.

The second date range is the "when did it look like this" range. This range would indicate when the database actually held the values indicated in the rest of the record. You use this range to show what was the state of data population as of a given point in time. This range is set automatically using defaults/triggers, etc. It is this range that gives you the history.

Answer 3

Would something like this suffice:

Ownership              Customer         OwnerHistory                    
===============        ========         =================               
CID    (PK, FK)  --->  CID (PK)  <----  CID          (FK)               
Domain (PK, FK)  -.    name         .-  Domain   (PK)(FK)               
                  |    etc          |   TimeFrom (PK)                   
                  |                 |   TimeTo                          
                  |                 |                                   
                  |    Domain       |                                   
                  |    ==========   |                                   
                  '->  Name  (PK) <-'                                   

with OwnerHistory managed by trigger - any insert or update in the Ownership table would result in OwnerHistory getting a new row for the new owner and the previous row (in the case of an update) being updated to fill in DateTo and any delete meaning just upadte DateTo on the relevant record. Client ID and domain being the primary key (or separate unique constraint/index) for the relationship table enforces the rule that one domain is owned by at most one user at any time.

The Ownership table contains current status and nothing more, the OwnerHistory table contains the trail needed for billing purposes, and you;ve avoided using much by way of DBMS specific features (though the trigger would need updated for syntax if you moved to another DBMS, its actual function would not need to be changed at all).