Re: sharing data accross several databases

"bob lapique" <lapique(at)chez(dot)com> writes:

> Hi,
>
> I am designing 2 databases :
> - the 1st relates to flasks
> - the 2nd relates to cylinders
>
> Both flasks and cylinders will be shipped accross the
> world and I need to keep track of check out dates,
> shipping method, destination, etc. These data will be of
> the same type for both databases.
>
> Questions :
> -----------
> 1) Is a 3rd database the solution ?
> 2) I would like to use foreign keys (e.g. between a
> shipped item and the destination location)
>
> Thanks for help.

As of right now PostgreSQL doesn't have a way to do cross database
queries, and so until schema support is available you want to put all
of the data that you might want to join in the same database.

So in answer to question #1 I don't think that a third database would
be a good idea (a third table might be a good idea though).

In answer to your second question PostgreSQL has very good support for
foreign keys. So you can easily create structures like this:

CREATE TABLE locations (
id SERIAL PRIMARY KEY,
address1 text,
address2 text,
city text,
you_get_the_idea text
);

CREATE TABLE shipment (
id SERIAL PRIMARY KEY,
type varchar(8) CHECK IN ('flask', 'cylinder'),
destination int REFERENCES locations,
more_stuff text
);

If you have more of these types of questions you might considering
subscribing to pgsql-sql(at)postgresql(dot)org(dot) There are a lot of clever
folks on that list.

Jason