-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200404241255
-----BEGIN PGP SIGNATURE-----
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ
MTilGUtbg0y4DOAENUzXc80=
=Jw5D
-----END PGP SIGNATURE-----