DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:user-agent:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:x-virus-scanned;
	b=XjJNHBOwEOoaCnHaFw6yyELpwaxPFmiK2zpTH+3BBw/8Xsix8H3tJNfs+A9nrTeH8o1b7XE+O6j9VKlttUx/Orfl8O3CIHzb72L3J0yTzhDhrbeG4RHJYKDZnQHnNdrJicjyOWBrcM9KS3OhE9R8CKJcq6j62/8mySb4SLuTNWY=
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
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200605231716.58784.jgodoy@gmail.com>


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>