Analytical DB design?

[CBedo]

I've designed relational dbs before (small ones) for business reporting, and financial planning, but none were truly analytical databases. I was just wondering what tips in db design you guys would give (or things to avoid) in designing my handicapping info db which will be used primarily analytically?

Also, I've been using the Brisnet drf (single) files and charts. If any of you guys would be willing to help me with table structures or other design tips using this data (or convince me to use something else--I'll listen), I'd be very grateful.

Thanks for everything you all contribute at PA,

CBedo

[Tom]

Keep your fields simple.
For example,. BRIS throws way too much in some fileds to make them have limited value.

c-fCl12500 should be three fields, c- claimed, f = fill race, cl12500 claiming 12,500.

f-alw44000nw1x - everytime the purse value changes, you have another level allowance race. More fields are better than one in many cases.

[podonne]

I've ben working with a big db of BRIS/TSN files for a year now to do analysis so I can say that all of the standard database design concepts you are familiar with are applicable here.

This is my table structure:
Card
Race
Entry
Horse
PP
Workout
Result (for the .xrd if you have that)

Plus a bunch of intermediate tables to make the analysis faster.

Hope that helps..

[robert99]

CBedo,

I have a 40 page chapter on analytical horse racing database construction within my "Racing Tutorial". The chapter is in English and uses UK and Ireland examples. PM me if you want a copy.

Robert

[Notso]

send me a note and I will forward you a couple files.

(have one small complaint ... the beaten lengths are not entirely accurate)

oh well ... NotSO

[CBedo]

Thanks for all the help so far. One of the issues I was thinking about was query/analytical speed. I know in general it's best to design the db in such a way as to cut down on duplication, but I was wondering if some duplication of data in mutliple tables might help the speed? I'm more worried about speed than size of the db as I'm designing this monster.

Thanks for your thoughts.

[sjk]

Not a bad idea for some information to be duplicated.

One of my main tables shows all of the running lines for all of the horses but that is a big table organized by date. If you have a list of horses running today and you want to retreive the last 5 starts for each of these horses that is not so easy to do using the big table.

I keep a smaller table organized by horse name and date with a few key pieces of information that can be resorted every day which makes the data retrieval above very simple.

Of course other information which is calculated such as track variants can be calculated and stored in a table so that you don't need to recalculate these every time you need a speed figure.

[njcurveball]

Thanks for all the help so far. One of the issues I was thinking about was query/analytical speed. I know in general it's best to design the db in such a way as to cut down on duplication, but I was wondering if some duplication of data in mutliple tables might help the speed? I'm more worried about speed than size of the db as I'm designing this monster.

Thanks for your thoughts.

I know this is probably not the answer that will help you, but there may be one brave soul who reads this and gives it a try. I have an Oracle Database and a program set up to basically handicap every horse in every race with many variables. I also have many race variables as well.

I re-worked some formulas last night. It took about 3 minutes to work over 500,000 records.

Queries on that size usually take about 30 seconds. I could play around and create some indexes and "tune" it to be faster.

What kind of speed are you looking for? Will you be using Access?

Jim

[sjk]

My Access database handicaps every horse in every race in 2-5 minutes depending on the number of tracks running.

[njcurveball]

My Access database handicaps every horse in every race in 2-5 minutes depending on the number of tracks running.


I guess I should probably clarify that in 3 minutes I handicapped every race in the past year and a half and wrote those values to tables for reporting and querying later.

I would not know what to do if I had to wait 2 minutes just to do 1 race. WOW! I thought Access was faster than that? :bang:

[sjk]

Guess I should clarify.

In 2-5 minutes I handicap all of a day's races at every track running. On the weekend with 40+ tracks running it takes 5 minutes.

To handicap all of last years races it would take hours. But then who would take the bets?

[njcurveball]

To handicap all of last years races it would take hours. But then who would take the bets?

So basically you keep 1 or 2 days in your database and delete the rest after the races are run?

You can't bet them, right? ;)

[sjk]

