Re: Complex database infrastructure - how to?

Em 30/06/2012 22:02, Misa Simic escreveu:
> Hi Edson,
>
> Maybe the best option is what Jack has already suggested, to have all
> in 1 DB in separate schemas (if tables are not already organised in
> separated schemas...)... But I am not sure I understand well problems:
>
> 2012/6/30 Edson Richter <edsonrichter(at)hotmail(dot)com
> <mailto:edsonrichter(at)hotmail(dot)com>>
>
> 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.
>
>
>
> 1) I guess data just need to be coppied in proper order... though,
> Postgres have deffered constraint feature, what means, FK will not
> break transaction until end (all trans commited) - untill all data
> copied...
>
> 2) I just wonder On what way it is possible in MS SQL Server or any
> other db engine? (to have FK to foreign table... )
>
>
> Thanks,
>
> Misa

I'm inclined to solution (1). Setup proper triggers to copy data into
all related databases would be easier (and have better performance).

Working with multiple schemas seems that I'll have lot of changes either
in applications and maintenance routines.

Also, I'll have different databases replicated to different servers,
according to some criteria. It would be impossible to set with schemas,
right?

About solution (2), it's my mistake: MS SQL does not support it. MySQL
does. Oracle allows to create foreign keys by using materialized views
(that, for instance, can be cross database). MS SQL and DB2 only allow
cross-database queries (that, perhaphs, I really don't understand why is
not supported in PgSQL).

Regarding why support it? Multi-tenant systems are the first though. If
you have separate databases because security issues (like HR database,
that must be completely separated - not only database, but also physical
server - from other applications due security constraints). But one
table or view with correct clearance would be acceptable...

Thanks,

Edson.