[snip]At 17:15 07/03/2005 +0000, you wrote:
well - two options spring to mind...
1) use a single table, with 23 columns instead of 2. Then sum the columns in your query.
- 1 query, no loops required.
2) have your script pull everything and add it all up.
- 22 queries, a loop for each, array storing scores & indexed by name.
Actually I disagree strongly unless your intention is to be able to read the rows yourself !
As the stated intention is to add up scores (doing the maths) in fact much more efficiently you want to have just 3 columns in a single table, "tblBlah" :
User RoundNumber RoundScore
Bob 1 6 Bob 2 2 Bob 3 3 Steve 1 5 Steve 3 1 Steve 4 6
Now you've got a list of rounds and the average score per round. And you don't have to write a gigantic query across 23 tables like SELECT tbl1.RoundScore, tbl2.RoundScore, tbl3.RoundScore, tbl4.RoundScore .... even if that was practical (you'd have to use outer joins and the result set would be huge).[snip]
Using multiple tables, or even multiple columns is deeply inefficient for this. You can set up a couple of queries to merge all the tables into one in a few minutes, it's called database refactoring and in your case would improve the manageability of your data by10x.
Using multiple tables was described for comparison reasons, to show why it was not a good idea.
Using multiple columns is the most space/time efficient - assuming players aren't only playing a few rounds. It is, however, not the most *flexible*, as you're saying. You need to modify the number of columns if things ever change, though you aren't limited to the number of players.
Your database is fully normalized, but has the penalty of storing more data (minor), and is slightly more work on him if he wishes to display a summary, which I assumed was also part of his intention.
Your way is the way I would do it for a full proper application, my suggestions were the way I would do it if it is something where the rules simply don't change (think sports/etc.) - I gave quick and simple suggestions for (what I saw as) a quick and simple app.
I agree with your solution wholly, if his deadline isn't in 2 hours. Unfortunately, all too often our deadlines are yesterday.
For this sort of table, you would create an index on each of the 3 columns to imccrease the query speed massively. A nice side effect apart from speed, is that data stored in this way takes up no space if a (new) user hasn't completed any rounds (for example ;-)
[snip] One should be indexing all their tables properly :P
All good valid points, well said.
Cheers,
--
- Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
