Re: Connection to second database on server

On Sun, Aug 24, 2008 at 10:19 PM, Troy Rasiah <troyr(at)vicnet(dot)net(dot)au> wrote:
>
>
> Scott Marlowe wrote:
>> On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr(at)vicnet(dot)net(dot)au> wrote:
>>> Sorry for bringing up an old post...If you have a generic set of tables..
>>>
>>> eg. table of countries / post codes etc which are used across several
>>> databases what is the best way to access / store them?
>>> I currently
>>> - use dblink to create views when i want to do joins,
>>> OR
>>> - i just open up a separate db handle when i just want to display the
>>> data (via a perl script) from the 'generic database' (eg. a select list
>>> of countries)
>>>
>>> but was wondering whether schema's would apply to me as well ?
>>
>> Yes, schemas would be much better. The nice thing is with
>> search_path, you could have a setup where application1 and
>> application2 live in different schemas but have access to a common
>> schema. When running app1, you'd do something like:
>>
>> set search_path='app1','commonschema';
>>
>> and when running app2 you'd change the app1 up there to app2 and then
>> you could access the tables in both schemas without having to use
>> prefixes.
>
>
> Thanks Scott. We currently do websites for different customers on the
> same machine so we have been setting each of them up with individual
> (database,user,pass).
>
> Instead should i be setting them all up in the one database and having
> individual schema's for each customer and then only granting each user
> access to their schema & the proposed 'commonschema' ?

That's how I'd do it. You probably want to drop the public schema as
well. If you need to separate everybody from each other into their
own database, then you could always replicate the common schema to
each db, but if the common schema is large or changes a lot this
could be a pain.