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

本文是小编为大家收集整理的关于在数据库中存储 "投票"。的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在写一个Intranet应用程序,其功能之一大致类似于内容投票 - 与Soe,Amazon和许多其他网站相同.

假设每个可提供的投票内容都有一个唯一的ID,并且每个用户(他们被认证)都有一个唯一的ID,最简单的方法似乎是具有"投票"表...

ContentID int
UserID int
VoteValue int

,但这每票创造了一排 - 拥有数百万个内容和数万用户,该表将是巨大的巨大.这是最好的方法吗?我的意思是,如果一个int取4个字节,每行都采用12个字节.如果一百万个内容获得一百票,那就是400MB+的存储空间,是吗?似乎...很多:).即使投票值是一个微小的(可能很好),只有1个字节,桌上仍然是几百个兆字节.我的意思是Sheesh.

有更聪明的方法吗?我应该将此"投票"表存储在单独的数据库(忽略潜在数据完整性问题)中,以从存储和性能方面将其从"主要"数据分开?

?

(我确实意识到,在当今的世界400MB中不是一吨 - 但是似乎只是为了存放选票,是吗?)

)

推荐答案

亲自,只要您拥有良好的索引,就可以正确地进行操作.根据您的使用情况,为了性能,您可能会尝试通过存储二级计数信息来避免击中投票表,但是总的来说,如果您必须跟踪谁投票了,则需要按照您列出的方式进行操作.

我不会打扰搬到另一个数据库,如果您真的很关心SQL Server,则可以创建一个单独的文件组来容纳它.....但很可能不是必需的.

其他推荐答案

好吧,是的,但是您需要查看更大的情况.带有一百万个内容:

(内容的大小)>>(投票的大小):其中" >>"表示"更大".

如果您有一百万个内容,那么这可能是数据的thabyte,而该数据的票数为400MB.大事吗?

我还会添加,如果您担心可伸缩性,请查看此博客:

http://highscalability.com/

其他推荐答案

如果您需要跟踪用户是否投票赞成特定项目,并且如果有不同的投票值(例如,1星到5星),那么这差不多是紧凑的.

不要忘记,对于明智的访问速度,您需要索引数据(可能是两个索引 - 一个以contentid为领先列,一个用UserId作为领先列).

您需要确定是否有理由不与其他表分开存储表.这意味着您使用的dbms的含义 - 对于Informix,该表将位于同一数据库中,但存储在不同的 dbspace 中,并且您可能将索引存储在其他两个不同的dbspace中.

本文地址:https://www.itbaoku.cn/post/597737.html

问题描述

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.

其他推荐答案

Well, yes but you need to look at the bigger picture. With a million pieces of CONTENT:

(Size of Content) >> (Size of Votes) : where ">>" means "much greater."

If you have a million pieces of content then that might be a terabyte of data where as the votes are 400MB. Big deal right?

I would also add, if you are worried about scalability, check out this blog:

http://highscalability.com/

其他推荐答案

If you need to track whether a user has voted for a particular item, and if there are different values of vote (so 1 star to 5 stars, for example), then this is about as compact as it gets.

Don't forget that for sensible access speeds, you'll need to index the data (two indexes, probably - one with ContentID as the leading column, one with userID as the leading column).

You'll need to decide whether there is a reason not to store the table separately from other tables. What this means depends on the DBMS you use - with Informix, the table would be in the same database but stored in a different dbspace, and you might have the indexes stored in two other different dbspaces.