I have over 500k races in the database dating back to 1993 and it is complete for all practical purposes back to about 2001.

I was talking about the process of handicapping a day's card and calculating odds lines for betting purposes. That is what takes 5 minutes.

If I recalculate all of the track variants for all of the races in the database (I do this every week or two to capture information from horses that run back) it takes about an hour and a half.

Of course I do bet them. That is the whole purpose for doing it.

[CBedo]

I haven't locked in on a specific db yet. I had used Access for quite a while to store my poker hand histories, but it became slow (old Access version, not sure if it's any better now). I convertted that database into a PostgreSQL db and it queried much faster. So that is one I'm considering. I'm definitely open to suggestions! I figure I might as well make good (or at least well informed and intentioned) choices now in the design phase before I implement anything.

[BillW]

I haven't locked in on a specific db yet. I had used Access for quite a while to store my poker hand histories, but it became slow (old Access version, not sure if it's any better now). I convertted that database into a PostgreSQL db and it queried much faster. So that is one I'm considering. I'm definitely open to suggestions! I figure I might as well make good (or at least well informed and intentioned) choices now in the design phase before I implement anything.

I hesitated to answer your post because I (mistakenly) assumed you were talking about Microsoft databases. I've used postgres for quite a few years and have no problem with query speed into multiple tables, complex WHERE clauses (maybe a dozen or so) etc. I'd say there is no need to duplicate data but I've never had queries go slow enough to really test it. One note: The database runs markedly faster when it is hosted on a SCSI drive vs. an IDE drive. This is running on Linux, I would imagine your mileage may vary GREATLY running under Windows.

[sjk]

I don't think the Access software is any faster than it was 15 years ago although the hardware you run it on certainly is.

If something runs slowly there may be an alternative way to deal with the problem which will be faster.

When I started using Access it took all of 3 weeks to build up to queries complex enough that they just would not run. However in 15 years I have always found ways to work around these issues.

[Tom]

Sometimes my Access runs ( crawls?) very slowly with rather small queries - say 300 or so records, 5 fileds! And other times, it runs very fast with 5 years of data and thousand of records. Go figure.

[richrosa]

With Postgres and MySQL being FREE enterprise databases with an awesome set of tools, never limiting, and scalable, but simple enough for a newbie to use.

I can never recommend using Access which is a subset of a true RDBMS, and clearly not enterprise. Its a toy compared to the Postgres and MySQL, plus you have to pay for it.

[delayjf]

Its a toy compared to the Postgres and MySQL

I assume one needs to be able to program in SQL for it to work?

[richrosa]

I assume one needs to be able to program in SQL for it to work?

The Access guys are going to try and flame me for this.

If you are going to get serious with horseracing data and get beyond a few thousand cards and try to build models or do other analysis, you are going to need to use SQL to get the right data quickly. At some point the Access pretty UI starts to become an impediment by not giving you enough direct power to do proper queries on a normalized, indexed data source. If you query ever takes less than 1/10 of a second, something is wrong with your database or programming.

You will spend more wasted time learning Access that could be put towards learning SQL (the world standard for manipulating databases), which is a transportable skill, unlike Access which is proprietary (and almost useless).

[CBedo]

Rich, that is what I assumed, and thought I'd skip the Access (baby step) stage. The problem with me learning SQL right now is Del Mar and Saratoga running!

I have a computer science degree, but it is from the dark ages (early nineties), when we thought Turbo Pascal was powerful, hehe. And I haven't used that part of my education much in a long time. I recently purchased the book SQL for Mere Mortals (the original DB book I thought was pretty well written), but I'd love to hear what you (and anyone else) would recommend to me to kickstart my SQL learning curve--book, online course/tutorial, or whatever else would help.

Luckily for me, I do have a computer programmer friend that I have recently gotten interested in horse racing (I'm taking him to his first live race on Friday at Arapahoe Park), and he can help me to some extent. (It might be a sign of the apocalypse that racing has a new fan under the age of 35! I can't wait for to get him standing by the rail when they come down the stretch.)

