ScoreID - scores
COUNT ScoreID as scoreCount - scores (basically counts the number of records in the query)
Score - not a field, but used here to output the average score
Country - countries
Year - nominations
Nominee - nominations
Comments - scores
CategoryShort - categoriesShort
CountryShort - countries
Just to clarify the whole thing. It might not be the best structure, but there are two scoring things going on.
There is a scores table which records scores from 1-10 that people have been making when visiting the website. So any given lodge can have multiple votes in any particular category.
And there is a nominations table, whereby any given lodge can only have one nomination in any particular category. So if a lodge has had any votes in a particular category, it gets a nomination. And it is this table which stores the ranks we’ve been looking at.
The idea now is that some industry judges can view all the results and rank any lodges from 1-5. Its slightly odd in that these rankings may not reflect the 1-5 based on the average score in the scores table.
And yes - the language used is terrible, and back to front, but its partly historical, and partly because they changed things around this year in a way I don’t personally think makes a whole lot of sense, but it is what it is.
So for example:
Table - lodges
LodgeID (PK), Lodge
1, Lodge A
2, Lodge B
3, Lodge C
Table - categoriesFull
CategoryID (PK), Category
1, Best Value
2, Best Cuisine
Table - scores
ScoreID (PK), Score, LodgeID (FK), CategoryID (FK)
1, 5, 1, 2
2, 7, 1, 2
3, 9, 1, 2
So Lodge A has received 3 votes (scoreCount) in the Best Cuisine category, with an average score of 7 (AverageScore)
That Lodge will also have a row in the nominations table:
NominationID (PK), LodgeID (FK), CategoriesFullID (FK), Rank1, Rank2, Year, Nominee, Finalist, RunnerUp, Winner
1, 1, 2, 1, 2, 2015, Y, N, N, N
So Lodge A has a nomination for Best Cuisine, and has been given one rank (Rank1) of 1 (worth 10 points) and on rank (rank2) of 2 (worth 6 points).
Hope that makes some sense - it doesn’t help that the system / process the site owners want to run is probably more complicated than it really needs to be.