Organizing content of multiple arrays

[visionaire]

I am working on a site with snooker results. Each match is one row in a MySQL table, and somehow needs to contain the breaks (scores) that were made in each match. I am expecting the site to have many users, so I want to find an efficient way to do this.

 

Let's say a match has 5 breaks in it, 50, 60, 70, 80 and 90. Another match only has 3: 20, 30, 40. I thought of storing them in the database as follows: '20;30;40' and then using PHP Implode to create an array when retrieving them. I want to, however, also be able to make a high break list, which would display 90, 80, 70, 60, 50, 40, 30, 20.

 

I am however stuck on a way to combine the data from multiple matches and combine them into one list and then sort that list from high to low. Does anyone have any tips for this?

[AbraCadaver]

Assuming you have a matches table with an id and match details, then create a breaks table with a break id, related match id and 1 score per row.  So each match would have 1 row in the matches table but multiple rows in the breaks table (1 for each break).

 

If you want scores for a specific match then:

SELECT * FROM breaks WHERE match_id = 1 ORDER BY break_id ASC

 

If you want a high break list then:

SELECT * FROM breaks ORDER BY score DESC

 

By joining with the matches table you can get the match details (date, location, etc.).

[visionaire]

The problem I see with this is the system will get very slow if I end up with 1000 matches and 10 breaks per match. It will be slow to do 'SELECT * FROM breaks WHERE MatchID="$ID"' for a table with 10.000 rows. Is there any way to avoid this?

[AbraCadaver]

It won't be slow and this what relational databases are built for.