Golf SQL Problem

I want to preface this by saying I bel开发者_如何学编程ieve the Right™ Way to handle this is probably to restructure the database tables BUT

I have a job for a client where he is purchasing a database of most of the golf courses in the US. Since he will receive updates periodically from seller I have preserved structure as sent. (Nasty aside: jackass seller of data promises to let us know when he "has to change an existing record's id". What? You are selling data and then changing the unique id after?)

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

In the tees table these dolts are storing the par (target score) like so field name Par_ 1, Par_ 2, Par_ 3 through Par_ 18 with INT values. So the hole number is part of the field name and is not stored as a value at all.

Now let's say that I need to look up an average of all of the scores you have for holes whose par is 3 in a particular round. Or all of your rounds.

Something like

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;

So I can easily look up scores by hole but to look up the par for a hole will involve a nightmare since the hole number is embedded in the field name like that.

Should I just start writing something to export the pars by tee and hole to their own table?

Am I missing some amazing SQL kungfu that will save me?

What's your advice?

I recommend writing a utility that transforms data from the vendor's format to your preferred format. Your utility will be responsible for parsing PAR_18 and the like into something more manageable that you can index. When you get updated data from the vendor, you run it through your utility to generate updated data in your preferred format.

I think you probably should handle it the Right™ Way.

Have a table that maps from the seller's IDs to your IDs (this way, if he changes one, all you have to do is change that table). And just write some scripts to import/update any data he sends you into your own system.

It shouldn't take you very long, and it'll save a lot of time in actually writing queries later on.

Asaph's answer is the best way, but if you'd really rather keep the broken structure, look into using UNPIVOT and hope it's supported by your DBMS. Something like:

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 ...
;

Since the field names are static (there will never be more than 18 holes) and the PAR of a hole is always 3-5, you could create a view/SP for each PAR that included the WHERE for each Par_X column in it. It'll be a pain in the butt the first time you write it, but you'll be done and won't have to continually reformat everytime you get new data.

VIEW PAR 3:

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

Do that 3 times for PAR 3 PAR 4 and PAR 5 and you're done. then you can call selects and wheres as you need them based on the PAR of the hole. Best part about this is that you can use the view in a join or anything else you can think of just like a normal table.

If you take the SP route, you can accept the PAR as a parameter and only write it once. But there may be some performance issues related to using a SP in this case and you may not be able to use it in joins or anything else.

An implementation depenant way, would be to access the system tables of the dbms, this should allow accees to the column names as values raher than column names. Although this would intoduce security issues, these may be overcome though use of a procedure, and restricting access to this procedure.