DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:user-agent:cc:references:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:x-virus-scanned;
	b=gfQD+JQ6Oqt43XsDuknoRS+LsNwaOP8Ps7sSqaV5NGbHDT52dUWgVhT00qHAODvGsdJeq1bm3NAwGzr1Zc1w0Sk7xWJPMxI1Sb5yNDaBHQobUB1vd8vY0YTAbZ6GPFUC3kg9Zjl43FkuSnHRNPeMZem2JX22H5y8qDsXoKuYECA=
From: Jorge Godoy <jgodoy@gmail.com>
To: Alban Hertroys <alban@magproductions.nl>
Subject: Re: [SQL] (Ab)Using schemas and inheritance
Date: Wed, 24 May 2006 10:26:05 -0300
User-Agent: KMail/1.9.1
Cc: "Jim C. Nasby" <jnasby@pervasive.com>,
 pgsql-general@postgresql.org
References: <200605231716.58784.jgodoy@gmail.com>
	<20060523232609.GP64371@pervasive.com>
	<447422D2.4060403@magproductions.nl>
In-Reply-To: <447422D2.4060403@magproductions.nl>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200605241026.07042.jgodoy@gmail.com>

Em Quarta 24 Maio 2006 06:09, Alban Hertroys escreveu:
>
> What about using updatable views instead of inheritence? You'd need your
> company_id back, but adding new companies or modifying table definitions
> could be a lot easier (as long as you don't need to update all of your
> views...).

Hi Alban.  Besides that update problem in my views -- that would force me to 
update the view and associated rules -- how would it behave with regards to 
performance?  From what I got reading 
http://www.varlena.com/GeneralBits/82.php all data would be on the same 
table, so if I have millions of records -- what is easy if I'll be recording 
each individual transaction -- either buying or selling -- for each company 
and I have 1000 companies, including hotels and other companies where we can 
have hundreds of entries per day... 

Some things are really important here:

	- performance for operations on an individual company --- it has to be
	  as fast as possible because this might be used by my client's clients. 

	- safety: if it becomes available externally, then one client could never
	  see other client's data.  This is a main concern.  If used only internally
	  it is desirable that only authorized employees view each client since all
	  of them doesn't need to access all clients even if working directly with
	  accounting.

	- easy to code on application side: other systems will be plugged to this
	  database.  The more we can do to avoid other people mistakes, the better.

	- easy to maintain database: if it is too painful, hacks will come and this 
	  is not a good plan before starting the project...


Thinking about the first item -- performance -- and by reading the manual, we 
got to table inheritance (data partitioning made easy ;-)).  Thinking about 
the second item -- safety -- and including search_paths we got to several 
schemas + access permissions.  The third and fourth items were a consequence 
of the first two decisions.

We haven't benchmarked anything yet, but from what is in the docs, this looked 
like a good approach.

Updateable views give me the part of second and also the third item but it 
seems to be missing on the first and last items...  On the other hand, if it 
solves problems with views and functions that I said I was having on the 
other thread then might become interesting...  But performance would still be 
a problem with millions of records (by law we're required to keep at least 5 
years of docs online for some docs, for other it is required to have the full 
company history... so getting to dozens of millions in 5 years is not all 
that hard...). 

Am I right or completely wrong? :-)

-- 
Jorge Godoy      <jgodoy@gmail.com>