[richrosa]

You picked a fine SQL book. There other more advanced books in that series, but you don't need them now. I would pick from the many MySQL books to get MySQL specific information next, although the wealth of information on the internet is vast and is probably quadruple the amount of free information available for Access.

Plus, I didn't need to ask you what platform you run. MySQL runs on them all. Access is targeted to run only on Windows (although it can run under wine, but nobody does that for Access).

[Barremill]

The Access guys are going to try and flame me for this.

If you are going to get serious with horseracing data and get beyond a few thousand cards and try to build models or do other analysis, you are going to need to use SQL to get the right data quickly. At some point the Access pretty UI starts to become an impediment by not giving you enough direct power to do proper queries on a normalized, indexed data source. If you query ever takes less than 1/10 of a second, something is wrong with your database or programming.

You will spend more wasted time learning Access that could be put towards learning SQL (the world standard for manipulating databases), which is a transportable skill, unlike Access which is proprietary (and almost useless).
================================================== ====
Rich, the Access guys haven't responded atoll(i am NOT an Access guy!) but did you mean to write "if your query ever takes MORE than 1/10 of a
second something is wrong with your Database or programming? but my question is if you were querying a DB with a 100,000 races and the query was a modest 20 'rule/factor' query, how does the 1/10 of a second 'elapsed time' for a lack of a better description come into play? i must be missing something as i would figure the query would take minutes. please explain your reasoning as i am not getting it. of course i know nothing about MySQL either;-) thanks Rich.
BarreMill

[richrosa]

Rich, the Access guys haven't responded atoll(i am NOT an Access guy!) but did you mean to write "if your query ever takes MORE than 1/10 of a
second something is wrong with your Database or programming? but my question is if you were querying a DB with a 100,000 races and the query was a modest 20 'rule/factor' query, how does the 1/10 of a second 'elapsed time' for a lack of a better description come into play? i must be missing something as i would figure the query would take minutes. please explain your reasoning as i am not getting it. of course i know nothing about MySQL either;-) thanks Rich.
BarreMill

No problem. That's a great question from an Access point of view. An Enterprise RDBMS like MySQL will have many different storage systems and more importantly indexing schemes that will optimize queries for you. Indexing essentially provides the database with a quicker way of organizing the keys of data that are generally searched upon.

The time a query takes in a an Enterprise RDBMS is still somewhat dependent on its size, but when indexed properly, executed with a well written and structured query (not the GUI mess that Access gives you) a query written against 10 million records should take just slightly (millseconds) longer than one run against 10,000 records.

This is one major disadvantage of desktop handicapping software. Most, if not all use the MS Jet engine, which is basically the same engine that powers Access. It does not have the powerful indexing tools that I speak fof, therefore their performance starts to degrade significantly after many records are thrown at it.

I hope this helps you save time from waiting on Access queries.

[richrosa]

This just came up this morning. On my development system, all of the sudden my PP's are taking forever to render. A quick code profile leads me to a problem in my workout table. I profile the query and see that its not using the index correctly (which is the tool that databases use to distinguish between millions of records to zero in on that data that you are looking for), and its doing a full table scan to pull out the last 10 workouts for a single entry. A quick rebuild of the index, as it was damaged, yields me the lightning quick performance that I expect.

The workout table has 6.4 million rows. With an index my query takes 10 milliseconds, without the index it takes 10 seconds.

Access does not have such a robust indexing system.

[podonne]

This just came up this morning. On my development system, all of the sudden my PP's are taking forever to render. A quick code profile leads me to a problem in my workout table. I profile the query and see that its not using the index correctly (which is the tool that databases use to distinguish between millions of records to zero in on that data that you are looking for), and its doing a full table scan to pull out the last 10 workouts for a single entry. A quick rebuild of the index, as it was damaged, yields me the lightning quick performance that I expect.

The workout table has 6.4 million rows. With an index my query takes 10 milliseconds, without the index it takes 10 seconds.

Access does not have such a robust indexing system.

Rich,

