I have a table with information about employees. Each empl receives a salary and I need to show the cumulated salaries over a period of time - for every employee.
so imagine this, every time you ignite the program you see a list of empls (100+) and their cumulated salaries.
Question: Should I add a column cumulatedSalary to the table (and every time an empl recieves a salary - add it to the comulted field) or simply to use the SQL aggregate function to sum up their monthly salary..
If you expect to read this data many more times that you update it, then overall denormalizing it is more time efficient. However, there's more to life than just time efficiency. The demormalized solution uses more space. It's harder to maintain, since it will require an update trigger. It's not clear that the time gain is worth these costs.
The usual development approach is to keep the data normalized, and only denormalize if you encounter a performance problem. In other words, to apply the usual advice about avoiding permature optimization.
If you do decide you want this kind of denormalization, see if your database supports materialized views. This might entail less maintainence than a trigger.
posted 15 years ago
thank you for your reply; but could you be more specific when you say: denormalizing.
simply put, would you go on an aggregate function or add extra field?
I agree with Loren that we can't give you an efficiency answer without more information. You should use the function unless it is demonstrated to be a performance problem. How often do you run the program? How critical is response time?
An extra field will cause updates to the table to take longer. This is another cost that needs to be evaluated.
As Loren says, everything is a trade-off. What you gain in performance, you pay for in extra disk space. You need to find the optimal solution, given your requirements and limitations. As Jeanne says, if you want us to help you find your optimal solution, then you need to supply us with details of your requirements and limitations.
Good Luck, Avi.
Nothing up my sleeve ... and ... presto! A tiny ad: