Does it make sense too have a column dependent on the total number of row in another table too improve speed?

user_17884919 0
Viewed 15

I have an application that requires the total number of interactions.

I have two tables. TABLE1 has a total column. TABLE2 has all of the interactions (columns) that update frequently. It has a column like didInteract. It is a 1:M relationship between TABLE1 and TABLE2.

Because my application uses the total interactions from TABLE2 if didInteract is true. I added the column total to TABLE1 so I wouldn't have to query all rows that match my criteria which could be costly. Therefore if a user interacts it performs two operations in the database. First, it creates a new interaction in TABLE2 if not already created and it increments the total interaction in TABLE1.

Does this logic make sense to do, or should I query TABLE2 to get the total (even if it may take a little longer) and remove the total column from TABLE1? Not sure if this passes 2NF although to me it sounds like an exception.

mysql
1 Answers

Technically what you describe is denormalization. There's a risk of data anomalies with any denormalization. For example, if you add an interaction but forget to increment TABLE1.total, then the total will be inaccurate. But clients who query that total won't know that it's inaccurate unless they double-check by querying the aggregate count from TABLE2. If that double-check is necessary to be sure, then there's no point in storing the total.

There are legitimate cases where denormalization is helpful. If you can depend on it being accurate, or if you don't care if it's inaccurate from time to time, and you periodically re-initialize the total from the count of TABLE2, then it could be good enough.

There's also the question of whether the slowness of querying TABLE2 directly is actually important. It's true that it's slower than querying the total as a precalculated count, but is the difference great enough that it makes your application fail its SLA?

These are tradeoffs, and which strategy is right for your app is up to you.

Bill_Karwin_208600
Related