Thread: Two many databases...

Hallo,

  would some experienced person care to comment on the following
problem?

I have, say, an accounting application using Postgres and I'm using it
for several (potentialy hundred) clients. Basically, I have two
possibilities:

- each client gets separate database
- one common database for all clients, each table has client_id field
added

First possibility is a maintenance nighmare (upgrading etc. hundred
databases instead of one) while the second possibility makes possible
(and quite likely) one client will see or modify other's data due some
programmer's errors (I'm sure there will be bugs in the application all
the time no matter what we do). Now, the idea I like best is to create
set of views for each client and let the application see just that - I
would never have to worry about application bugs (well, hardly ever).
However, this "virtual" database is read-only because in Postgres views
are read-only. Tom L. said in private email that I can create my own set
of rules for inserting/deleting/updating views, which could be a good
compromis I guess. What do you think? Thanks for your comments.

  - Robert

If there was any possibility you may want to do joins across more than
one client's database, then I would say make it one database. But if the
data is truely separate and no application wants to read more than one
database at once, then make them separate. Upgrades should be automated
anyway.


Robert wrote:
>
> Hallo,
>
>   would some experienced person care to comment on the following
> problem?
>
> I have, say, an accounting application using Postgres and I'm using it
> for several (potentialy hundred) clients. Basically, I have two
> possibilities:
>
> - each client gets separate database
> - one common database for all clients, each table has client_id field
> added
>
> First possibility is a maintenance nighmare (upgrading etc. hundred
> databases instead of one) while the second possibility makes possible
> (and quite likely) one client will see or modify other's data due some
> programmer's errors (I'm sure there will be bugs in the application all
> the time no matter what we do). Now, the idea I like best is to create
> set of views for each client and let the application see just that - I
> would never have to worry about application bugs (well, hardly ever).
> However, this "virtual" database is read-only because in Postgres views
> are read-only. Tom L. said in private email that I can create my own set
> of rules for inserting/deleting/updating views, which could be a good
> compromis I guess. What do you think? Thanks for your comments.
>
>   - Robert

On Thu, Jul 06, 2000 at 12:21:31PM +0200, Robert wrote:
> - each client gets separate database
>
> - one common database for all clients, each table has client_id field
> added

I'm not in your situation, but I can tell you that if you went with method
two, I would not recommend your services to my clients.

I work with many many law firms, and the possibility of their data getting
mixed up with someone elses (no matter how remote) would prevent me from
recommending that path.

While the maintenance is a bigger problem, I don't see it at nightmare
level. The biggest problem appears to occur when making table/index level
changes. I would think that it should be possible to write a wrapper for
those kind of updates that would make them across the board to each client.


--
Stand Fast,
    tjg.

Timothy Grant                         tjg@exceptionalminds.com
Chief Technology Officer              www.exceptionalminds.com
Red Hat Certified Engineer                      (503) 246-3630
Avalon Technology Group, Inc.               fax (503) 246-3124
>>>>>>>>>>>>Linux...Because crashing isn't normal<<<<<<<<<<<<<