Re: using schema's for data separation

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(at)myemma(dot)com>
software development
emma(r)