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.

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.

share|improve this question
    
An exclusion constraint over customer, domain and a timestamptz range (tstzrange) comes to mind .. –  Erwin Brandstetter Oct 9 '13 at 17:53
    
Thanks Erwin, I'll put an answer below as this comment box is too small. –  Craig R. Skinner Oct 10 '13 at 19:36
    
Guys, please don't close this question. It's interesting and well written. The author is new and has put in some effort. I would suggest upvotes instead of more close votes. –  Erwin Brandstetter Oct 10 '13 at 21:43
    
Related question using an exclusion constraint for a similar purpose: dba.stackexchange.com/questions/37351/… –  Erwin Brandstetter Oct 10 '13 at 22:05
    
Several moderators thought this question was "too broad" and requested a comment. I consider this question on how to maintain an event history of a many-to-many link table is fairly narrowly defined. Of course, design questions often have many possible (long) answers. For which I correctly tagged the question 'database-design', 'schema' & 'sql'. –  Craig R. Skinner Oct 12 '13 at 11:13
add comment

closed as too broad by RolandoMySQLDBA, Kin, bluefeet, Max Vernon, Mark Storey-Smith Oct 11 '13 at 23:16

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers

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)
share|improve this answer
    
Comments & improvements welcome. –  Craig R. Skinner Oct 10 '13 at 20:14
    
Nicely done! All your subscriptions are open end? None are ending at a pre-determined date? I.e., upper(active_period) > now()? –  Erwin Brandstetter Oct 10 '13 at 22:00
    
Cheers. Customers pre-pay for data volume. There is also a miniscule service charge per domain per day. When their bank balance empties, the subscription is ceased. Customers can continually buy more data blocks. Like pre-pay mobile phone accounts. A fair system where intensive users pay more than light users. i.e. light users aren't subsidising heavy users. –  Craig R. Skinner Oct 11 '13 at 9:45
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
That would work David. You've defined my thoughts above about the subscribed_dns_domain_history table –  Craig R. Skinner Oct 10 '13 at 19:31
add comment

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