How to structure database for relational? data

Question

I need to keep track of points scored and who they are scored against. Previously, I did this using a flat file database (that was a mess) that looked like this:

03611100025
00001000000
21011000000
00003000000
00021000000
10001050000
00001402000
00001000100
00001013050
00001000100
00001000000

Each team got a row and a column, and I guess the whole thing turned into sort of a graph. Now I can do this is MySQL as well, but it'd be bloated and would force each row to have an absurd number of columns.

I feel like I must be missing some other drastically easier method with which to do this. Can anybody lend a hand?

Thanks. (sorry I was very unsure what to title this Q)

Answer 1

Have 2 tables, Team and Game, where Team has a team name, and a team ID, and game has 2 team IDs (team 1 and team 2) and the score of the game. Then, you have foreign keys (for integrity) between the Game and Team tables. This will reflect who played who and what the score was with a minimal schema and a very simple structure.

Team
|-------------------------|
| Primary (int)| id       |
|         (chr)| name     |
|-------------------------|

Game
|-------------------------|
| Primary (int)| team1    |
| Primary (int)| team2    |
|         (int)| score1   |
|         (int)| score2   |
|-------------------------|

So, some sample data would look like:

Team
|------------------|
| id | name        |
|------------------|
|  1 | Blue Devils |
|  2 | Cardinals   |
|  3 | Fish        |
|  4 | Lemmings    |
|------------------|

Game
|---------------------------------|
| team1 | team2 | score1 | score2 |
|---------------------------------|
|     1 |     2 |      7 |      8 |
|     1 |     4 |      2 |     25 |
|     2 |     3 |      8 |      2 |
|     3 |     4 |     17 |     18 |
|---------------------------------|

This data indicates that team 1 (Blue Devils) played team 2 (Cardinals) with a score of 7 to 8. The rest of the data is similar.

If you do not need to track the team names, you can leave that field out, but this is often useful information.

So, with this schema, you would get the scores for a particular team with a query like

SELECT * FROM Game g 
 INNER JOIN Team t on t.team1 = g.id

You could then also add additional information if you need to, like when the game took place (date), and any other information, such as other statistics about the game or team.

Answer 2

The first response is correct. If you organize your data into relations, and create one table for each relation, the data will be a lot easier to work with. The background learning you need to do in relational database design will teach you more about this.

If you use the Team and Game structure from the response, you can still convert that structure into the kind of structure you imagined in your question. This process is called "crosstabulating" or "pivoting" and it's well documented in the tutorials.

While it's easy to move from a relational structure to a crosstabulated one, it's monstruously difficult to go the other way. It's also difficult to compose other queries against crosstabulated data. An example might be finding the highest score scored against each team. If you put your data in a database, sooner or later you are ging to want to do more than one thing with it.