You are invited to Log in or Register a free Frihost Account!

Design question on database

[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.
JBotAlan wrote:
I just would like some feedback.

In my opinion it's much more important to have code that you can understand easily (think next year's OCCRA) than to have a 100% normalized database.

You know your database isn't 100% normalized and you know why. Add a comment in your code explaining this design decision in the lines that update those columns in the `teams` table and be happy!
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...)



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
JBotAlan wrote:
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?

Are you sure it's the UPDATEs that are slowing your page down?
Can it be something else?
If you don't mind testing a few updates and reentering them again after the tests, try replacing all mysql_query("UPDATE ...") with dummy_mysql_query("UPDATE ...") [see code below] and maybe also somthing similar for other mysql_* functions.
function dummy_mysql_query($query=true, $conn=true) {
  return true;

If it really is the UPDATEs, do your tables have proper indexes (the columns specified in the WHERE part of the UPDATE)?
Related topics
Website design question
how did you learn about html, php or other languages???
How tough is PHP?
route book management - database creation
database design of room assignment schedules of nurses
Multiboot Vista with XP using MBR boot manager
Howto not get your Programmering Question answered
How to build a good database design...
My database project
Sybase Database error message
Big, complicated php project: 10,000 frih$
Chained Selects
Forms with Username and Password
More Changes
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

© 2005-2011 Frihost, forums powered by phpBB.