Multiple DBs, One app, How to do relations?
Dear List,
We have a large project with three of the larger components (of the
project) being the eventual creation of a Global Product DB and a Global
Customer DB and a Global Dealer DB.
Because of certain legacy issues, the schema has evolved to be one large
application specific schema containing product, customer, and dealer
tables. A better design would be three separate databases (and we can
pull them apart easily enough), but the question becomes how to make the
product to customer to dealer relations between distinct databases using
Postgres.
The following would be ideal (Oracle supports the following)
SELECT foo FROM customerDB.addressTable, productDB.priceTable WHERE
customerDB.addressTable.customer_id = productDB.priceTable.customer_id
Does anyone have any experience or suggestions working with multiple
db's within the same application. How best to do the relations. Within
the application code space? Using stored procedures? or other?
Sincerely,
Eric Dahnke
Sr. Engineer
Yamaha Music Division
Hello,
Actually why don't you use name spaces? That way you can segregrate your
data but not have to have actual separate databases.
Sincerely,
Joshua D. Drake
Command Prompt, Inc.
Eric Dahnke wrote:
Show quoted text
Dear List,
We have a large project with three of the larger components (of the
project) being the eventual creation of a Global Product DB and a Global
Customer DB and a Global Dealer DB.Because of certain legacy issues, the schema has evolved to be one large
application specific schema containing product, customer, and dealer
tables. A better design would be three separate databases (and we can
pull them apart easily enough), but the question becomes how to make the
product to customer to dealer relations between distinct databases using
Postgres.The following would be ideal (Oracle supports the following)
SELECT foo FROM customerDB.addressTable, productDB.priceTable WHERE
customerDB.addressTable.customer_id = productDB.priceTable.customer_idDoes anyone have any experience or suggestions working with multiple
db's within the same application. How best to do the relations. Within
the application code space? Using stored procedures? or other?Sincerely,
Eric Dahnke
Sr. Engineer
Yamaha Music Division---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend