The Johnstons have become a racing dynasty. Mark Johnston first registered his trainer’s license in 1987. In 2009 he became the first flat trainer to send out more than 200 winners in a single season (2009). He went on to repeat that feat in 2010, 2012, 2013, 2014, 2015, 2017, 2018, 2019 and 2021. His son Charlie was brought up to follow in his father’s footsteps and take over the family business. He started working full time for Mark in 2016, and joined his father on their joint trainers license in late January 2022, with Mark leaving the license from January 2023.

The key question for us is what implication does this have for the data in the Smartform database. Let’s take a look at the trainer_id values:

select distinct trainer_name, trainer_id from historic_runners_beta where trainer_name like "%Johnston";
+-------------------------+------------+
| trainer_name            | trainer_id |
+-------------------------+------------+
| M Johnston              |       1883 |
...
| Charlie & Mark Johnston |       1883 |
| Charlie & Mark Johnston |       NULL |
| Charlie Johnston        |     154458 |
+-------------------------+------------+
7 rows in set (5.72 sec)

Note there are three other unrelated Johnstons whom I have removed from the results table for clarity.

The first thing we see is that when Charlie joined the trainer license, the trainer_id remained the same. But when Mark left the license, a new trainer_id was created for Charlie on his own. If you take the view that nothing significant has changed about how the yard is run, the staff, the training methods, etc, you may prefer to see results from all three Johnston licenses under a single trainer_id.

But there is one other oddity. One or more of the Charlie & Mark Johnston results have a NULL trainer_id. That is a data error you may wish to correct. Further digging reveals that the NULL issue exists for 455 runners between 24/08/2022 and 31/12/2022. Let’s deal with this fix first.

The following SQL statement will correct all instances of the NULL trainer_id problem:

SET SQL_SAFE_UPDATES = 0;
UPDATE historic_runners_beta SET trainer_id = 1883 WHERE trainer_name = "Charlie & Mark Johnston";
SET SQL_SAFE_UPDATES = 1;

But if you prefer all the runners for all three Johnston licenses to be combined under a single trainer_id, simply use the following instead:

SET SQL_SAFE_UPDATES = 0;
UPDATE historic_runners_beta SET trainer_id = 154458 WHERE trainer_name = "Charlie & Mark Johnston";
UPDATE historic_runners_beta SET trainer_id = 154458 WHERE trainer_name = "M Johnston";
SET SQL_SAFE_UPDATES = 1;

Note that here we are using the newest trainer_id to tag all past results. This means the modification is a one-time deal and all future runners/races will continue to use the same trainer_id. If we had used the original M Johnston trainer_id value of 1883, we would have to apply the fix script every day, which would be far less convenient.

If you find there are other trainer name changes that have resulted in a new trainer_id which you would prefer to see merged, you can apply similar techniques. As a general rule, when new names are added to existing licenses, the trainer_id remains the same. But when names are removed from joint licenses, a new trainer_id is issued. But please check on a case by case basis, because rules often have exceptions !

The key takeaway is that you can create your own groupings with Smartform where you think such groupings are appropriate, without altering any of the source data.

If you know of other cases of trainer name changes which represent a continuation of substantially the same yard, please feel free to comment …