Calling Database Experts

[denton]

Hi all.
I've got a part-built MySQL form database. One thing I'm stuck on is how to store the form figures.
Do I store these in the Horse table, look them up "on the fly" ...or?

Another problem is that on today's card, for example, Horse A has form of 131. Today he finished unplaced, so his new form figure is 1310. That's fine for his next race card but if I pull the old racecard from the database, I need the old figures of 131 and not 1310. I can't think of a way to do this.

Help appreciated!

D

[RonTiller]

I don't know what tables or primary keys you are working with but I understand what you want to do.

Take a table with a primary key =

Horse
Date
Track
Race

When you calculate any number for that horse for that race, INSERT a record for that race into this table, a record that has the form number for that horse in that race for instance. Do a corresponding UPDATE if the record already exists.


So when you calculate all the relevant handicappng factors for Horse = Yabadabado, Date = 8/1/09, Track = MTH, Race = 2, dump all those factors into a Horse Factors table, with the Horse, Date, Track, Race Primary Key.

These factors might be Power Numbers, Form Factor (going into that race), Average Earnings (going into that race), Turf Record (going into that race), Class Change (going into that race), etc. You can also store the values of numbers which depend on a sequence of previous races (which I take is what you Form Number is). That is, you can store # of races after a layoff (going into that race), last 3 running styles (going into that race), last 3 speed ratings (going into that race), surface change indicator, etc.

Calculate all this stuff once, store it in a table, index the table appropriately, and never worry about them again.

When you pull the old race card from the database, link on the Horse Factors table and you get the all the factors as they were calculated going into that race.

Ron Tiller
HDW

[Dave Schwartz]

Be careful about storing things by horse name. They sometimes change!

[RonTiller]

Horse names also get reused after a certain number of years waiting period. Its best to use registration numbers if you have them, but registration numbers are not typically in anybody's data files. To help out in a pinch, the Dam name and year of birth will do the trick

I know, I know - Dam has twins, twins mixed up as 2 year olds, renamed once reidentified, but renamed to a name used 8 years ago whose Dam happens to have the same name as the other Dam, etc. Sigh...

Ron Tiller
HDW

[Dave Schwartz]

Ron,

My problem is with foreign horses who race once then get a name change because they have a name that has already been used.


MY favorite story for this was Ferdinand, a South American horse that race in the US maybe in 1994-1996. In his first start he grabbed all the races of the great Ferdinand (BC horse). Then, when he raced back he bacame Ferdinand III - because there was already a Ferdinand II.

So, in just 2 starts he showed me how easy it was to shoot down my entire filing scheme.


So, one must leave a way to re-assign races to the proper horses AFTER a name change is discovered.


The second story I have was a horse whose new owners decided to rename him. Thus, his 10 most recent race lines were suddenly duplicated in my database. Those 10 races now had an extra entrant, both of which had precisely the same running lines, jockey, trainer, odds, etc. but now with 2 different names.

These two issues caused me to completely change the way I stored data.

Dave

[denton]

I don't know what tables or primary keys you are working with but I understand what you want to do.

Take a table with a primary key =

Horse
Date
Track
Race

When you calculate any number for that horse for that race, INSERT a record for that race into this table, a record that has the form number for that horse in that race for instance. Do a corresponding UPDATE if the record already exists.


So when you calculate all the relevant handicappng factors for Horse = Yabadabado, Date = 8/1/09, Track = MTH, Race = 2, dump all those factors into a Horse Factors table, with the Horse, Date, Track, Race Primary Key.

These factors might be Power Numbers, Form Factor (going into that race), Average Earnings (going into that race), Turf Record (going into that race), Class Change (going into that race), etc. You can also store the values of numbers which depend on a sequence of previous races (which I take is what you Form Number is). That is, you can store # of races after a layoff (going into that race), last 3 running styles (going into that race), last 3 speed ratings (going into that race), surface change indicator, etc.

Calculate all this stuff once, store it in a table, index the table appropriately, and never worry about them again.

When you pull the old race card from the database, link on the Horse Factors table and you get the all the factors as they were calculated going into that race.

Ron Tiller
HDW


Hi Ron.
You are spot on with this solution - it's obvious once pointed out to me!
I already have the table you refer to (I think!). I call it race-participation, and I include race_id, horse_id and all the details needed for the racecard and result, such as number, draw, head-gear, finishing position, odds, etc.
For the form figures I mentioned, I have made a column "form_pre(race)". I also made a column "form_post(race)" which is form_pre + today's finishing position - I don't know the code for how to do this yet! I then intend to use form_pre as the default value for form_pre in the next race (again I don't know the MySQL or PhP code to do this yet.
I be interested to hear your comments on my thoughts/progress.

Regarding horse's names changing, my understanding is that a name can't be repeated for at least 10 years, which won't bother me. Regarding horses from different countries, I use the country suffix to distinguish these (eg. (USA) for American horses, (GB) for British etc.

Regards,
Denton