Problem with Golf SQL

Question

I want a preface to this, saying that I believe Right & trade; The way to handle this is probably restructuring the database tables. BUT

I have a job for a client where he buys a database on most golf courses in the USA. Since it will periodically receive updates from the seller, I saved the structure as sent. (Turn aside: the data seller about the word promises to tell us when he “should change the existing record identifier.” What? Do you sell the data and then change the unique identifier after?)

So, I have a table of tees (groups of holes that play as a sequence); round table (one tee play) and hole table (individual score for each hole).

In the tee table, these dolts store pairs (target score), for example, the field name Par_ 1, Par_ 2, Par_ 3 - Par_ 18 with INT values. Thus, the hole number is part of the field name and is not stored as a value at all.

Now let me say that I need to find on average all the ratings that you have for holes whose pairs are 3 in a particular round. Or all your rounds.

Sort of

SELECT (SUM(holesPlayed.score) / COUNT(holesPlayed.score))
FROM holesPlayed, tees
WHERE
holesPlayed.round_id = 9
AND tees.CourseTeeNumber = 'UT-94-1'
AND tees.Par_x = 3;

This way, I can easily find the results by hole, but there will be a nightmare to find the point for the hole, since the hole number is embedded in the field name like this.

Should I just start writing something to export pairs of tee and hole to their own table?

Am I missing some awesome SQL kung fu that will save me?

What is your advice?

Answer 1

, . PAR_18 - , . , .

Answer 2

, , , Right ™.

, ( , , , , ). / , .

, .

Answer 3

Asaph - , , UNPIVOT , . - :

SELECT (SUM(score) / COUNT(score)) AS avgScore
  FROM holesPlayed
  JOIN (
      SELECT * FROM Tees
        UNPIVOT (Par FOR Par_N IN 
          (Par_1, Par_2, Par_3, Par_4, Par_5)) AS Ts
        WHERE CourseTeeNumber = 'UT-94-1'
  ) AS Ts
  ON Ts.CourseTeeNumber = holesPlayed.CourseTeeNumber
  WHERE
    holesPlayed.round_id = 1
    AND Ts.Par = 3
  GROUP BY ...
;

Answer 4

( 18 ), PAR 3-5, /SP PAR, WHERE Par_X . , , , .

PAR 3:

SELECT *
FROM tees
WHERE
    tees.Par_1 = 3
OR  tees.Par_2 = 3
OR tees.Par_3 = 3
...etc

3 PAR 3 PAR 4 PAR 5, . selects wheres , PAR . , - , , .

SP, PAR . , SP, - .

Answer 5

dbms, accees raher, . , , .