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@hotmail.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.