DataBase Question ...

[Notso]

I have accumulated Race Charts from Axcis and Equibase for the past 4 years.
There's a lot of data sitting on my hard drives.
I want to develop a database with this information.
I have many ideas that I need to organize ... but hesitate in doing so.
I want to finish what I start.

I use Windows 2000 and Office 2000.

Is the Access 2000 DataBase powerful enough to handle four year's data and my ideas?

NotSO

[pantoner]

I have accumulated Race Charts from Axcis and Equibase for the past 4 years.
There's a lot of data sitting on my hard drives.
I want to develop a database with this information.
I have many ideas that I need to organize ... but hesitate in doing so.
I want to finish what I start.

I use Windows 2000 and Office 2000.

Is the Access 2000 DataBase powerful enough to handle four year's data and my ideas?

NotSO

Access has a 2GB file size limit. So the answer is probably no.

That being said, if you do not have experience with something more robust, Access is the best place to start.

[Tom]

What kind of charts from Trackmaster do you have?
I used to use them all the time and have set up some db's using thier charts and the free track profile software. If you have the comma charts, I can show you what I did ( I use other charts now, but the track profiel program was a big help, as it set up various db's from the chart files that I used to get the data I wanted.

[Notso]

Tom,
I acquired Trackmaster's Comma-Delimited (thoroughbred only) Race Charts.
Some of the files ... aqu1205.a04 ... cd0507.a05 ... suf0816.a05
Do these look familiar?

[Donnie]

You may consider breaking it out into 4 different databases, one for each year. Then use a 5th database to link the others' tables into to run your queries and reports from. Do you have any experience with Access?

[sjk]

I have far more than 4 years in an Access database.

You will want to design so as to avoid duplication of information.

[Tom]

Tom,
I acquired Trackmaster's Comma-Delimited (thoroughbred only) Race Charts.
Some of the files ... aqu1205.a04 ... cd0507.a05 ... suf0816.a05
Do these look familiar?


Yup - exactly.
Do you have the track profile program? It's FREE. And worth every cent!;)
Once you load the files into it, it creates a database - several actually.
The data is easy to take out and put into a custom one.
I used it up until the chart parse program came out, then when the charts changes, HTR came out wioth charts and I am there now.

(It is really fun to try to merge data from three different sources!)

Let me know if I can be of any help.

One point, TM changes their variants and speed figures - so older data might have to be massaged to fit with the newer stuff--no biggie, if you know what date it occurred. 46/Richie...do you know what date the change went down?

[Notso]

You may consider breaking it out into 4 different databases, one for each year. Then use a 5th database to link the others' tables into to run your queries and reports from. Do you have any experience with Access?
Donnie,
Many year's ago, I worked my theories into Access '97.
Because there was NO internet back then, I manually inputted 2 year's of Maryland's racing data into my Access DataBase.
I have not Access'ed much since but have collected tons of data.
I appreciate your input.
NotSO

[Notso]

I really appreciate your expertise and input.

You have helped me tremendously.

NotSO

[BCOURTNEY]

I have accumulated Race Charts from Axcis and Equibase for the past 4 years.
There's a lot of data sitting on my hard drives.
I want to develop a database with this information.
I have many ideas that I need to organize ... but hesitate in doing so.
I want to finish what I start.

I use Windows 2000 and Office 2000.

Is the Access 2000 DataBase powerful enough to handle four year's data and my ideas?

NotSO

I would recommend using the SQL 2005 Express version from Microsoft. It has all the power of a "real" SQL server with a limitation on space of 4 gigs. It is accessable through a management interface and query window, can perform import and export operations to and from all common formats like txt,dbf,access,excel,etc. Plus if you ever need to upgrade or have your data hosted offsite ( in a facility that will host it online and back it up for you etc ) you can.

B

[thelyingthief]

I would recommend using the SQL 2005 Express version from Microsoft. It has all the power of a "real" SQL server with a limitation on space of 4 gigs. It is accessable through a management interface and query window, can perform import and export operations to and from all common formats like txt,dbf,access,excel,etc. Plus if you ever need to upgrade or have your data hosted offsite ( in a facility that will host it online and back it up for you etc ) you can.

B

this guy sounds like he knows whereof he speaks, but he doesn't communicate it well, if he in fact does. you CAN use sql server and retain access as the front end, and not know you're using the server. simple as pie. not that you should want to: access is a db system used by many small to medium size manufacturers to maintain large amounts of data, vastly more than you ever will. access is very, very good, but like all software, the user's ignorance is the chiefest problem. it is NOT necessary to learn sql whatsoever. perhaps it would be nice to do so, but who has that kind of time? again, it ain't necessary. besides, almost anything you need to do, e.g. populating a track profile db with energy exertion parameters using the data from your charts, can be accomplished with macros. sophisticated users know a programming language, as that is necessary for flexible implementation of a strategy, and many of the things you may want to do--like automatically populating a db, fetching it off the internet, etc.--require it. however, handicappers rarely require that depth, and can pay someone to set that up for them in relatively quick fashion for a modest fee.

