I'm on the wrong side of cemetary hill, but I've decided to try to learn mysql.

my hobby is horse racing and I'm already a bit stuck in the design phase and don't want to start off down the garden path before I start!


The main table "raceresults" as suggest holds all previous race results.
"newrace" contains the details of the current days races.
"newrunner" contains the details of the horses due to run in the days races.

"newrace" :- race_id,r_date,course,r_time,pen_val,age_grp,dist_ mfy,r_bhb
"newrunner":- race_id,no_drw,form,horse,age,wt,trainer,jockey,OR
"raceresults":- race_id,date,course,time,value,race_age,dist_mfy,c lass,pos,drw,horse,wt,jockey,trainer,age,s_price.


my questions are:-
1. Is it ok to have identical names in more than 1 table.
2. what are the primary keys I need.
3. Would I be better joining "newrunner" and "newrace" into one table and just query from there.


many thanks in advance

Davey H

identical names in different tabels isn't a problem, assuming its referring to the same values, often where the value in one table is the FK (foregin Key) or PK (Primary Key in another table. its quite a common technique to, say name an autogenerated Priamry Key and give it the name "ID", then when that value is referred to elsewhere in other tbales its referred to asa entitynameID, eg you coudl have a table Horses with a PK of ID and when that value is used elsewhere in other 'child' tables its called HorseID.

theres as many naming conventions as there are DBA's, and like a***holes, everyones got an opinion, some good some bad.

at this stage I'd stick ore to narrative rather than designing tables, think of the problme in temrs of entities

fer instance
you have an entity store information about horses
presumably you'd have an entity store information about racecourses
from that you'd probably have an entity that stores stuff about races, a race is held at a specific racecourse, so the primary key of the racecourse will be stored somewhere in races so to identify a specific race with a specific racecourse. presumably you'd want to include the date of the racecourse meeting, that probably means your primary key for the race is going to be a composite key of racecourse, date(of race), and the race ID (race number?)

having defined a race you'd then want to assign horses to that race, using whats called an intersection table (essentially you have two entities horse and race and you need some method of associating (linking) the two entities. often an intersection table has just the primary keys of its parent tables and use those columns as its primary key. but in this case you coudl expand that to include any information that is unique to that interesection of entities. so this table could be a candiate to identify the starting weight, weight penalty, finishing position and so on... ie anything that is uniquely part of that entity so what else do you think you might want to store in this intersection table
where would you store the 'going'/ ground conditions?

if you are interested in the betting do you care about the odds over time, or just the SP.

You could have details of jockeys, presumably you'd have a table of Jockeys, and you would select one Jockey and assign that Jockey to a horse... so that also could go into the intersection table

You could record who is training the horse.. so presumably you have an entity that records trainers/stables and you'd have a different intersection table that identifies what horses are at what stable

Likewise you could consider owners of horses, an owner may have many horses, equally depednign on how you model it many owners (people) may have an interest in the same horse.

it depends on how complex your real world problem is.

as its a training exercise KISS springs to mind..... so don't think about stables, trainers, owners etc for now.

however bear in mind that you want to normalise your data, that means avoiding repeating groups (essentially if you find you have the same piece of information occurring in different rows in the same table you've probably not normalised your data. by repeating groiups, say you had details of addresses, if the same address appears in multiple rows then theres a problem. similarly if you find you are storing say multiple phone numbers that can be a problem (a person may have many phone numbers eg work, home, mobile and so on)