Can you post the specs of your mysql system? I set up a dedicated server a few months ago but it's not the fastest processor around. I'd love to get a sub 10ms responce.

Thanks,
Phil

[richrosa]

Rich,

Can you post the specs of your mysql system? I set up a dedicated server a few months ago but it's not the fastest processor around. I'd love to get a sub 10ms responce.

Thanks,
Phil


I prototype on a Centrino Duo 1.6 Ghz. Production is on a Pentium 900Ghz machine. A very modest box indeed. Hardware helps, but better indexing and queries help more.

If you are not getting a 10ms response, try the "EXPLAIN" function to analyze your query to ensure that its using the indexes that you setup properly.

[podonne]

I prototype on a Centrino Duo 1.6 Ghz. Production is on a Pentium 900Ghz machine. A very modest box indeed. Hardware helps, but better indexing and queries help more.

If you are not getting a 10ms response, try the "EXPLAIN" function to analyze your query to ensure that its using the indexes that you setup properly.

Hmmm. You're right, not much different from my setup. I suppose I have some optimizing to do...

[Charli125]

Guys,

Please be patient, I'm a newbie.

I am very familiar with SQL, and have mySQL installed at home. I can make it do whatever I want once I get the data into the database. My problem comes when trying to get the data into my database. Manually entering everything defeats the purpose, and I know it's probably a very simple thing to do.

On drf I see several types of data files that I can download, but it's all very confusing. Can anyone simplify a method on how to take one of the data files(and specify which type please), and get it into a database? When it comes to formulas in Excel and SQL I feel good, but the import part is driving me mad.

Thanks,
Charlie

[richrosa]

Guys,

Please be patient, I'm a newbie.

I am very familiar with SQL, and have mySQL installed at home. I can make it do whatever I want once I get the data into the database. My problem comes when trying to get the data into my database. Manually entering everything defeats the purpose, and I know it's probably a very simple thing to do.

On drf I see several types of data files that I can download, but it's all very confusing. Can anyone simplify a method on how to take one of the data files(and specify which type please), and get it into a database? When it comes to formulas in Excel and SQL I feel good, but the import part is driving me mad.

Thanks,
Charlie

Try the LOAD DATA FILE command

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Of course, a little PHP will help you massage the data on the way in. Also, I can recommend almost all of the MySQL front ends that can help automate some tasks for you, even though PHP is the best way.

[michiken]

Although I am not a professional programmer, I cobbled this php script to import bris single data files into mysql. At least you can read thru the code and possibly get an idea of how powerful php is... feel free to modify and use.

I do not extract every field. I just extract the bris pace and speed figs as shown in the attached report.

P.S. I use php to automatically check for the tables, if they do not exist, then it is created for you.

Rich Rosa also has released an open source program that can do a much more efficient job.

[Charli125]

Perfect, thanks for the info. I'll give your php script a shot, and the one from Rich Rosa.

Thanks.

[podonne]

I'm going to have to wait until later on to examine the php script, but I know the bris/tsn data files are too large to import as/is. MySQL ( and probably others) have a 1,204 limit on the number of columns per table. You've got to at least split out the PPs into a separate table, and probably the workouts as well.

[CBedo]

Rich and Mich (or anyone else with an opinion),

PHP instead of Ruby on Rails? Is there an advantage?

[richrosa]

Rich and Mich (or anyone else with an opinion),

PHP instead of Ruby on Rails? Is there an advantage?


Ruby has a smaller community, is more cutting edge, and has a steeper learning curve, however as a framework to build apps, its top notch.

PHP is easier for the casual programmer, which means its easier to program it badly, had a large community, a huge internet knowledge base and can do almost all of the things Ruby can. Symfony is a real good framework written in PHP that approximates what Rails is supposed to be. The learning curve is steeper though, unless you're learning from scratch, which might make it a little easier.

[Barremill]

