Re: using schema's for data separation

On 29/9/2006 15:29, "snacktime" <snacktime(at)gmail(dot)com> wrote:

> 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?

I would think of having a client table with their id as a permanent part of
the where clause so that you can't work without specifying which client you
are working for at the time. Not sure if a trigger would be able to ensure
you can't add, update or delete unless the clientID is included (pretty sure
you don't get to see the SQL only the results).
You may need to write a plugin or custom mod to get 100% certainty that a
statement can't be run without the clientID included in the search.

It would have to be an easier solution than continually updating thousands
of schemas to keep them in sync.

Thinking about it - it should only be a small source change to the sql
parser to stop it from running a statement that didn't include clientID in
the where clause.
A small change that is easy to add again to new versions as they are
released.

--

Shane Ambler
Postgres(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz