mysql table design, not sure on best approach

Question

I am building a mysql database to store golf scores.

I am not sure on the best approach to store the round information.

Each round is made up of either 9 or 18 holes and for each hole I need to store

• Hole id
• Number of shots
• Stableford points
• Green in regulation
• Fairway hit
• Number of putts
• Number of penalty shots

My question is should I have one huge table, that stores all of this. Like a rounds table. and have the above 7 fields 18 times for each hole.

Or should I have a smaller rounds table that just contains the date played etc and then another table such as scores that just has the 7 fields, and have multiple rows in that table to make up the complete round?

I guess I am asking in terms of which would perform better and which is the better design?

Thanks

Answer 1

Divide your information as much as possible. Otherwise you'll face alot of redundant data.

Answer 2

Definitely two tables. First, let's name it rounds will contain data relevant to round itself, such as date, id of the golf terrain etc. The other, let's name it hole, will have 7 aforementioned fields, together with round_id field that will reference round that particular hole belongs to.

Main benefits are clearer design and avoidance of redundant data. If you keep everything in one huge table, each row would need to contain not just fields relevant to the single hole, but also fields relevant to the whole round (date, id of the terrain..) -> same data in many rows, unnecessary. What if you mistakenly enter wrong date? You would have to change it in all 9 to 18 rows instead of only one.

See also:

• Normalization in MySQL
• Database normalization