Rich,
browsing a MySql book at the bookstore, it said that there were 3 programming languages that can be used with MySql and these were PHP, PERL and PYTHON? what 'qualifies' a programming language to be eligible for use with MySql? am i right in saying that MySql as a standalone has NO
such thing in it's syntax as the IF/THEN/ELSE? what about such things comparable to scanning a text field like one can do in(Gasp!) BASIC with the INSTR instruction? Thanks Rich.
BarreMill

[michiken]

I'm going to have to wait until later on to examine the php script, but I know the bris/tsn data files are too large to import as/is. MySQL ( and probably others) have a 1,204 limit on the number of columns per table. You've got to at least split out the PPs into a separate table, and probably the workouts as well.

MySQL has a maximum column limit of 4096 (http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html)

[raybo]

It's still hard for me to believe that no free db template exists for a basic handicapping db. I tried in an earlier thread to get one going but couldn't get enough assistance, that was understandable for my very limited knowledge, to continue.

[podonne]

MySQL has a maximum column limit of 4096 (http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html)

I stand corrected, thank you. I should have qualified that the limit applies to the InnoDB engine. Perhaps my original source had the same confusion.

[podonne]

Rich,
browsing a MySql book at the bookstore, it said that there were 3 programming languages that can be used with MySql and these were PHP, PERL and PYTHON? what 'qualifies' a programming language to be eligible for use with MySql? am i right in saying that MySql as a standalone has NO
such thing in it's syntax as the IF/THEN/ELSE? what about such things comparable to scanning a text field like one can do in(Gasp!) BASIC with the INSTR instruction? Thanks Rich.
BarreMill

MySQL does have a built in language, look up Stored Procedures, but you might find it unwieldly to create your entire anlaytical engine there.

There are many, many languages that can access MySQL, although I think your statement might have been qualified as "nativley". Any of the .Net languages can access the ODBC framework and plug in. You need the 3.51 ODBC Connector, search the MySQL site.

If you like Basic (I do) try Visual Basic 2008 express edition. Its free!

[podonne]

Rich,
browsing a MySql book at the bookstore, it said that there were 3 programming languages that can be used with MySql and these were PHP, PERL and PYTHON? what 'qualifies' a programming language to be eligible for use with MySql? am i right in saying that MySql as a standalone has NO
such thing in it's syntax as the IF/THEN/ELSE? what about such things comparable to scanning a text field like one can do in(Gasp!) BASIC with the INSTR instruction? Thanks Rich.
BarreMill

MySQL does have a built in language that can do lots of things, look up Stored Procedures, and of course if you get the source code you can do anything C can do, but you might find it unwieldly to create your entire anlaytical engine there.

There are many, many languages that can access MySQL, although I think your statement might have been qualified as "nativley". Any of the .Net languages can access the ODBC framework and plug in. You need the 3.51 ODBC Connector, search the MySQL site.

If you like Basic (I do) try Visual Basic 2008 express edition. Its free!

[njcurveball]

It's still hard for me to believe that no free db template exists for a basic handicapping db. .

HTR has many access templates for the data that is exported from the program. I would guess other software packages have similar.

[Charli125]

It's still hard for me to believe that no free db template exists for a basic handicapping db. I tried in an earlier thread to get one going but couldn't get enough assistance, that was understandable for my very limited knowledge, to continue.

I feel the same way. Fortunately I have a friend who is a wiz with php and a host of other languages, and I gave him a call last night. So last night he setup the mySQL db and used Michiken's php script as a base for a script to import .drf files.(Many thanks, I was having a hard time describing what it needed to do) So far so good. I basically upload the file to an ftp site, log into a page that's linked to the database, click process, and it's in the database. FYI, you don't need to have a website setup, you can just as easily do it on your own computer. Since my friend lives on the East Coast, and I live on the West Coast, this was the only way we could both access it.At this point I can run queries on the db, but only using mySQL.

The next step is to customize a front end for it. It might take us up to a month to get it finished, but once it is, I'll make sure to upload it for all of you. I sure wish I had better programming skills because it's really a very simple database that I should've been able to do on my own. Rich, I believe the front end he said we should use is navicat. Have you heard of that one?

[Barremill]

Hi Poddone,
if MySql doesn't contain the IF/THEN/ELSE syntax then it would be USELESS
to me for what i may want to accomplish, which would be to write queries
to select plays and produce those reports for all the HITS. so using a front-end programming language for doing this may be the only way to accomplish this. as far as learning/using ACCESS, from what Rich sez there is no comparison in the SPEED of queries dept, so i wouldn't want to use ACCESS and be MUCH slower. but ACCESS has the advantage of being a DB-oriented kind of software where it can be better used as a standalone. Waddaya Tink?
BarreMill

[Charli125]

MySQL DOES contain those queries. Here is a link that will explain the functions of SQL: http://www.webdevelopersnotes.com/tutorials/sql/index.php3

I would recommend a SQL for dummies type of book because it really is the simplest programming language out there. When I was learning I would basically write down what I wanted to do, and then start checking the syntax. It's really that simple.

[richrosa]

If you use a pretty front end like SQLYog, you will have an interface to MySQL that will make Access users comfortable. There are many FREE alternatives to SQLYog as well.

MySQL has a robust implementation of SQL and has a powerful Stores Procedures library. Both of those will help you get the job done.

Using PHP, Python, Perl, .NET, or whatever, will help you build an application on top of your existing database. The data import required of horse racing data is more like an application itself, so I'd recommend a programming language to get the data in the way you want it, massaged the right way.

[podonne]

Hi Poddone,
if MySql doesn't contain the IF/THEN/ELSE syntax then it would be USELESS
to me for what i may want to accomplish, which would be to write queries
to select plays and produce those reports for all the HITS. so using a front-end programming language for doing this may be the only way to accomplish this. as far as learning/using ACCESS, from what Rich sez there is no comparison in the SPEED of queries dept, so i wouldn't want to use ACCESS and be MUCH slower. but ACCESS has the advantage of being a DB-oriented kind of software where it can be better used as a standalone. Waddaya Tink?
BarreMill

I think the difference between MySQL and Access may be confusing the issue. Both are databases, per se, both can be connected to with an ODBC driver from any programming language that supports it (you don't have to open microsoft access to query data from a .mdb file, i believe using the Jet engine).

However, Microsoft Access, the software (as opposed to the jet database) is a front-end for the database, providing easy creation of entry forms, editing data in the database, building queries visually, etc... All (ok, maybe most) of these things can be also done with a robust front-end to MySQL, it is just not a part of the standard package.

So you can think of MySQL + robust front end = Microsoft Access

Hope that clears things up a bit.

[podonne]

Just in case anyone is wondering, this article (http://www.programmingado.net/a-381/Performance-testing-Access-MySQL-MSSQL.aspx)shows MySQL being about 11-20 times faster that Access when using an ADO.NET connection, but, to quote from the article:

Access is indeed really, really slow. But please note that befire ruling it out of your possible solutions, the question should not be "how slow is it compared to XXX" - but instead "Is performance good enough for my application?". Cause often it is.

Well said,
Philip

[raybo]

I feel the same way. Fortunately I have a friend who is a wiz with php and a host of other languages, and I gave him a call last night. So last night he setup the mySQL db and used Michiken's php script as a base for a script to import .drf files.(Many thanks, I was having a hard time describing what it needed to do) So far so good. I basically upload the file to an ftp site, log into a page that's linked to the database, click process, and it's in the database. FYI, you don't need to have a website setup, you can just as easily do it on your own computer. Since my friend lives on the East Coast, and I live on the West Coast, this was the only way we could both access it.At this point I can run queries on the db, but only using mySQL.

The next step is to customize a front end for it. It might take us up to a month to get it finished, but once it is, I'll make sure to upload it for all of you. I sure wish I had better programming skills because it's really a very simple database that I should've been able to do on my own. Rich, I believe the front end he said we should use is navicat. Have you heard of that one?

Very cool, charli, I know I am not the only one looking forward to the upload!

[barn32]

MySQL DOES contain those queries. Here is a link that will explain the functions of SQL: http://www.webdevelopersnotes.com/tutorials/sql/index.php3

I would recommend a SQL for dummies type of book because it really is the simplest programming language out there. When I was learning I would basically write down what I wanted to do, and then start checking the syntax. It's really that simple.

This is a good tutorial. I'm teaching myself to do some programing, and wanted to learn SQL. I got to the point in the tutorial where I need to insert a file, the author provides, but I just can't get it to work. He says:

"Inserting additional records requires separate INSERT statements. In order to make life easy, I've packed all INSERT statements into a file. Click to download the file, employee.dat (http://www.webdevelopersnotes.com/tutorials/sql/employee.dat).
Once you download the file, open it in a text editor. You'll notice that it's a plain ASCII file with an INSERT statement on each line.

Inserting data into employee_data table with employee.dat file
On Windows
1). Move the file to c:\mysql\bin.
2). Make sure MySQL is running.
3). Issue the following command
mysql employees <employee.dat

