Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.

I have 2 SQL tables, as such:

vote_id (PK)
question_id (FK)
user_id (FK)
option_id (FK) <The option the user voted for>

The 'questions' table looks like this:

question_id (PK)
option_1 (FK --> Options)
option_2 (FK --> Options)

The problem I have is that I very often have to retrieve the sum of all option_1 votes (or option_2 votes) for a question. This is currently done by selecting count where question_id = [] and option_id = []"

I'm guessing it'd be a heckuva lot quicker to just add option_1_votes and option_2_votes columns to the "questions" table and increment them each time a vote is added. But ultimately, that's redundant data.

So, as someone who's pretty clueless re: database design princpiples, what's the rule of thumb here? Would a top DBA just add in the columns, or try some other solution?


share|improve this question
Related: – Nick Chammas Apr 24 '12 at 19:09
up vote 6 down vote accepted

Generally, I don't add redundant columns unless I really need too.

Running a COUNT over a set of data is quite efficient in any RDBMS.

Consider this is a read over indexed (hopefully) cached data to get the count will beat the the 2nd write in to maintain the denormlaised column. This write requires more resources/locking/longer transaction etc which impact reads more

If performance becomes an issue over time, then you can pre-calculate the COUNT more efficiently using an indexed (aka materialised) view

share|improve this answer
Ahh - cheers gbn. Perhaps I'm pre-empting a problem that won't actually exist. I guess I'll profile the queries with larger data sets and see if it's an issue. – PlankTon Apr 20 '12 at 7:36

You could also add one (or more) trigger-filled column to pre-calculated the count of the most needed aggregated options...

share|improve this answer

Remember, the biggest problem in making data redundant is keeping it in sync. If you have it in two places and it doesn't match then your database has garbage ()or is now garbage) and you can't trust any of the data in it.

share|improve this answer
In general, I agree, but @PlankTon is clearly talking about adding summary columns to one table. There's an obvious source-derived relationship between the votes and the count of votes. If they disagree, the count is wrong and should be regenerated. – Daniel Lyons Apr 25 '12 at 7:14

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.