Data structure advice

Databases
1

Just looking for a bit of advice as to how to best go set up my database structure.

I currently have the following tables:

lodges

LodgeID (PK)
Lodge
etc

scores

ScoreID (PK)
Score
CategoryID (FK)
LodgeID (FK)
etc

categoriesFull

CategoryID (PK)
Category
etc

countries

CountryID (PK)
Country

So any lodge can have multiple scores, in multiple categories.

And I can currently produce a list of lodges for any given category, in a particular country which lists the lodge, the number of votes in the category, and the average score in the for that category, using:

SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, Lodge, Country, lodges.CountryID, CategoryID FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID INNER JOIN scores ON lodges.LodgeID = scores.LodgeID WHERE lodges.CountryID = 37 AND CategoryID=‘14’ GROUP BY Lodge

So far, so good.

What I’m looking to do now is allow create two new fields, ‘rank’, and ‘finalist’. So the plan is to use an update multiple record on those pages, with a tick box for ‘finalist’ and a text / integer field for ‘rank’.

So someone can view all the lodges in a list and just tick the boxes for the ones they want to be a finalist, enter the rank of 1-10 for the top 10 ,and click update to update all the records.

However, I am not sure which table it would be best to put these in. They’re kind of related to the scores, but really apply to the lodges, i.e. any given lodge will be deemed to be a finalist in a particular category, and may be ranked in any category.

At the moment I’m thinking I just need an extra table:

finalists

FinalistID (PK)
Finalist (yes / no)
Rank
LodgeID (FK)
CategoryID (FK)

Hope that makes sense - any advice on this would be appreciated before I start down the wrong path.

Thank you!

2

your scores table needs a slight rework (to remove the useless and inefficient id) –

CREATE TABLE scores
( CategoryID INTEGER NOT NULL
, LodgeID    INTEGER NOT NULL
, PRIMARY KEY ( CategoryID , LodgeID )
, INDEX altx1 ( LodgeID , CategoryID )
, FOREIGN KEY ( CategoryID )
    REFERENCES categories
              ( CategoryID )
, FOREIGN KEY ( LodgeID )
    REFERENCES lodges
              ( LodgeID )
, score      TINYINT NOT NULL
);

your finalists table needs to look the same –

CREATE TABLE finalists
( CategoryID INTEGER NOT NULL
, LodgeID    INTEGER NOT NULL
, PRIMARY KEY ( CategoryID , LodgeID )
, INDEX altx2 ( LodgeID , CategoryID )
, FOREIGN KEY ( CategoryID )
    REFERENCES categories
              ( CategoryID )
, FOREIGN KEY ( LodgeID )
    REFERENCES lodges
              ( LodgeID )
, finalist   CHAR(1)
, rank       TINYINT NOT NULL
);