Re: [SQL] (Ab)Using schemas and inheritance

Moving to -general, where it's more likely that others will have input.

On Tue, May 23, 2006 at 05:16:54PM -0300, Jorge Godoy wrote:
> I'm modelling an application that will have data -- financial data, human
> resources, etc. -- for several hundred (even thousands) of companies. This
> is for an accounting office.
>
> I could put some kind of "company_id" column in all of my tables to separate
> data in a more standard way, I could create a separate schema for each client
> and then create all needed tables in there (could I? I'd be abusing schemas
> here and this is part of my doubt) and, finally, I could create a "base"
> schema, define all my standard tables and create an individual schema for
> each client where I'd inherit from those base.tables.
>
> This would allow me to separate all information with a "SET search_path TO
> company" without having to make the restriction "by hand" (on "company_id",
> for example). It would also allow me to view some complete statistics
> grouping all clients by SELECTing data from the base schema. We're testing
> views and functions to see how they behave with inherited tables and changes
> on "search_path", and it looks like we can do that for, at least, a small
> number of schemas.
>
> Of course, this has implications on permissions as well, so there will be a
> large number of groups -- probably at least one per schema + some common
> groups -- and roles as well...
>
>
> Is this a good idea? Would this be too bad, performance-wise, if I had
> thousands of schemas to use like that? Any advice on better approaches? Any
> expected problems?

One issue is that you'll probably be breaking new ground here a bit; I
suspect there's very few people that are using more than a handful of
schemas. Shouldn't pose any issues, but you never know; although any
issues you do run into should only be performance problems.

Another consideration is that the free space map doesn't care too much
for tracking space info on tons of small tables.

Perhaps the biggest issue is: what happens when you need to do DDL? If
you have 1000 schemas that should be identical, you'll need to perform
any DDL 1000 times.

But as you point out, there's some interesting advantages to using
schemas like this.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461