|
Post by milfredo on Jul 13, 2020 18:57:19 GMT -5
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?
Thanks,Milfredo
|
|
|
Post by metro on Jul 13, 2020 19:58:05 GMT -5
Try a search on the forum for SQlite3 hours of fun for all (unless you're a gamer)
|
|
|
Post by honkytonk on Jul 14, 2020 3:28:27 GMT -5
With simple files and a few test lines, we load the files into arrays and deal with the arrays. Everything is possible. So give the structure of the table you want.
|
|
|
Post by milfredo on Jul 14, 2020 3:44:39 GMT -5
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.
|
|
|
Post by Rod on Jul 14, 2020 4:48:19 GMT -5
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.
|
|
|
Post by milfredo on Jul 14, 2020 14:48:34 GMT -5
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.
Thanks
|
|
|
Post by Rod on Jul 14, 2020 15:30:15 GMT -5
Very interesting project. As with all such projects getting a dumb computer programmer to write what you want requires oodles of detail.
The project is sound, the user need is sound and the data is pretty basic.
The problem is your astute understanding and the marshalling and presentation of the data is massively detailed and very difficult to simulate.
We also have the multi user access and update issue. This is a hugely complex online issue which Liberty simply does not handle.
Other may have more fire in their belly.
|
|
|
Post by milfredo on Jul 14, 2020 17:29:17 GMT -5
One database.... One user. So each user would have the DB on their machine attached to my program.If that helps.
|
|
|
Post by metro on Jul 14, 2020 18:46:21 GMT -5
|
|
|
Post by milfredo on Jul 14, 2020 21:50:24 GMT -5
Thanks.
|
|
|
Post by Rod on Jul 15, 2020 2:45:41 GMT -5
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.
|
|
|
Post by milfredo on Jul 17, 2020 12:41:10 GMT -5
Ok. Let me think about it a little bit. I'll be back as Arnold would say...LOL
|
|
|
Post by milfredo on Jul 27, 2020 18:39:19 GMT -5
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.
I think that's about it.
Milfredo
|
|
|
Post by Rod on Jul 28, 2020 3:49:36 GMT -5
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.
|
|
|
Post by meerkat on Jul 28, 2020 7:23:20 GMT -5
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);
# -------------------------------------------------------- # Table structure for: handicap 9 fields # -------------------------------------------------------- CREATE TABLE handicap ( trackNum INT(4), raceNum INT(4), horseNum INT(4), gate INT(2), handicap INT(3), speed INT(3), position INT(2), money DECIMAL(8,2), notes TEXT) ; CREATE UNIQUE INDEX handicap_track ON handicap ( trackNum , raceNum , horseNum );
# -------------------------------------------------------- # Table structure for: track 4 fields # -------------------------------------------------------- CREATE TABLE track ( trackNum INT(4), name VARCHAR(22), city VARCHAR(22), state CHAR(2), notes TEXT) ; CREATE UNIQUE INDEX track_num ON track( trackNum);
# -------------------------------------------------------- # Table structure for: race 7 fields # -------------------------------------------------------- CREATE TABLE race ( raceNum INT(4), trackNum INT(4), raceDate DATE, raceTime TIME, distFeet INT(4), condition VARCHAR(22), notes TEXT) ; CREATE INDEX race_num ON race( raceNum, trackNum);
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
|
|