snacktime wrote:
I'm re evaluating a few design choices I made a while back, and one
that keeps coming to the forefront is data separation. We store
sensitive information for clients. A database for each client isn't
really workable, or at least I've never though of a way to make it
workable, as we have several thousand clients and the databases all
have to be accessed through a limited number of web applications where
performance is important and things like persistant connections are a
must. I've always been paranoid about a programmer error in an
application resulting in data from multiple clients getting mixed
together. Right now we create a schema for each client, with each
schema having the same tables. The connections to the database are
from an unprivileged user, and everything goes through functions that
run at the necessary privileges. We us set_search_path to
public,user. User data is in schema user and the functions are in the
public schema. Every table has a client_id column.
This has worked well so far but it's a real pain to manage and as we
ramp up I'm not sure it's going to scale that well. So anyways my
questions is this. Am I being too paranoid about putting all the data
into one set of tables in a common schema? For thousands of clients
what would you do?
Hi, where I work we have similar issues wherein we have thousands of
clients who each have large amounts of the same kind of data that needs
to be kept separate. What we've done is to use table inheritance. So,
we have a group of base account data tables and whenever a new account
is added they get a set of tables that inherit from these base tables.
This works well in that whenever we need a global schema change to any
of these tables we can just alter that pertinent base table and the
change will cascade down to the child tables. In addition, we can
customize individual accounts' tables however we may need without
worrying about screwing up other accounts' data.
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
