Design question on database

JBotAlan

[Extremely long post warning Wink

]
[Newbie alert!!]

I am designing a scoring system for OCCRA (Oakland County Competetive Robotics Association). This year's game involves 2 teams vs. 2 teams. Each group of 2 teams is called an alliance. We have multiple events; each event has about 15 matches. At the end of each match, I get the score sheet from the judges with how many balls are in each goal, which way the teeter-totter is tipped, and how many robots are "ramped". They write the final score on the bottom of the sheet. I am writing a mySQL/PHP app to hang onto these scores, and rank the teams at the end of the event to figure out who won.

Each team plays 3-4 matches in a night with a different alliance each time.

So, what I've done is I've created a table called 'matches'. All it does is hold a match ID, an event ID, and which teams are on the red alliance and the blue alliance. The match ID is auto incrementing, and I refer to it from other tables. The event ID will refer to an auto incrementing key in another table, called 'events', but I have not implemented that yet. I created another table called 'match_scores'. This is the important part. It holds how many balls are in each goal, which way the teeter totter is tipped, and how many bots are ramped, and my PHP calculates the final score from this table.

Now, this is the part I need your opinion on. Since I am trying to rank the teams, the average score for each team must be found, then the teams need to be sorted by this average. I got it working, but to get it working, I added two columns to the 'teams' table, 'tempscore' and 'MatchesPlayed'. Their average is found by dividing tempscore by matchesplayed. Now, I hate putting this temporary data in the table, especially when it seems out of place like it does here. However, I don't know how else I could do this. So, do you think I did it right? How would you deal with this data? I know that I'm not supposed to store redundant data; so I need to calculate the final scores of each match on the fly. Unfortunately, it's making for some pretty complex code. I can show you what I mean if you need to see it.

I just would like some feedback.

Sorry for rambling. If you need, I can probably paraphrase this tomorrow. That's what you get for letting me post when I'm deprived of sleep... Laughing

Just tell me if you want to see the source.

Thanks for reading this far.
JBot

hexkid

JBotAlan

OK. That's sorta what I was thinking, but it felt a little dirty because of the info I'd read in my MySQL book. It had a bit of design theory, and I was trying to follow it. However, I understand what you mean by just worrying about readability.

In any case, my solution is much, much more efficient than an Excel spreadsheet or the messed-up (think 7-8 rows per team) Access database they used to use.

Thanks for the feedback (and for reading through that clear-as-mud post up there...)

JBot

EDIT:

Just out of curiosity, right now I'm bombarding my server with UPDATE TABLEs and the page loads slowly, which is a sign to me that the server is hurting. I think I can get around it, but do you forsee any problems with leaving it the way it is? It's getting 4 UPDATEs per match right now, which is 4 requests * 16 matches = 64 requests in less than a second. Is that painful?

Thanks again

hexkid