It's been a couple of years now since I asked a question about creating a database in LB. I read somewhere on here that LB 5. will have Database stuff included in the new version. Not sure if and when ver 5 will come out. Anyway, I am thinking again about needing a database application capability to attach to a program I am writing.
So, I am basically a Database virgin if you will, LOL. So what would be the easiest way to create a DB in LB here 2 years later? And any idea when Ver 5 might appear?
I was thinking of a database full of records. Each record would have say 150 fields. Based on that I expect when moving data into arrays, you would have to have several arrays linked by keys? Since the records would contain numbers and strings.
Honkytonk is right. Rather than pick a tool the first step would be to describe your data set. 150 fields, how many records? Again rather than set out the data structure now when we know little about the queries you will run you would be better just describing why you hold the data and what questions you want to ask of it.
For the size you describe I have every confidence that Liberty code will be all that you need. Perhaps a RAF no need for arrays.
The only thing Liberty can’t do is allow multiple users access to the same dataset. But I suspect you don’t need multiple access.
Ok. I write Horse race handicapping software. Have about 100 users. It's a labor of love for me. I don't charge and I keep updating it. I'm 70 and it keeps my mind clicking. So user would run my program handicapping a race and when done he would then enter the race info into the database. He would also after the day is over, enter the results of that day into the DB. How many records would depend on users commitment to building one adding to it every day or when he played. Queries, would be along the lines of before handicapping a race, user would start out selecting a race and then program searches for races of the same kind and return a factor set relevant to today's race. Then during non racing hours he/she could run reports based on certain criteria I would lay out for them. That's it in a nutshell. I could elaborate further but this really sums up what I'm looking to do.
So one database on each users PC is easy. How does it get filled? How about just listing the info you know you will need to keep in the database. Once we can see all the info we can start thinking about how to structure the data into tables and what order those tables will be kept in. It might be that a simple flat file is all you need. Say one horse's result for a specific race. All the info would be in that record, time place result weight location etc etc
Then its just a case of sorting this flat file to get to the info you want, like all results for one location in chronological order. So you sort and select the records you want then sum and report whatever you want.
These concepts of sort, select, sum, are probably the only database functions you will need.
Start by listing all the info you want to hold, describe it in a little detail, don't make it cryptic. Then give us an example of a question or report you would want from the database.
I have thought a lot about this.s So here goes. It's a horseracing database or will be. I'm thinking there would be two tables. One for the finished handicapping data and one for the results files. In the handicapping DB file, there would be say 3 keys....Record number, Date, Track. The Horse's name, weight age, sex, Race Distance, Race Conditions, Race Surface and then at least 180 data factors.. The results DB file would have the same three keys and The Horse's name, weight age, sex, Race Distance, Race Conditions, Race Surface and then the resulting payoffs for the horse's that finish 1, 2,3 and the finishing positions of each horse in the race.
When the User handicaps a race they will then be able to add that to the DB. At the end of the day, or anytime there after, they will be able to add the results file. If a corrected race results file is put out after one has been entered, the need to replace the old one with the corrected one is a necessity. I suppose there could be a need to reenter a handicapped race also, if the info in the original one was wrong somehow.
Now what can they do with the DB? When readying to handicap a race, program will access the DB and look for races just like the one that is about to be run and then pick out the best factors matching today's race and display them. The program then would use those factors to handicap the race.
As for research, that I haven't completely thought out as the possibilities could be Numerous. And I mean Numerous. So I'm a bit undecided concerning the scope to which I want the user to be able to access.
Ok you have really created one big flat file as your database. Its really a results table it records every result for every horse keeping a note of all the conditions that make a difference. Great so far. In fact I would just use it as is. I am not sure why two tables because the payoffs could just be more info on the end of each record in the results table.
There will be a huge amount of duplication but so what. Take for example a race, the conditions for that race were the same for all horses but you have recorded the conditions multiple times in each horses result record. Again so what, you are not taxing the PC or its memory.
Aficionados would immediately start linking tables, creating one conditions table for the race and putting a link in the results table so saving much space but super complicating the sorting and selecting.
As you have it the table is very well structured for selecting and sorting and so summing or comparing results. Just keep going with what you have, a flat file is easy to comprehend.
I don't pretend to know anything about horse races or what a handicap is. 2 things that are very important in DB design is referential integredy and normalization. With that in mind, and my lack of knowledge, here is my DB guess. And it could be completely off base. # -------------------------------------------------------- # Table structure for: horse 9 fields # -------------------------------------------------------- CREATE TABLE horse ( horseNum INT(4), name VARCHAR(22), birthDate DATE, deathDate DATE, weight INT(4), city VARCHAR(22), state CHAR(2), gender CHAR(1), notes TEXT) ; CREATE INDEX horse_ ON horse( horseNum);
This gives some normalization and integredy. Since you'd have a horse file, you get intregidy by having a drop down selection every time you enter horse information. Same with track, race and others. This reduces errors sense you can only select correct info.
No you have all kinds of selections. You could for example find everything about a horse as in: SELECT * FROM horse LEFT JOIN handicap ON handicap.horseNum = horse.horseNum LEFT JOIN race ON race.raceNum = winners.raceNum ORDER BY race.raceDate desc,handicap.position,horse.horseNum
This would give you all the horses by race Date, and winning position. As you can see you can do just about any combination of selections..
Hope this helps
Last Edit: Jul 28, 2020 13:13:13 GMT -5 by meerkat