title: mySQL database scheme for golf scores
body: <p>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.</p>
  
  <ol>
  <li>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.)
  
  <ul>
  <li>COURSE: course_id, par_1, par_2, ...par_18, distance_1, distance_2, ...distance_18, etc.</li>
  </ul></li>
  <li>Split the course information into tables, connecting certain tables with course_id at the proper time
  
  <ul>
  <li>COURSE_PAR: course_id, par_1, par_2, ...par_18</li>
  <li>COURSE_DISTANCE: course_id, distance_1, distance_2, ...distance_18</li>
  <li>COURSE_INFO: course_id, course_name, address, phone...</li>
  <li>etc.</li>
  </ul></li>
  </ol>
  
  <p>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? <a href="https://stackoverflow.com/q/4844593">This</a> question and its answers lean towards breaking this into tables. Does that mean a structure like:</p>
  
  <ul>
  <li>SCORE: hole_num, course_id, round_id, score, hit_fairway, num_putts, ...</li>
  </ul>
  
  <p>Recurring queries (using PHP) would be mostly on did the person hit above or below par, hit the fairway, # of putts, etc.</p>
  
  <p>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.</p>
  
  <p>Thank you!</p>