Re: Sharing static data among several databases

Thanks. For legal requirements, we need to keep each customer in a fully
isolated, separate db. (I'm not very familiar with schema - perhaps they
can do the same thing...).

What about just dropping the FKs? Can we do cross DB joins? Are there
significant performance penalties?

On 11/18/07, Douglas McNaught <doug(at)mcnaught(dot)org> wrote:
>
> "Robert James" <srobertjames(at)gmail(dot)com> writes:
>
> > 1.) Is there a way of separating, isolating, and sharing the shared data
> that
> > will still allow FKs to it?
>
> The only approach I know of would be to make all your customers use
> independent schemas in one database, with isolation via appropriate
> permissions, and have your shared data in another schema that all the
> users have read access to. Each user could have a search_path that
> contained their schema and the shared schema, which should minimize
> the amount of query-changing you'd need to do.
>
> Note that this is basically what you would do with Oracle--it doesn't
> have a concept of "database" really, just an "instance" that contains
> schemas (which map more-or-less onto database users).
>
> It would certainly be a change in your architecture, but how much work
> it would be I don't know...
>
> -Doug
>