I've a plan that will need a complex database infra-structure using
PostgreSQL 9.1.
I've seen similar setups using MS SQL Server and other databases, but
all of them support cross database queries (also easy to implement with
materialized views).
- Administrative database: have few tables, used to administer the
infrastructure. This database have some tables like "users", "groups",
"permissions", etc.
- Application databases: have app specific data.
1) One main Administrative application that will have read/write
permissions over the Administrative database.
2) Each application will have to access the application database (for
read/write), and the administrative database (for read only - mainly to
maintain the record references to the users that created objects, and so
on).
3) All applications are written in Java, using JPA for persistence.
4) All databases are running on same server, and all of them have same
encoding.
What I've tried so far:
1) Copy tables from Administrative to Application: this approach would
work, but I have trouble with the foreign keys. I'll have to disable (or
drop) them, then copy data, then activate (or recreate them again).
Could lead to problems?
2) dblink: I can't use foreign key to foreign tables. Also, it is very
hard to implement with JPA.
3) odbc_fdw: along with unstability, difficult to build/deploy, it is
too slow (why? - don't know)
4) JPA spacific multi-database approach: not really working, and can't
provide database integrity
My next try will be using triggers in Administrative database to send
data to Application databases using dblink.
Is there any ohter way to do that? Please, adivce!
Edson.