需要 MySQL 约束和派生值建议

发布于 2024-10-21 13:31:33 字数 359 浏览 7 评论 0原文

我最近开始使用数据库,尝试使用示例自学。

我目前正在尝试解决以下问题,这让我很困惑,我希望有人能提供一些线索。

数据库有4张表。摄影师、图片、竞赛和观众。这个概念是摄影师参加比赛。他们为每场比赛拍摄一张照片,并由观众评分。比赛的获胜者是获得最多积分的人。我设置了以下限制。

  1. 评分介于 0 - 5 之间
  2. 只有前 20 位观众可以投票
  3. 我不想存储摄影师的总评分,因为我想学习如何计算派生值。

对于数字 1 和2 - 我不确定如何显式创建此约束。 对于第三个,我不知道如何在数据库上表示派生值。 我正在使用 MySql

任何想法、建议将不胜感激!

此致

I have recently started playing with databases, trying to teach myself using examples.

I have the following problem that I'm currently trying to solve which confuses the hell out of me and I'm hoping that someone can shed some light.

The database has 4 tables. Photographers, Pictures, Competition and Viewers. The concept is that photographers take part in competitions. They shoot 1 picture for each competition and viewers rate it. The winner of the competition is the one who gets most points. I have put the following restrictions.

  1. Ratings are between 0 - 5
  2. Only the first 20 viewers can vote
  3. I don't want to store the total rating of the photographer as I want to learn how to calculate derived values.

For numbers 1 & 2 - I'm not sure how to explicitly create this constraint.
For number 3, I don't know how to represent derived values on the db.
I'm using MySql

Any thoughts, advice would be greatly appreciated!

Best Regards

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

寄离 2024-10-28 13:31:33

1]据我所知,你不能直接在MySQL中设置这样的约束。您可以使用允许值为 0-255 的 UNSIGNED TINYINT,但在将它们插入数据库之前,您必须先检查一下这些值是否正确。

2] 我是否理解正确,您希望每张图片最多只允许 20 票?

您要么必须将所有选票单独存储(然后检查已存在的选票数),要么只保存平均值和选票数。无论哪种方式,您都必须自己检查并确定是否允许用户投票。对于第一个选项:

SELECT COUNT(*) > 20 FROM votes
WHERE picture_id = '123'

对于第二个更简单的选项:

SELECT votes_count > 20 FROM pictures
WHERE id = '123'

3] 如果您对直接存储在图片数据库中的图片进行投票,则只需使用 SUM() 函数:

SELECT *, SUM(points) as total_points FROM photographers AS ph
LEFT JOIN pictures AS pic ON pic.photographer_id = ph.id
GROUP BY p.id, competition_id

我希望我在那里没有犯错误。如果您有更详细的问题,请直接提问,我会尽力填补漏洞;)

1] You can't set such constraint directly in MySQL as far as I know. you could use UNSIGNED TINYINT with allowed values of 0-255 but you have to check yourself if the values are ok before inserting them into the database.

2] Do I understand you correctly that you want to allow only a maximum of 20 votes per picture?

You'd either have to store all the votes in a separate (then check for the number of votes already present) or save just the average and number of votes. Either way, you have to check for this yourself and determine if you allow the user to vote or not. For the first option:

SELECT COUNT(*) > 20 FROM votes
WHERE picture_id = '123'

For the second even simpler:

SELECT votes_count > 20 FROM pictures
WHERE id = '123'

3] If you had your votes for the pictures stored directly in the pictures db, you simply use the SUM() function:

SELECT *, SUM(points) as total_points FROM photographers AS ph
LEFT JOIN pictures AS pic ON pic.photographer_id = ph.id
GROUP BY p.id, competition_id

I hope I made no mistakes there. If you have more detailed question, please just ask, I'll try to fill the holes ;)

绝不服输 2024-10-28 13:31:33

1) 看一下 enum 值,尽管 tinyint(1) unsigned 可能更快

1) Take a look at enum values, although tinyint(1) unsigned might be faster

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文