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@xxxxxxxxxxxxxxxxx
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 //
