Database Architecture Advice

I want to create a database to store the competition tables for (European) football. What I initially wanted to have was a single table for all competitions with a competition_id column. And if I wanted to get the table for a competition, I would select based on competition id (where c开发者_运维百科ompetition_id=1 for example).

I have a decision to make now. Because one of the competitions uses a groups then knock-out games, this doesn't work with the general all-purpose table.

Even if I were to add another column "group" and where it applies use it, it seems like a bad idea. Plus, there is no way to add knock-out games to this table.

I am considering doing a separate table for each competition, thus being able to embed particularities of each type of competition. Future proofing is also something I'd look at.

Do you have any advice?

You should make a general_competitions table which contains same data type about each competition and a second table for each competition type. And for queries use JOIN.

Regardless of the higher structure, I would expect there to be a single games/matches table:

game_id, home_team_id, away_team_id, home_score, away_score, etc...

This might also have information about the match venue and time, etc.

I would think this is a granular unit and would facilitate team analysis and statistical reporting without having to union different tables.

Then games would be linked to competitions:

competition_id, game_id

In that link you could add group information, or it might be possible to view the groups as sub-competitions

For elimination tournaments (where the next game's participants are unknown until the previous round's game is complete), you might have a binary-tree structure which linked to the games. Such a structure might have place-holder games or not link to games until they are scheduled, I can see a few ways to skin that cat, depending on your requirements for analysis.

I guess this woul be my approach:

table Competitions
competition_id PK
competition_name
...

table Teams
team_id PK
team_name
...

table Results
result_id PK
competition_id FK
home_team_id FK
away_team_id FK
group_id
eliminatied 
date_played
...

So now you can store all the main info you need.
You can use group_id of Results table to specify if that is a group or elimination stage. You can set eliminated to 1 if the team has been eliminated.

With this structure you can come up with the select statement(s) to generate standings for you. You can use date_played ranges to select particular season (year). So technically you can use same structure every year, just keep adding teams and new games. Search date range to get data for a particular year/season.

There is a lot of other info can be added, like table to store different statistics, but all that is up to your imaginations.

Hmmmmmmmmmm.

I think the key is in the observation that there are several distinct "basic competition forms", and that what you call a "competition" may actually be composed of several phases which are each of a separate "competition form".

Competition "forms" are, essentially, direct knock-out and everyone-against-everyone. Both of them come in the flavour of "home and away games" (think football) and "one single game" (think tennis tournament).

A competition such as the UEFA champions league consists of three phases : a direct knock-out phase, a second phase in which there are eight distinct "everyone agains everyone" competitions, and a final phase which is again direct knock-out.