高尔夫分数的mySQL数据库方案

I am designing a database to store golf scores and other statistics about each hole or round, but am having difficulty in knowing which method will be most efficient. My question centers around structuring the tables for courses and rounds. Here's two options, although I'm not sure if there's a better third. I'm using mySQL 5.1.63 and PHP 5.2.17. Tables in CAPS for readability.

我正在设计一个数据库来存储高尔夫球得分和其他每个洞或圆的统计数据，但我很难知道哪种方法最有效。我的问题围绕为课程和轮次构建表格。这里有两个选项，虽然我不确定是否有更好的第三个选项。我正在使用mySQL 5.1.63和PHP 5.2.17。 CAPS中的表格是为了便于阅读。

1. Have one large tables that stores all of the course information, since this information will only be listed once in the database (~100 columns...18 holes * 4 attributes each hole, in addition to course_name, location, phone, etc.)
   • COURSE: course_id, par_1, par_2, ...par_18, distance_1, distance_2, ...distance_18, etc.
   • 课程：course_id，par_1，par_2，... par_18，distance_1，distance_2，... distance_18等。
2. 有一个存储所有课程信息的大表，因为这些信息只会在数据库中列出一次（~100列...每个洞18个洞* 4个属性，除了course_name，位置，电话等）课程：course_id，par_1，par_2，... par_18，distance_1，distance_2，... distance_18等。
3. Split the course information into tables, connecting certain tables with course_id at the proper time
   • COURSE_PAR: course_id, par_1, par_2, ...par_18
   • COURSE_PAR：course_id，par_1，par_2，... par_18
   • COURSE_DISTANCE: course_id, distance_1, distance_2, ...distance_18
   • COURSE_DISTANCE：course_id，distance_1，distance_2，... distance_18
   • COURSE_INFO: course_id, course_name, address, phone...
   • COURSE_INFO：course_id，course_name，地址，电话......
   • etc.
   • 等等
4. 将课程信息拆分为表格，在适当的时间将某些表格与course_id连接COURSE_PAR：course_id，par_1，par_2，... par_18 COURSE_DISTANCE：course_id，distance_1，distance_2，... distance_18 COURSE_INFO：course_id，course_name，address，phone。 ......等

A similar question could be asked about recording the round. Do I have round_1, round_2, ..., hit_fairway_1, hit_fairway_2, num_putts_1, num_putts_2, or do I break each of those attributes into their own table? This question and its answers lean towards breaking this into tables. Does that mean a structure like:

关于记录该回合可能会有类似的问题。我是否有round_1，round_2，...，hit_fairway_1，hit_fairway_2，num_putts_1，num_putts_2，或者我是否将这些属性分解为自己的表格？这个问题及其答案倾向于将其分解为表格。这是否意味着像这样的结构：

• SCORE: hole_num, course_id, round_id, score, hit_fairway, num_putts, ...
• SCORE：hole_num，course_id，round_id，score，hit_fairway，num_putts，...

Recurring queries (using PHP) would be mostly on did the person hit above or below par, hit the fairway, # of putts, etc.

重复查询（使用PHP）主要取决于击中高于或低于标准杆的人，击中球道，推杆数等等。

Hopefully my question was clear enough. I know there are semi-similar questions out there, like the one referenced above, but I couldn't quite transfer those answers to what I was looking for.

希望我的问题很清楚。我知道那里有半类似的问题，就像上面引用的问题一样，但我无法将这些答案转移到我想要的地方。

Thank you!

谢谢！

1 个解决方案

#1

Not sure what 'slope' is, but how's this for a start:

不确定'斜率'是什么，但这是如何开始的：

COURSE - one record per course
CourseID
AddressInfo
NumHoles - usually 9 or 18

HOLE - one set of these per course
CourseID
HoleNum
Par
Slope - you can add more here if you want (water, sand, etc.)

MATCH
CourseID
MatchID
MatchName
StartDate

ROUND
RoundID
MatchID
PlayerID
RoundNum
StartDate

SCORE - every time a player plays a hole, add one of these
CourseID
MatchID
RoundID
HoleID
PlayerID
NumPutts
NumStrokes - you can add more specifics if you want (fairway, rough)

PLAYER
PlayerID

This is what you call 'fully normalized', and has minimal duplication of data from table to table. The resulting SQL statements can get a little complicated for a beginner, but it will be worth it if you get it right now.

这就是所谓的“完全规范化”，并且从表到表的数据重复最少。对于初学者来说，生成的SQL语句可能会有点复杂，但如果你现在得到它，它将是值得的。