Re: shared data for different applications - Mailing list pgsql-general

On 22 Nov 2010, at 4:43, Kent Tong wrote:

> Hi Alban,
>
> Thanks a lot for your useful info!
>
>> I think most companies have ended up at that point just by the progress
>> of time. They have several different databases (often from different
>> vendors even) that they need to aggregate their information from.
>
> So, is this the result of lack of central coordination or a carefully
> thought-out decision?

It's usually a mix of both. While a company grows, requirements change, for example. Also, there tend to be 3rd-party
applicationsthat don't work with the company's database of choice, necessitating to install a second database, etc. 

Being in a situation where you get to decide this without the historic requirements is pretty cool, but, no matter how
carefulyou plan now, history tends to catch up with you. That doesn't mean you shouldn't try your best to prevent it
to,though ;) 

>> Usually the different databases contain different kinds of contents, so
>> the need for distributed transactions and such is quite minimal.
>
> Let's say, if a customer would like to change his address through
> a certain app (eg, the web interface for customers), and assuming
> that customer info is shared across the whole company, then a
> distributed transaction will be required, right? Or there is a better
> approach?

I don't really see why that would require a distributed transaction. They can just directly change a record in the
masterdatabase, can't they? 

If I were you, I certainly wouldn't let them change their _live_ data directly in your production database though!
You'llwant somebody (in your company) to approve what they entered - people who're not familiar with a system (it's
yours,not theirs, after all) are bound to make mistakes, no matter how obvious you make the interface. 

There are all kinds of approaches to that though, it doesn't mean you need a separate database for their data. You
couldfor example keep an approval flag if the data is from the application that the customers use to update their data. 
You could also keep a separate database around, but then you're quickly moving in the direction of master-master
replication,which is really quite complicated due to the conflicting data it tends to generate between masters. 

Now, before you storm off to implement what I'm telling you; I'm not an expert on this issue. I know my databases and
all,but I haven't been in your position and I've never had an opportunity (or a reason) to put replication to practice.
I'veread a lot about it, mostly from this mailing list, but my knowledge in that respect is mostly theoretical. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4cea1ff710421896774915!