Database programming/design question

[Bathless]

First, a little background:

I have a database with race results going back to 2000. The database includes results for MTH, MED, ATL, BEL, AQU, SAR, PHA, DEL, PIM, LRL, CNL, TIM, and I plan to begin adding PEN. I use MS Access and SQL-like queries generated in stand-alone Visual Basic programs. This system replaced DOS-based dBase files I first created in 1990, but the design is essentially unchanged -- one table with all race-specific information and another with horse-specific information. The tables are 'related' by a key specific to the track, julian date and race number, e.g. '6170MTH09'. This design was mostly for the purpose of saving disk space on 1990-vintage PCs, well before the word 'gigabytes' applied to hard drives. Because it served me well, had no complications, and mostly because I was familiar with it, I kept it when migrating to Access.

My question, though, is more a logical one in the area of design and involves a way to handle race classifications. I use 3 race-level fields (call them CLASS1/2/3) in the 'RACE' table:

1) the general race classification, e.g., MSW, MCL, CLM, ALWC, AOCL, STRT, STKS, HCAP, etc.
2) where applicable in #1, e.g., CLM, MCL, or AOCL, the claiming price or top claiming price, if a range.
3) additional conditions, e.g., NW1X, NW16M, NW2L, etc.

Right now, I find that determining whether a horse's performance involved a move up in class, down in class, or was at the same level as his previous race, is cumbersome, at least programmatically. I can scan the PPs, recognize the names of a few horses, and intrinsically know the answer. But dealing with this question programmatically is a bit more daunting.

Moves like going from maidens to winners and allowance to stakes are pretty straight-forward. Where my difficulties lie is in handling moves from claiming to starter or back, claiming to allowance (and optional claiming), an open $10K claimer to a conditioned $16K claimer, statebred allowance to open claimer, and the myriad of combinations possible.

I realize, of course, that one move from claiming to allowance may be a step up, but in another case might actually be a drop in the level of competition. I don't expect to be able to address that programmatically.

How do you handle this? How deeply do you disect race class? Or do you just handle it with broad strokes? Again, I'm looking for general design solutions, although relevant programming hints are appreciated.

BTW, because horses easily ship to and from the racing circuits in my universe, races are written with very similar conditions for the tracks mentioned above.

[GameTheory]

I realize, of course, that one move from claiming to allowance may be a step up, but in another case might actually be a drop in the level of competition. I don't expect to be able to address that programmatically. Actually, that is the easiest to handle programmatically because you can usually come up with some sort of semi-objective measure (speed figures, for example) of the past performances of each horse in a race.

