Carrying on from where we left off puts us at the stage of designing the table layouts. A database is made of different tables and each table contains data. For example a Trainer table would probably just container the trainers name and an ID so that we can find them quickly and easily. Every table should have an ID number, this makes finding the information that you want later on much simpler.
Of course not all the tables are going to be so simple but how you lay them out is going to determine the complexity of your database and what you can do with it.
This brings us back to the earlier stages in creating a database where you decided exactly what it is that you wanted to do with your database. I work on the assumption that the database needs to be as flexible as possible and while this means that it will take longer (sometimes a lot longer!) to build it is better to build it once and be able to do everything I want than have to re-develop in a few years (which I have also done a few times).
So what are some of the tables that we may need in our database? Below is a list of the essential tables in your database:
When I said ‘essential’ it is certainly possible to make your database with less tables but above are what I personally consider to be the bare minimum for a working racing database. The first two simply have the trainer and jockey names along with an ID number for each of them. The horses table would contain the horses name and any other information about the horse (not related to a race). This may include, foaling date, dam, sire etc…… and of course an ID number.
Race Conditions is where you have all the information about a race. Some examples would be the race name, going, distance, number of runners, prize money etc…..and of course an ID number! HorsesRaces would be all the information specific to the horse in each race so this would be the place to put official ratings, speed figures, finish positions etc…..yup and that ID number.
Your Race Conditions and HorsesRaces table would be your main table and in it you would have the ID’s of the Race Conditions (so you know which race the information relates to) and the Jockeys and Trainers tables as well. Race Conditions would have the ID of the Course that the race was running at.
As I mentioned earlier you can go even more basic than that and simply have a Races and Horses table, Races containing all race information and Horses containing all horse information for each race. What this means though is that you are replicating a lot of the information, for example course names, horse names etc….
In the next part of this series we shall look at where you can get your data from to fill your database.