CODEWITHSUNDEEP
postgresqldatabase-design
mack
mack

Reputation: 2965

Schema for golf tournament scores

I work for a small non-profit organization and we are looking for a way to quickly tally scores and provide a few statistics for donors at our annual golf tournament. I thought this would be fairly easy, but I'm struggling to come up with a database schema to capture the scores. I can't figure out how the player's score relates to the specific hole on the course.

This is the diagram that I have so far. Am I way off base with this?

ERD

The Schema can be found here: https://app.quickdatabasediagrams.com/#/schema/forneGJp40inm7rWlf2Sbg

Upvotes: 1

Views: 2088

Answers (1)

TrevorJ
TrevorJ

Reputation: 554

Perhaps make the Scores table a m:n join table between Players and Holes to capture each player's score on each hole. This is depicted on the diagram below. To get the score for a round you'd sum all scores for all holes with a specific CourseId, for a specific event.

I also denormalised it a little, adding a total score to the Rounds table. This means that you don't need to SUM() the individual scores every time to get the tallies for each Player's Round. That's just a suggestion for performance optimisation.

enter image description here

Source: https://app.quickdatabasediagrams.com/#/schema/x_amshIckkeGp8KAKEAmLQ

If it is possible to play the same course twice in the same event (the first and last matches could be on the same course, for example) then you should provide for that.

I have two other suggestions:

  1. I think the relationship between Events and Venues is in the wrong direction.
  2. I suggest splitting Players into two tables. One representing the human being, and the other representing the human's participation in the round. Perhaps "Person" and "Contestant" would be good names.

Upvotes: 2

Related Questions