Thread: DB Design Advice

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.

I appreciate all the help.

Thanks,
Ted

---

Handyman Franchises. Click Here. Handyman Franchise Click Here For More Information

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.

IMHO, that would depend on what this data is and how you use it.    Is
this your company's data on your business with these customers, like
AR/AP transactions and so forth?  Or is this data you're storing for
these companies, data thats really 'theirs', and that won't be used
together, such as their websites that you host ?

>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.
>
>MHO, that would depend on what this data is and how you use it.

Yeah, I figured you might say that.

>Id this your company's data on your business with these customers, like
>AR/AP transactions and so forth? Or is this data you're storing for
>these companies, data thats really 'theirs', and that won't be used
>together, such as their websites that you host ?

I'd say that the data is more 'theirs' then ours. Customers' data shouldn't be used together but we may occasionally compare customer data. I'll also add that each customer should have a fairly significant amount of data.

Thanks again for the help.
Ted

---

Click here for to find products that will help grow your small business. Small Business Tools Click Here For More Information

"stoneg64@excite.com" <stoneg64@excite.com> wrote:
>
>
> >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.
> >
> >MHO, that would depend on what this data is and how you use it.
>
> Yeah, I figured you might say that.
>
> >Id this your company's data on your business with these customers, like
> >AR/AP transactions and so forth? Or is this data you're storing for
> >these companies, data thats really 'theirs', and that won't be used
> >together, such as their websites that you host ?
>
> I'd say that the data is more 'theirs' then ours.  Customers' data shouldn't be used together but we may occasionally
comparecustomer data.  I'll also add that each customer should have a fairly significant amount of data. 

If you're concerned about future-proofing your design, consider the fact
that it will be much easier to break the system apart if it outgrows a
single server and needs part of it to be moved to a separate server, if
the data is naturally separate (i.e., in different schemas, or even
different databases)

--
Bill Moran
http://www.potentialtech.com

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