Re: Complex database infrastructure - how to?

On 6/30/2012 9:25 AM, Edson Richter wrote:
> 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.
>
>
Consider using one database with multiple schemas. You can separate your
applications into their own schemas, and you can have cross-schema
foreign keys.

--
Jack Christensen
http://jackchristensen.com/