If we measure the level of a race by the median speed figure earned in that race (much more stable than the winner's figure), it is then easy to assign a level to any past race. (You do need to know the figures of all the horses that ran in that past race though, which won't be included in your standard pp's.) I have found (using my own personal speed figures) that a very good predictor of this number (before the race is run) is to take the median figure of the 3 most recent figures from each horse, and then use the median of those as your projected race level for today's race. This way you can see whether a horse is moving up or down based solely on the horses actually in the race because we've got a race level measure based on performance and a pretty good way to predict what this number is going to be beforehand. This is not neccessarily the best method, but the point is that you can devise a way to measure competive level based on the actual performances of the horses involved without even looking at the class designation.

Or you could just use par times based on the designation without bothering to look at the horses knowing.

Much much trickier to handle programmatically is the human element -- what did the trainer think when he entered the horse? If he is dropping in claiming price all else being equal I'm sure he considers it a drop. But when he is dropping in price but also going from state-bred to open company -- is that a drop, a lateral move, or even a raise? Again, we can decide based on the horses involved, but determining the intent of the trainer here is mostly art. Using par times is probably the easiest way to make an educated guess about what the trainer is thinking in unclear situations...

[delayjf]

If we measure the level of a race by the median speed figure earned in that race (much more stable than the winner's figure), it is then easy to assign a level to any past race. (You do need to know the figures of all the horses that ran in that past race though, which won't be included in your standard pp's.) I have found (using my own personal speed figures) that a very good predictor of this number (before the race is run) is to take the median figure of the 3 most recent figures from each horse, and then use the median of those as your projected race level for today's race. This way you can see whether a horse is moving up or down based solely on the horses actually in the race because we've got a race level measure based on performance and a pretty good way to predict what this number is going to be beforehand. This is not neccessarily the best method, but the point is that you can devise a way to measure competive level based on the actual performances of the horses involved without even looking at the class designation.


This seems to me to be a better way to determine class and project a
variant than the Class / Par method.

[KYJACK]

Right now, I find that determining whether a horse's performance involved a move up in class, down in class, or was at the same level as his previous race, is cumbersome, at least programmatically. I can scan the PPs, recognize the names of a few horses, and intrinsically know the answer. But dealing with this question programmatically is a bit more daunting.

Moves like going from maidens to winners and allowance to stakes are pretty straight-forward. Where my difficulties lie is in handling moves from claiming to starter or back, claiming to allowance (and optional claiming), an open $10K claimer to a conditioned $16K claimer, statebred allowance to open claimer, and the myriad of combinations possible.

I realize, of course, that one move from claiming to allowance may be a step up, but in another case might actually be a drop in the level of competition. I don't expect to be able to address that programmatically.

BTW, because horses easily ship to and from the racing circuits in my universe, races are written with very similar conditions for the tracks mentioned above.Bathless
The previous posts of GameTheory and delayjf strike me as a very interesting and possibly the best way to proceed in this day and age! That being said, if you still want to ponder the class level method, then here's a possible suggestion:

"Research how Par Times are actually computed and adjusted, to help you derive a Class Rating!"

Back in the 90s, when I was just starting the horse racing learning curve and being a programmer - I hit upon the idea of taking the various "Systems" that came in the mail (i.e. the ones that pushed your 'greed button' and gave you visions of early retirement! :lol: ) and programming the details into a computer program. Then, download a bunch of BRIS comma-delimited data files and results, to see if the systems actually work!

Typically, after eliminations, these systems would have you rate each horse on a point system, and then the highest valued horse would be the play for that race. Usually there was some type of Class rule -

"If the horse is dropping 1 level in Class, then award him 5 points. If dropping 2 levels, then award 10 points. If rising 1 level, subtract 5 points. And, if 2 levels, subtract 10 points"!

So, I ended up needing a numerical value of Class for each horse in the race. Code-wise for the above rule, using Visual Basic 6, it would be something like this:

Todays_Race_Class_Rating = Compute_Class(Current_Race)
Current_Horse_Class_Rating = Compute_Class(Current_Horse_Last_Race)
'*
'* Check for Class Drop
'*
If Todays_Race_Class_Rating < Current_Horse_Class_Rating Then
'*
'* Class Drop of at least 1 level detected!
'*
Horse_Rating = Horse_Rating + 5

If Todays_Race_Class_Rating < (Current_Horse_Class_Rating - 1) Then
'*
'* Class Drop of at least 2 levels detected!
'*
Horse_Rating = Horse_Rating + 5 '* (This makes 10 points total!)
End If
End If
'*
'* Check for Class Rise!
'*
If Todays_Race_Class_Rating > Current_Horse_Class_Rating Then
'*
'* Class Rise of at least 1 level detected!
'*
Horse_Rating = Horse_Rating - 5

If Todays_Race_Class_Rating > (Current_Horse_Class_Rating + 1) Then
'*
'* Class Rise of at least 2 levels detected!
'*
Horse_Rating = Horse_Rating - 5 '* (This makes 10 points total!)
End If
End If
The key item is the routine "Compute_Class". We use it for the Track level of the current Race and the Track level of the Horse's Last Race (Might be a shipper!). So, first off you need some sort of table relating the various tracks across the country. Take a quick peek at American Turf Monthly's Track Equalization Chart. http://www.americanturf.com/equalization/index.cfm (http://www.americanturf.com/equalization/index.cfm), whereby each track is assigned a rating. So using the above logic, a shipper from Santa Anita to say Charlestown (yeah right! :lol: ) in a sprint, would experience a class drop! (a really big drop! -21 levels just on Track Class alone!) Conversely, if he ships to Bay Meadows, that's a Class Rise (+2 levels)! This is very basic, but it gives you an idea of the process. Various details such as race classifications and adjustments (e.g. Age, State-breds, Sex, Purse, etc) must also be considered!

Also, during this time frame, I started to play with the Thorobrain Software. In addition to their software they were selling some interesting reference materials. I ended up buying one such item:

Understanding & Applying Par Times
"A Practical Approach to Adjusting Pace Lines"
By David E. Schwartz (C) 1992

This book turned out to be the "Holy Grail" for me in deriving a numerical rating for a horse's Class in a given race! I was then able to code a completely programmatical method for Class (at least with respect to my newbie understanding of the issues involved :rolleyes: ).

First, there were two key "Track Class" tables - A main table relating each track and the non-claiming race classifications levels such as MSW, NW1, NW2, etc, to a numerical value. The second table was a Claiming Class table assigning a class rating based upon the Claiming price Structure. Back then, the $10K level was considered universal across all tracks. Today, however that has changed a bit due in part to the "Racinos" at cheaper tracks inflating the purses! (Interesting reading: Claiming Races and the Racino Tracks http://www2.als.edu/glc/wagering/racino.html (http://www2.als.edu/glc/wagering/racino.html) ). Dave also makes reference to this on his website (see below for the link). Using the two tables, one can relate the various race classification levels between tracks!

Next, adjustments for Maidens, Females, month of today's race, age, distance were usually shown to be various set offsets from the Track Class rating. Thus a total final Class figure specific to each horse in today's race could then be obtained!

Here's two additional references that were instrumental in my understanding of class ratings:

1. "The Handicapper's Condition Book - An Advanced Treatment of Thoroughbred Class" by James Quinn 1986 ISBN: 0-688-05931-7
2. "ThoroBrain IV Reference Manual" - Page. 1-30 & Help Screen on "Class Level"

The Thorobrain folks have closed up shop. The above reference materials might be available on ebay or other sources, but take a peek at Dave's Horse Street Handicapper (HSH) website - http://www.horsestreet.com/ (http://www.horsestreet.com/)). In addition to selling his flagship software product, he's also selling Par Times. Look closely at the ad copy, for that's the key for what you're interested in! -

"These are the pars that everyone is talking about. Our pars have quickly become the industry standard. If you are interested in comparing times from distance-to-distance or track-to-track, these are a must have."

Although in pars you're comparing times, along the way you're also delving into Class! Take a big look at the his web page Understanding Par Times http://www.horsestreet.com/products/pars/2005Pars/index.html (http://www.horsestreet.com/products/pars/2005Pars/index.html). It's a gold mine of information! Although the "prime objective" is to sell the Par times, topics such as "Adjustments|Track Class Levels", "Make you own pars", and others should give you some ideas. Check with Dave about the "Class Pars Book" http://www.horsestreet.com/products/pars/index.html (http://www.horsestreet.com/products/pars/index.html), to see how much of the old book's info might be included. Notice the numbers in the section titled "A Page from the Class Booklet"! Looks like a great start, if not just what you need for your Class Levels! Conceivably you could use these numbers as the basis for your own class ratings! Could save you a lot of coding and updating, rather than doing everything from scratch!

In any case, I hope my reply, albeit long-winded helps you along the path! Good luck!

Jack

[Dave Schwartz]

Bathless,

(What a name!)

Dang, you mean people actually read this stuff?

This link might help you as well:
http://www.horsestreet.com/products/pars/2005Pars/index.html

Also, we actually have a format in our par times file that addresses class level quite functionally.

Here is a link to the comma-delimited par formats (of which there are 4). If you can handle a relational model, the "Hi-Tech" would likely be best for you. If not, consider the "Kearns."
http://www.horsestreet.com/package/Users/Apr01/Samples/


BTW, I do not suggest that you try to separate things like "non-winners at a mile or over in the last 90 days" from the same condition in 150 days. That will just make you crazy. Just call them "classified allowances" and be done with it.

Finally, with or without our pars, we will be happy to consult with you (fee-based, of course) and help you design what you need.


Regards,
Dave Schwartz