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

Jorge Godoy wrote:
> 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.

I suppose inherited tables would be faster. Both at read and write
operations.

However, I think that partitioning/clustering the table over an index on
company_id would help a lot. Also, considering you need to keep a lot of
old data around, you may be able to split your data in archived and
recent data, keeping your indices small (partial indices maybe).

> - 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.

Safety would be about equal to your inheritence solution. You can GRANT
rights on the views, that can be different from the access rights on the
base tables. The views must evidently have access to your base tables.

> - 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.

Views might be a bit easier there, as you can add derived data to your
results (like first name, infix and surname concatenated, dates in a
format specific for a companys' locale, etc.).

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

This was my main reason to suggest views. For select you could probably
get away with SELECT * FROM table WHERE company_id = x; (Though SELECT *
is being frownde upon).

The update/insert/delete parts are also all rather similar. You may be
able to generate REPLACE VIEW statements if the base table layout
changes. You'd probably need to DROP and re-CREATE them, but being views
your data is not at risc. Nothing physically changes in your tables.
Hmm... Almost forgot about the RULEs that make them updatable... Those'd
need updating too, of course...

With inherited tables you would need a way to dump and restore their
contents, or do a whole lot of ALTER TABLE statements. You'd be
physically altering the tables that contain your data; there's more than
zero risc involved. You can't rollback DDL statements...

OTOH, with inherited tables you can have company-tables that differ from
the base tables for a specific company; with views that'd be... difficult.

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

Neither did I benchmark updatable views; haven't had much chance to use
them yet. No, I don't have a lot of experience with them, but I know
some people on this list must have.

> 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...).

In my experience, having the right indexes on your tables/views helps a
LOT. Millions of records need not be a problem if you know what you will
be querying for.

> Am I right or completely wrong? :-)

Have you considered downtime if a schema change is necessary? What would
cause the most; tables or views?
I'm pretty sure VIEWs will be faster if you need to DROP/CREATE the
tables, as you'll have to move your data around not to lose it... Of
course, you'd need to do the same for your base tables, that contain
_all_ your data in the updatable view case...

Well, only more things to consider choosing between, I'm afraid.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //