agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: skinner@britvault.co.uk
To: pgsql-sql@postgresql.org
Subject: Many to many link tables with history?
Date: Tue, 8 Oct 2013 18:04:15 +0100
Message-ID: <20131008170415.GA29813@teak.britvault.co.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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 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."
view thread (4+ messages) latest in thread
Message-ID: <20131008170415.GA29813@teak.britvault.co.uk>
Permalink: ../20131008170415.GA29813@teak.britvault.co.uk/
Also on: postgresql.org/message-id/20131008170415.GA29813@teak.britvault.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: skinner@britvault.co.uk
Subject: Re: Many to many link tables with history?
In-Reply-To: <20131008170415.GA29813@teak.britvault.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox