论坛线程投票/视图的数据库架构,以及递增和显示视图数量的策略
如果现在重要的话我正在使用 MySQL/MyISAM 但我愿意使用 PostgreSQL。我也愿意使用 memcached。
考虑一个用于存储论坛主题的表:
id forum_name post_date
1 Hey! 2009-01-01 12:00:00
- 存储与主题相关的实体(例如投票、视图和计数器)的最佳实践是什么?
我应该..
创建一个单独的表,例如
id thread_id views
1 1 532
或将其保留为初始线程表中的一列?
id forum_name post_date views
1 Hey! 2009-01-01 12:00:00 532
另一个相关的花絮,显示和增加页面浏览量的实用解决方案是什么?我读了这个线程并且它似乎我可以只缓存一个值一段时间,我对增量部分不太清楚 - 也许就像将值存储在某个地方的平面文件中,然后定期使用 cronjobs 每小时左右更新数据库论坛视图?
编辑: 需要澄清的是,投票就像每个线程一票一样,并且可能会发生逆转。别介意我关于计数器的意思。
If it matters as of now I'm using
MySQL/MyISAM but I'm open to using
PostgreSQL. I'm also open to using memcached.
Consider a table that's used to store forum threads:
id forum_name post_date
1 Hey! 2009-01-01 12:00:00
- What's the best practice of storing thread-related entities such as votes, views, and counters?
Should I..
Create a separate table such as
id thread_id views
1 1 532
Or keep it as a column in the initial threads table?
id forum_name post_date views
1 Hey! 2009-01-01 12:00:00 532
Another related tidbit, what's the practical solution for displaying and incrementing page views? I read this thread and it seems like I could just cache a value for a certain time, I wasn't exactly clear on the incrementing part - perhaps something like storing the values in flat files somewhere, then periodically with cronjobs update the database forum views every hour or so?
EDIT:
To clarify, voting IS like SO with one vote per thread and there can be reversals. Nevermind what I meant about counters.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
投票
我建议除了线程表之外还定义两个表 - VOTE_CODES 和 THREAD_VOTES。乍一看,它可能看起来过于标准化,但该格式允许您更改投票值,而无需进行重大 DML 更改。
VOTE_CODES
表vote_code
,主键,IE:up、downvote_cast_value
--归因于向上/向下投票的值vote_caster_value
--可选,如果你想保持 SO 的负面投票风格影响施法者。THREAD_VOTES
表thread_id
user_id
vote_code
THREAD_VOTES
中的所有列都是主键- 这将确保给定用户和线程的行数只能与投票代码一样多。假设只有两个代码,这将支持逆转投票的能力,因为只能有两个记录 - 一个具有任一代码。视图
我建议存储:
以上所有内容都是主键。您的表将快速填充,但它将使您能够在视图中创建计算列以获得更准确的报告。
Voting
I suggest defining two tables in addition to the thread table -
VOTE_CODES
andTHREAD_VOTES
. At a glance, it might appear over normalized but the format will allow you to change vote value without needing major DML changes.VOTE_CODES
tablevote_code
, primary key, IE: up, downvote_cast_value
--value attributed to up/down votevote_caster_value
--optional, if you want to keep SO's style of the negative vote impacting the caster.THREAD_VOTES
tablethread_id
user_id
vote_code
All of the columns in
THREAD_VOTES
are the primary key - that will ensure that there can only be as many rows for a given user and thread as there are vote codes. Assuming only two codes, this would support the ability to reverse a vote because there could only be two records - one with either code.Views
I would suggest storing the:
All of the above are the primary key. Your table will populate fast, but it will give you the ability to create a computed column in a view for more accurate reporting.
平面文件显然是一个坏主意,因为您需要实现锁定(数据库已经这样做了,并且该代码中的错误较少)。
关系数据库设计更多的是一门艺术而不是一门科学:你可以拥有它
,它不会更多也不会更少“正确”,所以
这实际上取决于你。
我会说:首先从最简单的事情开始,如果您发现有必要(例如出于性能原因),请将其变得更复杂。 IOW:将
views COUNTER
属性放入threads
中。如果事实证明流量会损害性能(threads.views
属性上的更新过多意味着 dbms 必须在其他属性中的其他不可变数据周围进行洗牌),您始终可以将表分成两部分,并将其替换为连接它们的视图。瞧,不可变(或很少改变)的数据与易失性数据分开,接口保持不变。当然,选择 PostgreSQL。上面显示的代码在该dbms中是有效的,只需添加这些:
编辑来反驳OMG Ponies的评论:当然它是安全的。
要么成功,要么退出。
编辑 2 添加对投票方面的考虑
假设规范是:用户可以对某个线程进行向上 (+1) 或向下 (-1) 投票,即他或她对给定的投票的总和线程不能超过|1|,并且历史是无关的。用户可以对一个线程进行投票,然后将其投票重置为“不投票”,然后再次投票为“否决”,等等。
在 MySQL 中,您可能会
遗憾的是,PostgreSQL 还没有构建这样的功能中,因此您需要使用惯用的用户级实现
flat files are obviously a bad idea, because you'd need to implement locking (the db already does that, and there's fewer bugs in that code).
relational database design is more of an art rather than a science: you can have
and it'll be no more and no less "correct" than
so it's really up to you.
i'd say: go with the simplest thing first, make it more complicated if you find it's necessary (e. g. for performance reasons). IOW: put the
views COUNTER
attribute inthreads
. if it turns out the trafic is hurting performance (too many updates on thethreads.views
attribute mean the dbms must shuffle around otherwise immutable data in the other attributes), you can always split the table up in two, and replace it with a view that joins them. voila, immutable (or rarely changing) data separated from the volatile data, interface remains the same.of course, go with PostgreSQL. the above shown code is valid in that dbms, just add these:
edit to refute the comment by OMG Ponies: of course it's safe.
either succeeds or bails out.
edit 2 to add consideration for the voting aspect
let's say the spec is: a user may vote a thread up (+1) or down (-1), the sum of his or her votes on a given thread may not exceed |1|, and the history is irrelevant. iow a user may vote a thread up, then down to reset their vote to "no vote", then down again to "voted down", etc.
in MySQL, you could
alas, PostgreSQL doesn't (yet) have such functionality built in, so you'd need to use the idiomatic user-level implementation