db optimization - have a total field or query table?

Posted by Dorian Fife on Stack Overflow See other posts from Stack Overflow or by Dorian Fife
Published on 2010-12-25T07:54:45Z Indexed on 2010/12/25 8:54 UTC
Read the original article Hit count: 173

Filed under:

database-design

|

running-total

I have an app where users get points for actions they perform - either 1 point for an easy action or 2 for a difficult one. I wish to display to the user the total number of points he got in my app and the points obtained this week (since Monday at midnight).

I have a table that records all actions, along with their time and number of points.

I have two alternatives and I'm not sure which is better:

Every time the user sees the report perform a query and sum the points the user got
Add two fields to each user that records the number of points obtained so far (total and weekly). The weekly points value will be set to 0 every Monday at midnight.

The first option is easier, but I'm afraid that as I'll get many users and actions queries will take a long time.

The second option bares the risk of inconsistency between the table of actions and the summary values.

I'm very interested in what you think is the best alternative here.

Thanks, Dorian

© Stack Overflow or respective owner

Related posts about database-design

(Database Design - products attributes): What is better option for product attribute database design

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I new in database design. What is better option for product attribute database design for cms?(Please suggest other options also). option 1: 1 table products{ id product_name color price attribute_name1 attribute_value1 attribute_name2 attribute_value2 attribute_name3 attribute_value3 } option… >>> More
Book Review: Pro SQL Server 2008 Relational Database Design and Implementation

as seen on SQL Blog - Search for 'SQL Blog'
Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design. Surely we can and sometimes do cut corners and save on database design to get things done faster… >>> More
Advice on database design / SQL for retrieving data with chronological order

as seen on Stack Overflow - Search for 'Stack Overflow'
I am creating a database that will help keep track of which employees have been on a certain training course. I would like to get some guidance on the best way to design the database. Specifically, each employee must attend the training course each year and my database needs to keep a history of… >>> More
Fiscal year handling strategies in database design

as seen on Stack Overflow - Search for 'Stack Overflow'
By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years. What way of implementing this would you prefer, and why: Separate fiscal year data based on multiple… >>> More
Database design for summarized data

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a new table I'm going to add to a bunch of other summarized data, basically to take some of the load off by calculating weekly avgs. My question is whether I would be better off with one model over the other. One model with days of the week as a column with an additional column for price… >>> More

Related posts about running-total

Can somebody explain the running total and SQL self-join in this tutorial to me?

as seen on Stack Overflow - Search for 'Stack Overflow'
I was reading over the tutorial here: http://www.1keydata.com/sql/sql-running-totals.html and it all made sense until it suddenly got extremely ridiculously unbelievably complicated when it got to rank, median, running totals, etc. Can somebody explain in plain English how that query results in a… >>> More
Crystal Reports: global variable running total not displaying in header

as seen on Stack Overflow - Search for 'Stack Overflow'
Using Crystal Reports I'm trying to display the running total of a database field in the header where all the labels are. I've attempted to do this by placing the running total (RTversion) into a formula field with the following: Shared stringvar CurrentVers; CurrentVers := {#CurrentVers}; and… >>> More
Optimization of running total calculation in SQL for multiple values per join condition

as seen on Stack Overflow - Search for 'Stack Overflow'
I have the following table (test_table): date value --------------- d1 10.0 d1 20.0 d2 60.0 d2 10.0 d2 -20.0 d3 40.0 I calculate the running total as follows. I use the same query twice, because first I need to calculate the values for a specifi date, and afterwards… >>> More
Using the ASP.NET Cache to cache data in a Model or Business Object layer, without a dependency on System.Web in the layer - Part One.

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
ASP.NET applications can make use of the System.Web.Caching.Cache object to cache data and prevent repeated expensive calls to a database or other store. However, ideally an application should make use of caching at the point where data is retrieved from the database, which typically is inside a Business… >>> More
Using PLINQ to calculate and update values within the enclosure does not work

as seen on Stack Overflow - Search for 'Stack Overflow'
I recently needed to do a running total on a report. Where for each group, I order the rows and then calculate the running total based on the previous rows within the group. Aha! I thought, a perfect use case for PLINQ! However, when I wrote up the code, I got some strange behavior. The values I… >>> More