储存“投票”在数据库中 [英] Storing "votes" in a database

问题描述

我在写一个内部网应用程序，其中一个功能大致类似于内容投票 - 与SO，亚马逊和许多其他网站不同。

假设每个可投票的内容都有唯一的ID，并且每个用户（他们被验证）都有一个唯一的ID，最简单的方法似乎是有一个投票表...

  ContentID int 
 UserID int 
 VoteValue int 
  

但是这会为每次投票创建一行 - 包含数百万个内容和成千上万的用户，该表将是巨大的巨大。这是最好的方法吗？我的意思是，如果一个int需要4个字节，每行需要12个字节。如果一百万件内容得到一百票，那是400MB +在存储，是吗？看起来像很多:)。即使VoteValue是一个tinyint（这可能很好），只有1个字节，在表中仍然是几百兆。我是指sheesh。

有更聪明的方式吗？我应该将这个投票表存储在单独的数据库中（忽略潜在的数据完整性问题），以便在存储和性能方面将其从主数据中分割出来。

（我知道，在今天的世界400MB不是一吨 - 但它似乎只是一个LOT只是为了存储投票，是吗？）

解决方案

就个人而言，只要你有良好的指标，你就是正确的方式。根据您的使用情况，为了性能，您可以尝试避免通过存储辅助计数信息来打击投票表，但总的来说，如果您必须跟踪WHO已投票的事情，您需要按照您列出的方式进行。

我不会打扰移动到另一个数据库，如果你是真正关心的SQL Server中，你可以创建一个单独的文件组来保存它.....但很可能不是必要的。 p>

I'm writing what will be an intranet application, and one of its features is roughly analogous to content voting - not unlike what SO, Amazon, and many other sites do.

Assuming each votable piece of content has a unique ID, and each user (they're authenticated) has a unique ID, the easiest way would seem to be to have a "votes" table...

ContentID int
UserID int
VoteValue int

But this creates one row per vote - with millions of pieces of content and tens of thousands of users, that table's gonna be huge huge huge. Is this the best way to do it? I mean, if an int takes 4 bytes, each row takes 12 bytes. If a million pieces of content get a hundred votes, that's 400MB+ in storage, yeah? Seems... like a lot :). Even if the VoteValue is a tinyint (which is probably fine) and only 1 byte, that's still a couple hundred megabytes in the table. I mean sheesh.

Is there a smarter way? Should I store this "votes" table in a separate database (ignoring potential data integrity issues) to partition it from the "main" data in terms of storage and performance?

(I do realize that in today's world 400MB ain't a ton - but it seems like a LOT just to store votes, yeah?)

解决方案

Personally as long as you have good indexes in place, you are going about it the right way. Depending on your usage, for performance you might try to avoid hitting the votes table by storing secondary count information, but overall if you must track WHO has voted something, you need to do it in the way you have listed.

I wouldn't bother moving to another database, if you are REALLY concerned in SQL Server you could create a separate filegroup to hold it.....but most likely not necessary.

这篇关于储存“投票”在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！