Re: DB Design Advice

On Fri, Aug 21, 2009 at 12:50 AM,
stoneg64@excite.com<stoneg64@excite.com> wrote:
> Hey all,
>
> My company is designing a database in which we intend to store data for
> several customers.  We are trying to decide if,
>
> A:  we want to store all customer data in one set of tables with customer_id
> fields separating the data or,
> B:  storing each customers data in a separate schema.
>
> I'd like to get some opinions on the pros and cons of these methods
> concerning maintainability, scalability, and performance.

The schema approach works really well. In cases where you can do this,
I greatly prefer it over the build in table partitioning features.
Some tips:

*) You only have to define functions once.  As long as you don't
schema qualify tables in function definitions, you can have your
pl/sql and pl/pgsql functions 'float' over the schema.  Just be
prepared to regenerate the plans if you change the search path.

but,
*) Views must be added for each schema

*) Queries joining against multiple companies are a pain.  In cases
where you must do this, make views in the public schema with lots of
'UNION ALL'.

*) Make helper functions in dynamic pl/pgsql so you can do things like
apply ddl to multiple schemas and rig a 'grant all in schema' if
necessary.

*) If you are managing huge numbers of schemas, you can use
tablespaces to divide up the database into different filesystems.

merlin