Scott Marlowe wrote: > On Sun, Aug 24, 2008 at 10:19 PM, Troy Rasiah <troyr@xxxxxxxxxxxxx> wrote: >> >> Scott Marlowe wrote: >>> On Sun, Aug 24, 2008 at 8:43 PM, Troy Rasiah <troyr@xxxxxxxxxxxxx> 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. Thanks for the info, much appreciated. -- Troy Rasiah
