Racing Database Redesign - Part 5 - Tables (continued)

Last time I left you to mull over what might be wrong with the ERD I had left you with. In particular, I queried the inability to marry a jockey/horse partnership to the EVENT table.

Hopefully, you'll have spotted the big clue I left. The ERD as presented is incomplete in that there should be another relationship in there.

Just as a list of horses running in an event can be ascertained through the relationship between the HORSE and EVENT tables, a similar many-to-many relationship exists between the JOCKEY and EVENT tables. That is to say, any one jockey can ride in a number of events and any one event clearly has any number of jockeys taking part.

Once that is established, a query can be run on the HORSE - EVENT relationship to establish a list of horses; a query can be run on the JOCKEY - EVENT relationship to establish a list of jockeys; and finally, the jockey/horse partnerships can be established by comparing those two lists through a query on the JOCKEY - HORSE relationship.

Exactly the same situation exists with regard to the TRAINER table. A many-to-many relationship between the TRAINER and EVENT tables must be catered for.

Those changes are illustrated in the revised ERD below in which you'll notice two things:

1. The number of tables required has doubled due to the number of many-to-many relationships that need to be handled.
2. I've started to label each table with field names. For now, I'm limiting myself to listing 'Key Fields' and those involved in the creation of the relationships.

Again, I'd point you to the tutorials on my business website for more details but it's worthwhile giving a brief explanation at this point regarding 'Key Fields'.

Put simply, each record within a database table must be unique. It is the job of the table key to provide that functionality. If one looked down the key field (or column) in a table, no entry will ever be repeated.

In an ERD, I show the keys in bold type. Relationships are generally established between table fields containing the same data. To enable that to happen, the key field from the ONE side of the relationship is exported to the table on the MANY side of the relationship as a 'Foreign Key'. This name is perhaps a little misleading in that 'Foreign Key' is NOT a key field within the new table. You'll see the CourseID field in the EVENT table is not shown in bold but its presence is necessary to establish the relationship between the COURSE and EVENT tables.

Now, consider the joining tables used to model the various many-to-many relationships by taking the JOCKEYEVENT table as an example:

These have two fields both of which are embolden because AS A PAIR they make up a unique table key. Individually however, they are not unique. Each EventID will be repeated a certain number of times equal to the number of runners in that event. Each JockeyID will repeat equivalent to the number of races he/she has participated in to date. But taken together, there will be no repeating combinations.

Moving back to the model as it now stands, there is still a problem with it...

Imagine for a particular race we wanted to list the horses taking part together with trainers concerned. If a particular horse was competing in a race where its previous trainer also had a runner then any query done on the current model would list both those partnerships.

To get around that, a record of when a horse was transferred to a new trainer would have to be included in the database. The easiest thing to do would be to put that date information into the HORSETRAINER joining table. Any subsequent query would simply ask for the most recent combination in order to eliminate the old partnership from the list.

Whether that data is readily available may be an issue for the purposes of this database.

The same problem rears its ugly head elsewhere. When trying to list the jockey/horse combinations for a particular race it is quite feasible that a horse may be up against a previous jockey riding another horse. This one isn't quite so easily catered for as the horse/trainer example above.

In my last post I indicated that there were a couple of solutions to the initial model proposed. (There may well be others that I haven't considered). Next time, I'll introduce my preferred method that will both reduce the number of tables required and, once the database is operational, make querying the data easier.

In the meantime, if you have any questions or if you are finding my ramblings even a little bit useful then please leave a comment below.


t-rex said...

Hi Alistair. Nicky from SL land. It's friday night and I have had a couple of beers. I have had an initial read through your project and I am facinated. I will need to have a proper slow look through it again but it is very interesting stuff so far. Keep it going mate.

Alistair said...

Nicky, you are a wise man. It is probably best to have a few beers before one reads my ramblings. :)

Dave said...

Interesting stuff and a better subject for a db than when I had to learn making them to 3nf! I'm tinkering with a football results one currently for making data trends easier to find.

Enjoying your posts greatly.

Alistair said...

Thanks for the comment Dave. Always good to know that folks are finding my posts useful.