relational db systems perform better when data is segregated into like material, and merged using keys where needed. this is the primary use of the query. while access IS limited by size, a correctly designed db for your charts should work fine for YEARS and YEARS and YEARS of data.

i don't know about trackmaster's charts, but other suppliers already have their zip files segregated into component elements, so that you are not importing one db, but several. there are a lot of heavy weights here in db design and maintenance, and you should do a search on your question. you'll soon find out who they are.

tlt

[socantra]

One point, TM changes their variants and speed figures - so older data might have to be massaged to fit with the newer stuff--no biggie, if you know what date it occurred. 46/Richie...do you know what date the change went down?

February, 2006.

They changed the scale, so that each point represents half as much time as it used to.

[Tom]

Thanks.

[pantoner]

It is accessable through a management interface and query window, can perform import and export operations to and from all common formats like txt,dbf,access,excel,etc.
B

You are right SQL Express is a good solution. But, NotSO sounds like he may be a bit inexperienced and SQL Server requires some tweaking to make it work. Also, the SQL Server Managment Studio EXPRESS does not support import/Export. Just type in that sentence into google and you will read all of the complaints. You can get the data into a table using other methods but it's not for amateurs.

a correctly designed db for your charts should work fine for YEARS and YEARS and YEARS of data.


The key phrase is "correctly designed" if you are not so good at writing queries than you might have to make a lot of tables as intermediate steps and that will increase your database size. You can believe me because I learned this the hard way and it only took me about 6 months to hit the 2GB limit. Also, not all of the Horse Racing data adheres to normal form. So if you want to correctly design your tables you should muscle through this stuff below.

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

I also recommend reading Database Design for Mere Mortals.
http://tinyurl.com/3wmjck

When I first read it I thought that it was boring and that I got nothing out of it but I have been using the principles that I learned from this book everyday since.

[sjk]

In my experience lots of intermediate tables is the only way to get anything reasonbly complex done in a finite amount of time.

You can make the intermediate tables in a separate database and link to the results. In their own database these have their own 2gb.

I have managed to stay on the right side of the size limitations for 15 years but during most of that time I have been aware that care must be taken to avoid having a problem.

For what its worth whenever you do breach the size limit it would appear that the database is broken beyond repair but you can create a new database and import all of the objects from the one that is too large to avoid losing your objects.

[pantoner]

In my experience lots of intermediate tables is the only way to get anything reasonbly complex done in a finite amount of time.

For me too. Then I switched to SQL Server and started paying people to write queries for me. The pros can put it all in one query and its worth a few bucks to save all the days of trying to figure it out myself.

[BCOURTNEY]

To the OP, if you are interested in creating a database structure that does exactly what you want it do, it's best to contract the services of someone that is familar with UML modeling techniques and can produce the required requirement and design documents, and UML diagrams to give to a programmer to impliment your database in whatever back end / front end combination that you use. Some people have the luxury of being good designers and programmers although this is not usually the case. I have made the bold assumption that your investment in the data totals in the thousands and that you are willing to invest more to get the system that you want. If you contract services, it would proabably be helpful for you to "draw" or write out the GUI screens or reports that you want.

[BCOURTNEY]

You are right SQL Express is a good solution. But, NotSO sounds like he may be a bit inexperienced and SQL Server requires some tweaking to make it work. Also, the SQL Server Managment Studio EXPRESS does not support import/Export. Just type in that sentence into google and you will read all of the complaints. You can get the data into a table using other methods but it's not for amateurs.



The key phrase is "correctly designed" if you are not so good at writing queries than you might have to make a lot of tables as intermediate steps and that will increase your database size. You can believe me because I learned this the hard way and it only took me about 6 months to hit the 2GB limit. Also, not all of the Horse Racing data adheres to normal form. So if you want to correctly design your tables you should muscle through this stuff below.

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

I also recommend reading Database Design for Mere Mortals.
http://tinyurl.com/3wmjck

When I first read it I thought that it was boring and that I got nothing out of it but I have been using the principles that I learned from this book everyday since.

The largest most common complaint regarding SQL Express is its apparent "lack" of the DTS (Data Transfomation Services) interface that is a standard part of the regular pay-for version of SQL Server. If you investigate more, most of these compliants are baseless, and DTS Import/Export can be installed and used just fine with SQL Express, it takes about 10 minutes to download install and configure, but it is easy to complain about I guess, since it requires the user to actively do something, they should probably have it as part of the install, but they don't. More "google" on DTS Import/Export solves whatever this issue is, and worst case, I could explain it in private messages if needed.

Cheers,
B

[gm10]

