database design question

[GlennC]

Hello,

I have a horseracing database with over 50,000 records and climbing daily. Each record has over 200 fields, some data, some calcs. I think I might need to change the way the database works, but I'm not sure what is the best way to go.

Because of the format of the the external raw data (past performances) each record is one horse in one race. By now I have different records for the same horse, where the horse has raced on different dates. It would be useful to have one record for each horse with its each previous run and associated data.

I have thought a portal type of setup would be the way to go, but I interrogate the data in many different and at times complex ways and involving a variety of summary fields and scripts. I don't know if that would be possible if the data was within a portal. I would also need to navigate and transfer data between portal lines, even though they would be different line numbers for different records, (depending on the number of records for each horse). I would need most of the 200 fields accessible in this format - is that workable in a portal?

Is there a different possibility I am not seeing? I'm sure I'll carve out the neccessary functionality in my usual non-expert and plodding way, but I need to have a handle on the most appropriate structure first. My head is spinning trying to know how it should be.

If anyone is able to offer help I would be very grateful. If I have not made things clear I can describe things further. Thanks to anyone who might help.

Regards,

Glenn

[_ian]

Hi Glenn

You should investigate moving to a structure with multiple tables. In FileMaker each table corresponds approximately to either a file or a value list. So you will end up with multiple files. The files you will probably need to get started are the following: Horses, Races and Entries (entries of a particular horse in a particular race). You might also want to add a Race-Course file depending on what kind of reporting you want. Map out on paper which of your ~200 fields belongs to which file.

Each record in each file should have a unique identifying key. This is usually a field that is auto populated by a serial number that is not modifiable. This field is then used to set up the relational link between files. So, for example, your Entries file is going to include the ID of the horse and the ID of the race as well as any other information pertinent to the particular entry, perhaps the finishing position, notes etc.... Your Entries file will not contain a lot of information about the horse itself since that is stored in the horses file.

So you will be able to view the entries of a particular horse through a portal. You will still be able to do your summary reporting, but some will probably be done from the Entries file instead of the Horses file. Other summary reporting, such as number of races a horse has entered can be displayed directly in the Horses file, since you merely need to have a calculated field that counts the number of related races.

regards

Ian

[GlennC]

Thanks very much Ian, it's very much appreciated.

I've taken some time to digest this and have (I hope) a conceptual grasp. As far as I understand things, I think the 'Horses' file will more or less be only the horse's name, becasue almost all of the data is relative to the 'Entry' itself - eg days since last start, betaten margins, etc. All of this data changes for each 'Entry'. Or do you see something I don't?

I roughly put together a 'Horses' file together with an 'Entries' portal. Because I have mulitple records of the same horse in the current database ('Racing'), I ended up with as many duplicates in 'Horses', all with identical portals, even though I had specified Horse Name to be unique. FM ignorses this under some circumstances??

(The horse's name should theoretically be a unique identifier as racehorses must all be registered and supposedly no duplicate names allowed).

The external data is imported into FM via a spreadsheet - one 'Entry' = one record. I don't see how to get this data to add itself to existing records in 'Horses' or create new record if it's a new horse.

I use a number of different sorts in the processing of the data. As I understand things data in a portal can only be sorted one and only one way as defined by the relationship definition. Is this correct?

Again, thanks very much!

Glenn

[_ian]

Hi Glenn

FileMaker won't object to duplicate values in the name field in the Horse file unless you add a new record or modify an existing one. The easiest way to populate the Horses file to start is to sort the data in your existing file by the horse name, then export the horse name to a text file while selecting the "summarise by" option and choosing to summarise by horse name. FileMaker will then export one record per horse name and you can import that into your horses file.

When you import your new records into entries, you may need to script the process of adding the new horses into the horses file. Alternatively you could do an import into Horses, opting to update exising records and adding any that don't exist.

given that I know absolutely nothing at all about horse racing, I'm not really sure what data you might be tracking. Things like days since last start, I think would be in the Horses file, but it would be calculated something like (possibly not exactly): status(currentDate)-Max(Entries_by_Horse::entryDate).

On the portal sorting issue. You can get around that by defining a field as the portal sort field which is calculated as a Case statement on a global field that contains the desired sort order. Then to change the displayed sort order you simply change the contents of the global, the calculation refreshes and your portal is now sorted in the new specified order.

hope this helps

Ian

[GlennC]

It certainly does help, Ian. Everything you've suggested is right on the money and within 24 hours I have a whole new baby on my hands. Thanks alot and all the very best to you!

Regards,

Glenn