From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Design Problem...
Date: Sat, 24 Apr 2004 16:54:21 -0000
Message-ID: <725e4370e635c7b3c8ff293b53a284e9@biglumber.com> (raw)
In-Reply-To: <000001c4287b$db326d20$0202a8c0@buildways>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> The problem is that it is possible that the service can be switched to a
> different business unit, and then possibly back to the original later on.
First, you will get more responses if you do not create a new topic in
the middle of an existing thread. Here is a simplified answer to your problem.
Basically, you need to create a link between a business and a service,
and note when that link was created. You can grab the highest creation
time for a service to see which business currently owns it. Depending on
how often things change around, you may want to simply have a trigger on
the bs_map table that updates a "business" column in the services table,
rather than having to compute the max creation time constantly.
CREATE TABLE business (
id SERIAL UNIQUE,
bname VARCHAR
);
CREATE TABLE service (
id SERIAL UNIQUE,
sname VARCHAR
);
CREATE TABLE bs_map (
business INTEGER NOT NULL,
service INTEGER NOT NULL,
assigned TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_business_fk"
FOREIGN KEY (business) REFERENCES business(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk"
FOREIGN KEY (service) REFERENCES service(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404241255
-----BEGIN PGP SIGNATURE-----
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ
MTilGUtbg0y4DOAENUzXc80=
=Jw5D
-----END PGP SIGNATURE-----
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: greg@turnstep.com
Subject: Re: Design Problem...
In-Reply-To: <725e4370e635c7b3c8ff293b53a284e9@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox