Using SQL table structure for points scoring

I am currently playing in a Fantasy Football league and I’m attempting to use some stats to make some informed decisions for my team.

At the moment, I have a table with some historical player performance data, with each row of the table being one player’s output in one game, as below:

Player          PassingYards    PassingTDs
Tom Brady       200             5
Deshaun Watson  350             3

My league has a specific points scoring tariff, which I aim to store in another table, as below:

Metric          Points
PassingYards    0.04
PassingTDs      4

From the two tables above, I aim to build a view that will calculate the points scored for each game, which would like the below:

Player          Points
Tom Brady       28
Deshaun Watson  26

[Where 28 is (200*0.04) + (5*4) and 26 is (350*0.04) + (3*4)]

My question is, how do I store the points tariff data? My data obviously has more than 2 metrics so I’d prefer not to type in the number manually for each of the metrics. I’m sure there’s an easy way to do it but I just can’t work it out. Thanks!

Answer

I think you want:

Player          Metric          Value
Tom Brady       PassingYards    200
Tom Brady       PassingTDs        5
Deshaun Watson  PassingYards    350
Deshaun Watson  PassingTDs        3

and:

Metric          Points
PassingYards    0.04
PassingTDs      4

Then you can join the tables and aggregate:

select pm.player, sum(pm.value * m.points)
from playermetrics pm join
     metrics m
     on pm.metric = m.metric
group by pm.player;

Actually, I suspect that you also want a Players table and a MetricId column in the metrics table. That way, you don’t need to repeat strings in different tables — something where typos can throw off your results.