Re: Design Problem...

-----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
ONUPDATE CASCADE; 
 
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk"FOREIGN KEY (service) REFERENCES service(id)ON DELETE RESTRICT ON
UPDATECASCADE; 
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404241255
-----BEGIN PGP SIGNATURE-----
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ
MTilGUtbg0y4DOAENUzXc80=
=Jw5D
-----END PGP SIGNATURE-----