I do all fo this, but nothing shows up, so I'm stumped. What I need is a place to go for help for questions like these when I bogged down. I have a place like that for Excel, and it's great. But for something like this I don't.

Any suggestions? I really want to get this.

I know it's something simple, but...

[Charli125]

This is a good tutorial. I'm teaching myself to do some programing, and wanted to learn SQL. I got to the point in the tutorial where I need to insert a file, the author provides, but I just can't get it to work. He says:

"Inserting additional records requires separate INSERT statements. In order to make life easy, I've packed all INSERT statements into a file. Click to download the file, employee.dat (http://www.webdevelopersnotes.com/tutorials/sql/employee.dat).
Once you download the file, open it in a text editor. You'll notice that it's a plain ASCII file with an INSERT statement on each line.

Inserting data into employee_data table with employee.dat file
On Windows
1). Move the file to c:\mysql\bin.
2). Make sure MySQL is running.
3). Issue the following command
mysql employees <employee.dat

I do all fo this, but nothing shows up, so I'm stumped. What I need is a place to go for help for questions like these when I bogged down. I have a place like that for Excel, and it's great. But for something like this I don't.

Any suggestions? I really want to get this.

I know it's something simple, but...

This forum should be able to answer all of your questions. They even have a newbie area, and they're usually pretty helpful. http://forums.mysql.com/

I would also try just running one of the INSERT commands rather than processing the dat file.

[Tom]

Couple questions -

Can you import the whole DRF BRIS file?
Can you export data to a comma delimeted type?
Can you import Excel sheets?


Thanks

[RonTiller]

When you're doing or reading about benchmarks with these various databases, you need to be careful. Several years ago, I bechmarked SQL2000, Access and MySQL 4.x with very large tables doing the actual sequences of queries that we do in our day to day processing of data. I spent some time making sure all indexes and performance tuning parameters were optimized for that environment.

Doing the queries in the native environments of each database product, I found that for most of these intensive sets of real world queries, there were no dramatic differences, when run in their native environments (ie. no odbc, ado.net, etc.). MySQL was the definitely the fastest but not by a landslide - still, kudos to MySQL.

Trying to run Access as a front end with MySQL as a back end was a disaster, using OBDC or ADO.net. This is not a criticism of MySQL - just a very unproductive way to use MySQL. Rich named what are surely better programming/front end environments for MySQL

On the other hand, there were literally queries we could run in SQL2000 and MySQL in minutes that would not run at all in Access, because they took too long in Access. And to follow up on the it-depends-on-what you-are-benchmarking, we subsequently found quite a few queries that were in fact orders of magnitude faster on SQL2005 (and no doubt MySQL) than on Access. But again, many weren't significantly faster or slower. In real world applications, performance enhancements/degradations can be a mixed bag, depending on what you do.

In the end, I would have chosen MySQL over SQL2005 as our backend database, when we were redoing our database infrastructure several years ago, except for 3 issues: 1) at the time MySQL had no trigger support (it does now), 2) at the time MySQL had no replication support (it does now) and 3) we prefer using Access as a front end, and it just didn't work well with MySQL, due to ODBC or ADO.net connectivity issues (it may be better now).

