Sharing static data among several databases

"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

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?

"Robert James" <srobertjames@gmail.com> writes:

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...).

[ shrug... ] If your lawyers insist on that, wouldn't they also object
to all customers linking to the same copy of the shared data? They
should, if they know what they're about.

regards, tom lane

On Nov 19, 2007 11:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

[ shrug... ] If your lawyers insist on that, wouldn't they also object
to all customers linking to the same copy of the shared data? They
should, if they know what they're about.

You're implying that that lawyers understand what database, schema
and shared data are ... ?

regards, tom lane

Cheers,
Andrej

On Nov 19, 2007 12:29 PM, Robert James <srobertjames@gmail.com> wrote:

Comedy aside, this makes a lot of sense:
The shared data has nothing private in it at all - it's chemical info.
Sharing it is no worse than sharing the application code, or the OS's
libraries. It's the customer's data which needs to be isolated.

I appreciate that. But realistically if you had locked information isolation
down via permissions and appropriate views the information for each
customer would be as safe as it would using separate databases or
even servers.

Cheers,
Andrej

P.S.: I assume this was meant to go to the list, not to me as an individual;
try reply-all for this list.
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Comedy aside, this makes a lot of sense:
The shared data has nothing private in it at all - it's chemical info.
Sharing it is no worse than sharing the application code, or the OS's
libraries. It's the customer's data which needs to be isolated.

On 11/18/07, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote:

I appreciate that. But realistically if you had locked information
isolation
down via permissions and appropriate views the information for each
customer would be as safe as it would using separate databases or
even servers.

True. But, being human, we make mistakes. The simpler things are, the less
likely the chance of mistake. Sep. DBs = simple, dumb. "ocked information
isolation down via permissions and views" = complicated, smart. When it
comes to reliability, dumb is good.