Golf SQL Problem

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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