From: Jorge Godoy <jgodoy@gmail.com>
To: pgsql-sql@postgresql.org
Subject: (Ab)Using schemas and inheritance
Date: Tue, 23 May 2006 17:16:54 -0300
Message-ID: <200605231716.58784.jgodoy@gmail.com> (raw)
Hi!
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?
TIA,
--
Jorge Godoy <jgodoy@gmail.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jgodoy@gmail.com
Subject: Re: (Ab)Using schemas and inheritance
In-Reply-To: <200605231716.58784.jgodoy@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox