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.