Newbie Question 1

[SirReal66]

I have been tasked to create a database for a local race track (auto racing) that tracks the results for individual drivers through out the racing season (20 weeks).

Being new to MySql, I was wondering what input could be offered on what would be the best way to structure the tables for this...

Would it be easier to create a table for the drivers and then add the results for each weeks race? Or easier to add each weeks race and add each drivers finishing position?

Be gentle, I am new to this! ;-)

 

[johnwm]

For a good grounding in the basics of relational database construction, try:

http://www.alphabetcitydataworks.com/fundamentals.htm

I suspect you will need a Drivers table, a Races table and a Results table which is keyed to both of the others

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[guelphdad]

please note that your subject line is useless. it does not tell anyone browsing or searching on this forum what your topic is about. then folks don't know if a) they can help you with your problem and more importantly b) whether or not your thread might contain a solution to help them when they have a similar problem to yours.

in future please take the time to post a decent thread title.

 

[Kozusnik]

driver_tbl
-driver_id (auto_increment INT)
-driver_name (text)
-driver_active (TINY INT(1) - basically boolean yes/no.)
--other info about the driver

race_tbl
-race_id (auto_increment INT)
-race_name (text)
-race_location (text)
-race_date (DATE)
--other info about the race itself

results_tbl
-result_id (auto_increment INT - not sure it's necessary, but better to use within an application.)
-result_driver_id (INT)
-result_race_id (INT)
-result_place (INT - however you want to denote this.)

Now you can do something like...
SELECT * FROM results_tbl WHERE (whatever criteria)
LEFT OUTER JOIN driver_tbl on result_driver_id=driver_id
LEFT OUTER JOIN race_tbl on result_race_id=race_id
(more sql)

That's where I'd start.
Mark

 

[guelphdad]

I'd say the result_id isn't necessary, you can create an index across result_driver_id and result_race_id because a driver can't enter a race more than one time. when you want to reference that table you are going to reference one of the other two tables as well but not by the result_id field so that is why I say it should be removed.

Just my 2 cents.

 

[saywhat01]

Question

How would one go about importing data already on an Excel spreadsheet into this database using these tables?

The spreadsheet has the following columns

Name Number Event 1 Event 2 and so on...



If this hasn't helped the original poster, it sure has helped me!!!!

Thanks!

 

[Kozusnik]

Me, personally...
I'd export the Excel file to a csv and write a perl program to loop, parse, and upload. I'm sure there are better ways, but I stick with what's comfortable. How much data are you talking about...it could be easier to key it in manually.

Mark

 

[johnwm]

As there is an ODBC driver available for MySQL and (natively) in Excel, a simple bit of VBA will suffice

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints