Database: Sybase ASE 15.0.3
Tables:
authors(int author_id, varchar(20) author_name)
books(int author_id,int number_of_pages)
I need to add a new field to the table "authors" for total_number_of_pages (the sum of the number of pages for all the books written by the author).
Can this be done by a (possibly materialized) computed field?
I don't like the idea of a view, I really would prefer the field to be in the table authors, if possible.
With the proper index (on author_id) the View solution should actually be quite quick. It's just a simple aggregation. Unless you have hundreds or thousands of books per author, this shouldn't be a problem at all. (I expect the average number of books per author to be under or around 10?)
If you really do need to optimise this and store a cached value, I'd recommend a trigger.
As books are added, deleted or updated, in the books table, you alter the value in the authors table appropriately. It means the least amount of recalculation possible.
The downside of this is increased complexity and interdependence within your design.