CODEWITHSUNDEEP
databasedatabase-design
tobiv
tobiv

Reputation: 842

Should I write total values to the database or calculate them when needed?

I have a number of tables which are in a cascading relation, i.e. projects references clients, and tasks references projects. There are certain columns in a task which will need to be added to a total in the referenced project.

Should I have an extra total column in projects, and update its value each time there is a change in tasks? Or should I calculate the total whenever it is needed at output? What if I need a total of the totals for a client?

Upvotes: 1

Views: 99

Answers (1)

Abe Miessler
Abe Miessler

Reputation: 85056

I don't think a general answer can be given to this question. If you have a need for totals at the project level you will need to evaluate if it's worth creating a column just for that.

It could probably be done fairly easily with a calculated column.

Keep in mind that this will take up more space on your db and possibly slow down Insert/Update/Delete operations. On the other hand it would also potentially speed up certain select operations.

You will need to take a look at what your needs are and decide if these trade offs are worth it.

Personally, if I wasn't seeing performance problems and the SQL wasn't too ugly I would probably just calculate when needed.

Upvotes: 2

Related Questions