Multiple customers sharing one database?
I'm developing an application that will be used by several independent
customers. Conceptually, the data from one customer has no relation at
all to another customer. In fact, each customer's data is private, and
you would never combine data from different customers. I'm trying to
decide whether to:
a) give each customer a separate database
or
b) put everyone in the same database, but take steps to ensure that
customer #1 cannot see data from customer #2.
I was talking to an Oracle expert who suggested (based on his oracle
background) option b where each customer has their own schema. His
feeling was that each database has significant memory overhead, which
then gets multiplied with option a. At least it does with oracle.
Does anyone have any suggestions?
On Jun 1, 2007, at 13:27 , Rick Schumeyer wrote:
I'm developing an application that will be used by several
independent customers. Conceptually, the data from one customer
has no relation at all to another customer. In fact, each
customer's data is private, and you would never combine data from
different customers. I'm trying to decide whether to:
Unless the database schema is shared by the different customers, I'd
set up a separate database for each. There's better security with
separate databases, and since different customer's data would never
be combined, there's no advantage to putting them in the same one.
Per database overhead is probably going to be negligible compared to
the infrastructure you'd want to put in place for security.
Michael Glaesemann
grzm seespotcode net
Rick Schumeyer wrote:
I'm developing an application that will be used by several independent
customers. Conceptually, the data from one customer has no relation
at all to another customer. In fact, each customer's data is private,
and you would never combine data from different customers.
Michael Glaesemann wrote:
Unless the database schema is shared by the different customers, I'd set
up a separate database for each. There's better security with separate
databases, and since different customer's data would never be combined,
there's no advantage to putting them in the same one. Per database
overhead is probably going to be negligible compared to the
infrastructure you'd want to put in place for security.
I am always confused by the overloaded term "database" in such discussions.
Do we mean the RDBMS engine, i.e., run separate instances of PG? I sure would
recommend against that. Or do we mean it as the PG documentation does, e.g.,
<http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html>
A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.
That seems to be PostgreSQL's answer to the OP's question.
Note: Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of user names means that there cannot be different users named, say, joe in two databases in the same cluster; but the system can be configured to allow joe access to only some of the databases.
In the OP's case, "some of the databases" is "one of the databases".
--
Lew
On Jun 3, 2007, at 12:45 , Lew wrote:
Michael Glaesemann wrote:
Unless the database schema is shared by the different customers,
I'd set up a separate database for each. There's better security
with separate databases, and since different customer's data would
never be combined, there's no advantage to putting them in the
same one. Per database overhead is probably going to be negligible
compared to the infrastructure you'd want to put in place for
security.I am always confused by the overloaded term "database" in such
discussions. Do we mean the RDBMS engine, i.e., run separate
instances of PG? I sure would recommend against that. Or do we
mean it as the PG documentation does, e.g.,
<http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html>
I likewise try to be careful in my usage of database and database
server as they are different things. I meant (and used) database, not
database server.
Michael Glaesemann
grzm seespotcode net
On 06/03/07 12:45, Lew wrote:
[snip]
I am always confused by the overloaded term "database" in such
discussions. Do we mean the RDBMS engine, i.e., run separate instances
of PG? I sure would recommend against that. Or do we mean it as the PG
documentation does, e.g.,
<http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html>
A (relational) database is a database, and an RDBMS is what
manipulates that (relational) database.
"The" schema "defines" the database. "A" schema defines a specific
logical sub-set of "the" schema.
--
Ron Johnson, Jr.
Jefferson LA USA
Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!