Re: PgSQL and integration between 2 PHP programs and a Java program

Hi, folks!

On 11/9/06, Brew <brew@themode.com> wrote:
> Amir.....
>
>
> >
> > After all I have thought of 3 methods of integration. I would like
> everyone who have an experience to share it with me and vote for the best
> methd that comes to your mind :
>
>
> I vote for number 1.
>
> Unless you have some other reason to do a major rewrite, it seems the
> easiest to share the username and passwords across applications.  It's nice
> to make things perfect, but sometimes you have to look at how much effort
> versus the return.
>
> > Cons : Huge amount of data duplication as each username. password, address
> and other
> > related fields will be the same in many databases! , High database loads
> due to multiple
> > UPDATE of several tables in different DBs.
>
> Yes, but disk space is cheaper than the labor, and time, of all that
> rewriting.  And further, except for the added CAS table, it's no more than
> the space you are already using.  No wait, that's not so, each table will
> have ALL the users now, not just a subset.  Oh, well.
>
> As far as updating, that only happens when a user makes a change, or maybe
> once when he logs in.  And if it's too slow you can find a way to do it in
> the background, let the user set it in the CAS and be done, then find
> another way to propagate the changes to the other database tables.  A
> trigger (maybe, not sure) or some recurring job that finds data that hasn't
> been propagated and does it, then sets a flag column for that user that it's
> been synced.
>
> I'm sure others on the list can think of some more (maybe better) ways.
>
> So I'm voting for number one.  On the other hand, I can think of times that
> I've cobbled something together and then realized I should have taken the
> time to do it right in the first place.  It's a hard call to make up
> front!!!
>
>
> brew
>
> --
>
> Strange Brew (brew@theMode.com)
>
> Check out my Stock Option website http://www.callpix.com
> and my Musicians Free Classified http://www.TheMode.com

Indeed, it's a hard call, but if you go for option 1 (keeping account
information in all your existing databases AND LDAP repository),
perhaps you could take some time and try something like PostgreSQL
DBI-Link (http://pgfoundry.org/projects/dbi-link/), in which
theoretically you could access any data source available for perl DBI
(and that includes of course pgsql, mysql, ldap, and many others) as
postgresql tables, so the overhead and complications of updating
several databases could be diminished.

Also, I'd dare to suggest a 4th option for consideration: centralize
account information only (perhaps in LDAP repository), keep
application-specific information in application-specific databases. Of
course, that would mean some code rewriting (but not for all
application queries, just for account information ones, and you could
take advantage of your CAS apis). I've seen this approach work in
several scenarios, but of course, there's no perfect solution for
every situation, and YMMV.

Thanks a lot to all you guys keeping up the good work in the
PostgreSQL community, and any other suggestions/experiences regarding
this case will surely be interesting.

Greetings!