Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

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

The 'questions' table looks like this:

QUESTIONS
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 = [@question.id] and option_id = [@question.option_1.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?

Cheers...

share|improve this question
    
Related: dba.stackexchange.com/q/239/2660 –  Nick Chammas Apr 24 '12 at 19:09
add comment

3 Answers

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
1  
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
add comment

Remember the biggest problem in making data redundant is keeping it in synch. If you have it two places and it doesn't match then your datbase has 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
add comment

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
add comment

Your Answer

 
discard

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.