创建可扩展的数据库模式来存储高尔夫分数 [英] Create a scalable database schema for storing golf scores

问题描述

我正在设计一个数据库来存储我的所有朋友和我的高尔夫分数。你可能知道，高尔夫球得分是由18洞的个人成绩组成。我可以想到设计架构的两种方法：

I am trying to design a database to store all my friends and my golf scores. As you probably know, a golf score is comprised of 18 hole individual scores. I can think of two ways to design the schema:

1. 创建一个表，每个孔有一列（例如h1到h18），该表具有引用其他表的FK player_id，round_id和course_id。它有一个总列，它是列h1到h18的总和。如果我更改一个洞分数，我将需要手动更新总列。

1. Create a table that has one column for each hole (e.g. h1 to h18), the table has FK player_id, round_id and course_id that referenced other tables. It has a total column, which is the sum of columns h1 to h18. If I change a hole score, I will need to manually update total column.

创建一个具有孔分数列，孔索引列，player_id，course_id和round_id列。为了获得一轮的总分，我需要对round_id，player_id进行SUM查询。

Create a table that has a column for hole score, a column for hole index, a column for player_id, course_id, and round_id. To get the total score for a round, I will need to do a SUM query on round_id, player_id.

现在，数据库可能会存储少于20人的分数，所以任何一种方法都应该是好的。但是如果我想为20,000人存储分数，哪种方法更具可扩展性？

For now, the database would probably store scores from less than 20 people, so either approach should be fine. But what if I want to store scores for 20,000 people, which approach is more scalable?

我正在使用MySQL 5，使用PHP5。谢谢。

I am using MySQL 5, with PHP5. Thanks.

更新>查询示例：
1.阅读所有玩家的9/18分，并建立记分卡。
2.基本统计数据，例如找到最后X轮的玩家的最低/平均/最高总分。
3.更高级的统计数据，如上次X轮的任何一个洞的平均分数。

Update> Examples of queries: 1. Reading the 9/18 scores from all players for a round and building a scorecard. 2. Basic stats like finding the lowest/average/highest total scores for a player for the last X rounds. 3. More advanced stats like average score of any hole for the last X rounds.

推荐答案

即使第二次方法是我最喜欢的方法，因为高尔夫球规则可能不会很频繁地变化，所以第一种设计可能在这种情况下起作用。如果您选择了一个，则可以使用触发器计算总和列。

Even though the second approach is my preferred one, since the rules of Golf probably won't change very often, the first design could work in this case. The sum column, if you would choose to have one, could be computed with a trigger.

但是，当您和您的朋友决定使用时，您必须处理这种情况去一个9洞或36洞的一轮。还有一个问题，如何存储额外的每个孔信息，万一有什么。具有单独表格的模式，具有玩家玩的每个洞的一行，将更容易维护。

However, you would have to handle the situation when you and your friends decide to go a 9-hole or a 36-hole round. There is also the issue with how to store additional per-hole information, in case there are any. A schema with a separate table with a row for each hole played by a player will be much easier to maintain.

与往常一样，最佳解决方案取决于您将如何使用，更新和查询数据。如果您的唯一用例涉及显示总分和高分，则第一种方法可能会起作用。

As always, the best solution depends on how you are going to use, update and query the data. If your only use case involves displaying total scores and high scores, the first approach could work.

一旦用例处理与特定孔相关的信息，您的应用程序代码和/或SQL将难以编写和维护。例如获得所有玩过的特定洞穴的平均分数。

As soon as a use case is to deal with information related to a particular hole, your application code and/or SQL will be harder to write and maintain. Take for instance getting the average score for a particular hole for all games played.

无论如何，我建议您使用第二种替代方法，并在单独的表。在同一张表中获得所有分数的表现好处可能不会超过维护它并为其撰写查询的麻烦。

In any case I would suggest going with the second alternative and store per-hole scores in a separate table. The performance benefits of having all scores in the same table will probably not outweigh the troubles with maintaining it and writing queries towards it.

这篇关于创建可扩展的数据库模式来存储高尔夫分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！