CODEWITHSUNDEEP
databasedatabase-design
Josh
Josh

Reputation: 1019

How to handle this DB schema?

Using SO as a prime example, let's say a question or answer to a question is deleted but garnered a few up-votes before it was deleted. I imagine these points are still awarded to the author (if they aren't, let's suppose they are), then how does SO keep an accurate reputation total?

Are the questions/answers actually not deleted from the DB itself, and perhaps have a status field that is processed and decides whether a question or answer is visible?

Or, they are, in fact, deleted and the reputation relies on the system being continuously accurate as each vote is counted and doesn't necessarily have a history of it (like a question that recorded the vote)

Upvotes: 0

Views: 69

Answers (1)

Joel Brown
Joel Brown

Reputation: 14418

SO uses a combination of soft and hard deletes, to the best of my knowledge. I can say for sure that I've lost reputation that was gained on questions deleted by either the poster or the moderator. That is not the point of your question, however, so...

If you want to be able to deduce an accurate total, especially if you want to be able to account for that total (the way SO lets you do by looking at your points history) then you need to keep transactional information, not a running total.

If you want to have referential integrity for the transactional log of points then you will need to use a soft-delete mechanism to hide questions that are "deleted".

If you don't keep the transactional log and you don't have soft delete-able questions to back up your transactional points log, then you won't be able to either recalculate or justify point totals. You'll also have a much harder time displaying a graph of points awarded over time and accumulated reputation over time. You could do these graphs by keeping a daily point snapshot, but that would be much more onerous and costly in terms of storage than just tracking up and down votes.

Upvotes: 1

Related Questions