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@mcnaught.org> wrote: "Robert James" <srobertjames@gmail.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