I'm currently using MySQL + PHP for various in house production tracking and
reporting applications. I'm looking to convert a few of these apps over to
PostgreSQL 7.0.3 for performance and stability comparisons and to test out
some of the more advanced features of Postgre. Unfortunately I've hit a
wall with this project. PostgreSQL does not allow select statements to work
over multiple databases (a feature I use quite extensively in MySQL). To
illustrate my predicament I'll describe the setup:
We have 13 databases in our system, 12 of which are used for tracking
production (one for each department). The 13th one is called our "support"
database which is used to store tables that are common to all the tracking
databases. Tables in this database include region codes, state names and
common product listings. Some of these reference tables are quite large,
making the obvious choice of duplicating the necessary tables in each
database a bit wasteful (also quite difficult to maintain). Since I'm using
PHP, and have a very nice database abstraction system built, I also
considered using a generic "valueLookup" function which would pull the
reference values from the support database and match them up. I would go
with this route but I'm guessing this will probably slow things down
considerably.
So, this is the impasse I'm at, and I'm wondering if anyone else has
encountered this situation and has a simple solution. I don't consider the
decision to make a reference database as a bad decision. It makes sense to
share data between systems :)
PS: Please cc me in all replies. I'm not subscribed to this list.
- Pat Wendorf