From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Design Problem...
In-Reply-To: <000001c4287b$db326d20$0202a8c0@buildways>
Date: Sat, 24 Apr 2004 16:54:21 -0000
Message-ID: <725e4370e635c7b3c8ff293b53a284e9@biglumber.com>


-----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-----