Racing Database Redesign - Part 4 - Tables (continued)

Let me start by saying if anyone has any questions or would like to exchange ideas with me or anyone else that is reading these tutorials then feel free to post a comment.

In part three of this series, I started to introduce the tables and discuss the relationship they have with each other. Hopefully you've taken the opportunity to look at the tutorials on my business website which will give you a basic insight into database table design.

The initial ERD I introduced last time is deliberately flawed so that I could illustrate the important link between the objectives and specification of the project; the data modelling presented within the table design and the internal relationships with the data being modelled.

If you recall, the 1st draft ERD indicates a number of many-to-many relationships within the design. This type of relationship is not unusual in a database but they need to be modelled differently because Relational Database Management Systems (RDMS) cannot handle them efficiently. The solution is quite simple however. Each many-to-many relationship can be modelled by splitting the relationship into two one-to-many relationships between the existing tables and a new intermediary table known as a 'joining' table.

The ERD diagram below represents the new arrangement where each joining table contains the key fields from corresponding tables that 'feed' it.

The presence of many-to-many relationships within the model as it stands is not an issue. I'm more interested in the relationships between jockeys, trainers and horses. First, ignore what you see in the above diagram and simply consider the relationship between jockeys and horses.

It is reasonable to assume a relationship between the two exists in that any one jockey can ride any number of horses in a career lasting many years and any one horse could be, over its career, ridden by a number of different jockeys. This is the very definition of a many-to-many relationship. Yet does this relationship fit in with the objectives of the project? How does one go about listing the jockey and horse partnership in any one event?

Clearly, for any given event, the list of horses can easily be obtained thanks to the relationship that exists between the EVENT table and the HORSE table. One might then think that the relationship between the HORSE table and the JOCKEY table gives us access to the jockey information. It does, in that it lists all those jockeys who have every ridden those horses. Unfortunately, because there is no direct link between the EVENT and JOCKEY tables it is impossible to assertain which jockey was riding which horse in any one particular event.

If you recall from the last post, the EVENT table consists of information about each race, namely Betfair's unique ID number (EventID) as well as date and time information. We need a way to relate the jockey table to that event information before jockey/horse partnerships for any particular event can be identified.

Now a very similar argument exits for the relationship between the HORSE and TRAINER and its resolution will use the same technique as that used between HORSE and JOCKEY.

Such is the way of things in the world of database modelling that there is not necessarily a single solution. In this case there are a couple of solutions that spring to mind. With that said, I'm going to finish this post here and give you the opportunity to mull over what those solutions might be and I'll address them next time.

No comments: