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

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>