Many to many link tables with history? - Mailing list pgsql-sql

Hi folks, I'm new here. I hope this is the correct list....


While creating a 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 February 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
DEFAULTnow(), 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
UPDATECASCADE ON DELETE RESTRICT, CONSTRAINT subscription_service_fk FOREIGN KEY (service)     REFERENCES service (id)
MATCHSIMPLE     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(),
CONSTRAINTdns_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
FOREIGNKEY (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_attimestamp 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_fkFOREIGN KEY (dns_domain)     REFERENCES dns_domain (id) MATCH SIMPLE     ON UPDATE
CASCADEON 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 subscribed_dns_domain table? Not sure how to populate
customer reference/ticket numbers in there too...

How about partial unique indexes or EXCLUDE USING gist () on the above table?

"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 http://www.postgresql.org/docs/9.2/static/indexes-unique.html

Thoughts/tutorial websites?
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7