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?

In your sample score database, you have the 5th team as having played themselves w/ a score of 1 — cdeszaq
– cdeszaq, Commented Jan 13, 2010 at 17:56

cdeszaq · Accepted Answer · 2010-01-13 17:58:11Z

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.

i feel like this'd be very helpful, but I'm afraid that I'm quite confused still. :-/ could you explain further?
Remember that you need to make sure that you only store data for one pair, ie if you store data in the game table for team1-team2, don't store for team2-team1 as well. Do this by simply always placing the team with the lowest id in the first column or similar.
I'm trying hard to wrap my mind around this. The first table seems to do nothing but assign an ID (I'm a little confused as to why that's necessary, but i'll press on). Then the second table has two primaries (does that mean they share a row?) and the score between just them. The team table would also have a score column that you omitted? Am I right about the primaries sharing a row in Game? Thanks again
I see how you might have been confused. The "id" field in the team table serves as a way to uniquely identify the different teams. It is a surrogate primary key and really holds no information other than being an identifier. The Game table should record the scores of each team for each game, and the team table is where information about each team is stored. Having 2 fields marked as the primary key indicates that the primary key for the table is a composite of both fields. Each game gets 1 row, and that row has the 2 teams that played and their respective scores.
Perhaps this will help you understand relational database design deeptraining.com/litwin/dbdesign/…

Walter Mitty · Accepted Answer · 2010-01-14 13:28:05Z

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.

Collectives™ on Stack Overflow

How to structure database for relational? data

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related