If you are willing to invest in a bit of IT infrastructure, I would recommend Oracle as it's the best relational database system and it's free for non-commercial use.

And you will need to learn SQL if you want to get the most out of your database, but any graphical query builder will do while you're mastering SQL.

Trackmaster changed its speed ratings end of Jan 2006 I believe.

[Notso]

Recently, I have been remiss.
Reading everone's posts and words of expertise, I sat done and 'JUST DID IT!'.
I 'DID' start on my Access database and am pleased with what I presently have ... (45 days of Belmont's and Hollywood's Spring meets).

My PaceFigures ROCK !!!

Thanks Again,
NotSO

[Tom]

:ThmbUp:

[podonne]

Just to throw this out because it wasn't mentioned before, MySQL has worked very well for me over the past few months to implement a database-driven handicapping method. Its free for just about everybody, lots of open source graphical interfaces, and works really well on Windows-based machines with no problems.

On a Win32-NTFS file system the maximum table size is 2 terrabytes (about 2,000 GB), and if you split the input file into logical parts (one table for PPs, one for entries, one for the horse, one for the race, one for workouts), then well I guess you could probably fit the whole goddamn world in there.

I even bought it a standalone server, parts only cost $200 from a local Chicago PC store.

[richrosa]

MySQL is the jam for managing any type of handicapping data. Its free and it does everything you could possibly need.

On a side note, if anyone is interested in a licensed replication of racing data, please PM or contact me. This is not a product offer, rather some information gathering before a product is issued, which may very well be imminent.

[raybo]

MySQL is the jam for managing any type of handicapping data. Its free and it does everything you could possibly need.

On a side note, if anyone is interested in a licensed replication of racing data, please PM or contact me. This is not a product offer, rather some information gathering before a product is issued, which may very well be imminent.

What do you mean by "replication of racing data"?

[raybo]

MySQL is the jam for managing any type of handicapping data. Its free and it does everything you could possibly need.

On a side note, if anyone is interested in a licensed replication of racing data, please PM or contact me. This is not a product offer, rather some information gathering before a product is issued, which may very well be imminent.

In MySQL, do you have to import data from a spreadsheet, etc., or can comma-delimited data be directly imported into it?

[felinis]

These charts tell you a lot about the starters in the race, but have no data on the betting/payoff details of the race. They only tell you the purse.

If you have a betting system that you want to test against real-world data, TrackMaster's charts lack details on the payoffs for each race.

If you are only interested in evaluating the abilities of the horses in an upcoming race, wouldn't you be better off analyzing PPs?

[richrosa]

In MySQL, do you have to import data from a spreadsheet, etc., or can comma-delimited data be directly imported into it?


Sorry Raybo, I missed this question last week.

You can import comma-delimited directly into MySQL. You will want to do some restructuring work to normalize the data, which could be done using SQL (harder) or another programming language on the way in. Either way with the MySQL power, you will need to add some SQL skills to get the data that you'll probably want.

My argument is that for all the messing around people do with Access, they could have learned the SQL basics much faster.

[Ted Craven]

These charts tell you a lot about the starters in the race, but have no data on the betting/payoff details of the race. They only tell you the purse.

If you have a betting system that you want to test against real-world data, TrackMaster's charts lack details on the payoffs for each race.


In the CSV format chart files, for each horse row (H) check the final 4 columns for WPS mutuels (36 - 39), and for Exotic mutuels check the race row (R) columns 42 - 81.

For closing Odds for each runner, check column 13 in each horse's row (H).

Here's the CSV format data schema: http://www.trackmaster.com/track/info/chtcomma.txt

Here's the XML format schema: http://www.trackmaster.com/xmlSchema/tchSchema.xsd

Good luck!

Ted

[RobW]

Sorry Raybo, I missed this question last week.

You can import comma-delimited directly into MySQL. You will want to do some restructuring work to normalize the data, which could be done using SQL (harder) or another programming language on the way in. Either way with the MySQL power, you will need to add some SQL skills to get the data that you'll probably want.

My argument is that for all the messing around people do with Access, they could have learned the SQL basics much faster.

Hi all, I'm new here. If you need to manipulate/restructure/normalize your data while uploading, or even do calculations and what have you, I'd recommend LogParser 2.2. Extremely powerful tools, and FREE (from Microsoft, go figure...). You can use it to work with text files, excel files, access, sql (any flavor, as long as you have the ODBC driver and a data source configured) or pretty much anything else.

Hope not too high-geek a contribution ;->

--Rob W.

[equicom]

What do you mean by "replication of racing data"?

He must have missed the question, but while you're waiting for confirmation, what I think he means is to have a legal right to re-sell the data via a licensing scheme with the original provider of the data.

To professionals that work with databases, the term "replicate" has an entirely different meaning, and it is a way of ensuring data integrity between updates (but I am fairly sure he was not referring to that!).

:confused: