Re: Complex database infrastructure - how to?

Edson Richter <edsonrichter@xxxxxxxxxxx> · Sat, 30 Jun 2012 22:46:20 -0300



    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@xxxxxxxxxxx>

          
            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.