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@xxxxxxxxx>