Rich is correct - its pretty hard to beat MySQL for price (free) and performance (very fast) so its hard not to recommend it. If you're intimidated by writing elaborate SQL or you just don't want to mess with it or you like Access thank you very much, you don't need to feel like a 2nd class citizen though. Depending on what you want to do, it may work perfectly fine. With tables, queries, forms, reports, macros and VBA all integrated, its a nice package - we still use it for some applications, even though we use an industrial strength database for our central data storage. For many applications, Access is lousy to unusable, even according to Microsoft. Of course, they recommend Microsoft SQL2005 (expensive) in these cases, not free MySQL. Go figure.

Ron Tiller
HDW

[podonne]

Couple questions -

1. Can you import the whole DRF BRIS file?
2. Can you export data to a comma delimeted type?
3. Can you import Excel sheets?


Thanks

1. Probably. As I learned earlier on this same thread, there are enough columns to load the entire BRIS file. There is still a question of whether there is enough room since the columns cannot cummulativly exceed 64KB in non BLOB and non TEXT values. I can answer this more definitivly later.

2. Yes.

3. Not in the .xls format, but if you save it as a .csv, sure.

Cheers.

[podonne]

When you're doing or reading about benchmarks with these various databases, you need to be careful. Several years ago, I bechmarked SQL2000, Access and MySQL 4.x with very large tables doing the actual sequences of queries that we do in our day to day processing of data. I spent some time making sure all indexes and performance tuning parameters were optimized for that environment.

Ron,

Great comments. Always good to here from a professional practitioner. Thank you.

Cheers,
Philip

[barn32]

This forum should be able to answer all of your questions. They even have a newbie area, and they're usually pretty helpful. http://forums.mysql.com/
Thanks for the link.

I would also try just running one of the INSERT commands rather than processing the dat file.
I did that and it worked, but the problem is that to follow along with the tutorial and do future problems you should have the .dat file. I could insert the data one line at a time, but I'm eventually going to have to learn how to upload data files anyway so...

[osophy_junkie]

It's still hard for me to believe that no free db template exists for a basic handicapping db. I tried in an earlier thread to get one going but couldn't get enough assistance, that was understandable for my very limited knowledge, to continue.

Attached is the schema for MySQL database.

[raybo]

Attached is the schema for MySQL database.

Ok, now what do you do with that thing?

[CBedo]

Thanks for the table structures Osophy. I'll take all the help I can get while I'm in the design phase (and after). I'm sure I'll have plenty of questions after I get a chance to thoroughly go over the schema.

[denton]

Attached is the schema for MySQL database.

Thanks for that - I tried to import this into a DB using phpadmin, but got this error (which appears to be the first line):

#1044 - Access denied for user 'eurobet_abc'@'%' to database 'equalog'

Any ideas, as I keen to have a look. My porblem (I am a total beginner) is getting my heas around how to store results.

I simply want racecards and then results for use as my own "form". Eventually I want this in a web-based front end via php. If anyone can help then please fell free to PM me.

Thanks.
Denton

[dartman51]

Personally, I think if you are going to do several tracks, I would switch to TSN PROCAPS files. You pay 1 flat price and get unlimited downloads. IMHO, there files are as good as BRIS. The XRD files are included if you need them and charts are free. I also believe CHARTS from any source are subject to errors in lengths behind or ahead. They are only as good as the chart caller, and anyone can have a bad day.

Good luck in your endeavor. You have come to the right place for help. There are a lot of VERY SMART people on this board, that know a lot about databases, and how to set them up. I, however, am NOT one of those people. ;)

[denton]

CBedo,

I have a 40 page chapter on analytical horse racing database construction within my "Racing Tutorial". The chapter is in English and uses UK and Ireland examples. PM me if you want a copy.

Robert

I sent Robert a PM but no reply....anybody else have this tutorial?

Denton