处理社交新闻网站上的赞成/反对票
我正在尝试从头开始制作一个类似 reddit 的网络应用程序。 我不知道如何存储赞成票和反对票。
我正在考虑创建一个名为“user_votes”的表 包含字段 'id'、'user_id'、'voted_link_id'、'up_or_down'
因此，每次用户投票时，基本上都会添加一行“谁对什么投票”。
我要插入一个新行，而不是仅添加 1 行，因为用户个人资料页面必须显示用户投票的链接列表。所以我需要跟踪每一张选票。但我感觉效率不高。
我不熟悉严重依赖数据库的Web应用程序。 请指导我。
聚苯乙烯 哪些列应该被索引？
I'm trying to make a reddit-like web application from scratch.
I'm not sure how to store the up and down votes.
I'm thinking about creating a table called 'user_votes'
with fields 'id', 'user_id', 'voted_link_id', 'up_or_down'
So it's basically adding a row for "who voted what on what" every time a user votes.
I'm inserting a new row instead of just adding 1, because the user profile page will have to show the list of links the user voted. So I need to keep track of every single votes. But I don't feel like it's efficient.
I'm not familiar with web applications heavily relying on DB.
Please guide me.
P.S.
Which columns should be indexed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
事实上你两者都想要。
您的表必须包含 Article_ID、User_ID、+1 或 -1。这正是您所说的原因。您需要将用户的投票显示为运行帐户。您还可以使用它来确保唯一性。
您现在必须考虑频率。文章的查看频率将高于投票频率。因此，您必须在 Vote 表上执行大量 SUM，从而生成完全相同的值。
相反，您应该在 Article 表上保留两个计数器：赞成票总数和反对票总数。两个的原因是，总和无关紧要，因为两个值位于同一行。其次，您可能想要实现一些像 SO 那样公开这些值的东西。 （除非你获得这么多积分（大约 1500），否则你无法看到这一点）。
您可能想要显示文章列表以及每篇文章的总分...您不想对一长串文章的投票表进行求和。您可能还希望允许人们对文章设置限制，“只显示超过 +10 的文章”。同样，您不想每次有人打开主页时都对投票表进行求和。
Actually you want both.
You must have the table with Article_ID, User_ID, +1 or -1. This is for exactly the reasons you've stated. You'll need to show a user's votes as a running account. You'll also us that to ensure uniqueness.
You now have to think about frequency. Articles will be viewed more frequently than voted on. Because of that, you'd have to do a lot of SUMs on the Vote table that result in the exact same values being produced.
Instead you should keep two counters on the Article table: a total of upvotes and a total of downvotes. The reason for two is that, the sum is inconsequential since the two values are on the same row. Second you may want to implement something that will expose those values like SO does. (You can't see that until you get so many points (about 1500)).
You may want to show a list of articles and the total points each has... you don't want to SUM over the votes table for a long list of articles. You also may want to allow people to set limits on articles, "only show me over +10". Again, you don't want to sum over the votes table every time someone opens their home page.
首先，很高兴您喜欢挑战！
其次，我建议不要优化应用程序，除非你能证明你需要，尤其是。在数据库设计中。这是一个很好的平衡，但从广义上讲，我会首先选择“纯”关系设计，并且仅在确实需要时才引入重复数据。
因此，我首先记录每次投票，并即时计算总分。这样，您就不需要处理当您的“计算得分”与投票不同步时发生的情况（例如，因为应用程序在写入投票和更新总数之间失败）。
一旦设计可行，就找到一种方法来测试性能，使用大量数据和大量访问者。 DBMonster 和 Apache JMeter 可能是这里使用的工具。
当您遇到性能问题时，请首先尝试通过查询优化和索引来解决它们 - 充分利用 Stack Exchange！另请参阅应用程序级缓存。
当您真的、真的无法从应用程序中榨取更多性能时，我会开始按照斯蒂芬妮建议的方式预先计算分数。
Firstly, it's good you like the challenge!
Secondly, I'd suggest not optimizing the application until you can prove you need to, esp. in the database design. It's a fine balance, but broadly speaking, I'd go for a "pure" relational design first, and only introduce duplicated data when you really, really have to.
So, I'd start by recording each vote, and calculating the total score on the fly. This way, you don't need to deal with what happens when your "calculated score" gets out of sync with the votes (e.g. because the application failed between writing the vote and updating the total).
Once you have that design working, find a way to test performance, with huge volumes of data, and large volumes of visitors. DBMonster and Apache JMeter may be the tools to use here.
When you bump into performance issues, try to solve them through query optimization and indexing first - use Stack Exchange to it's fullest! Also look at application level caching.
When you really, really can't squeeze any more performance out of the application, I'd start pre-calculating the scores in the way Stephanie suggests.
如果您希望能够跟踪所有投票的去向并确保没有人可以对同一篇文章进行多次投票，这可能是最好的设计。如果索引正确，一张表即使有几百万行也没什么。尽管您需要明智地在应用程序中进行缓存以提高性能。
This is probably about the best design if you want to be able to track where all the votes went and make sure nobody can vote multiple times on the same article. Properly indexed, a table even with a couple of million rows is nothing. Though you will want to be wise with caching in your application to help performance.