Racing Database Design Inquiry

[eluser]zimco[/eluser]
I am trying to develop a CI application that keeps track of cars and points earned while racing in a series of races on a racing circuit and i could use some advice on the database design using MySQL.

The circuit consists of 16 racetracks with cars racing in two divisions of races: fast or slow, during the racing events at each racetrack. Points are earned as follows: A car Starting in a race gets an automatic-5Pts then add to that points for order of finish (if they finish) Win-5pts, Second-4pts, Third-3pts, Fourth-2pts, Fifth-1pt.

I was thinking i would need at least 5 tables: Racetracks, Cars, Race_Events, Event_Results, Car_Scores.

However, i am unsure if this would be sufficient to produce all the reports i would eventually like to generate from the database: Point leaders for each division, List of all starters in each division, Race Results, Fastest times, etc.

Any suggestions, thoughts (MyiSam or InnoDB?), and experiences in setting a database up for something like this would be appreciated.

[eluser]exodus7[/eluser]
As far as i know, MyISAM is a bit faster than InnoDB. The only advantage I know that InnoDB has over MyISAM is that it supports row locking. MyISAM only supports table locking. If you have lots of reads and writes to large tables, it eliminates some of the DB errors that using a MyISAM table would cause from the overload. InnoDB in my opinion is a little bit more reliable but the disadvantage is you have to take a small performance hit in exchange for not dealing with table locking issues.

[eluser]Ainab[/eluser]
MyISAM does not support Foreign keys while InnoDB does.

[eluser]webthink[/eluser]
Your table schema will likely handle what you need to do. What will be more important than the tables themselves is the columns. Car_scores is not absolutely necessary as it will be just calculated values based on values in other tables. If you need those values quickly without doing to much on the fly querying and calculating then you may find it useful. That said it could just as easily be a column in your cars table as it will be a one to one relationship

racetracks
-id
-city
-...

car
-id
-driver
-etc
-score

race_events
-track_id
-date
-etc

event_results
-event_id
-car_id
-division
-time

[eluser]zimco[/eluser]
Many thanks to all for the thoughtful responses. Each of you has pointed out some things i had not considered until you mentioned it, and have helped me to make better decisions for the database design.

--webthink special thanks for pointing out the lack of necessity for the car_scores table; as i was debating whether i really needed it or not as the database should not exceed 200 cars and i would think the server should be able to query and do the calculations relatively fast on such a small number. However, the database design books i've been reading lately always seem to prefer having the extra table rather than putting it in a column of the cars table, like you proposed.