Re: sharing data accross several databases

"bob lapique" <lapique@chez.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@postgresql.org.  There are a lot of clever
folks on that list.

Jason