Racing Database Redesign - Part 6 - Tables (continued)

I promised last time I'd introduce you to a second option; indeed, my preferred model. I'm going to renege on that promise for now as there are a couple of things I'd like to discuss in relation to the ERD I presented last time.

You may have seen the comment to the last post made by Dave regarding making a database '3nf'.

Dave was referring to the process of database 'table normalisation', specifically '3rd Normal Form'. As ever, you can find further information on my business website or feel free to google 'database normalisation'.

Suffice to say any database you design will be well served by getting your model into 3rd normal form. It ensures the integrity of the data contained within; eliminates repeating fields within tables; guarantees that each piece of data is stored in only one place; confirms tables only include data that belongs to the entity the table is describing and thereby makes accurate querying and data manipulation much, much easier.

Using Entity Relationship Diagrams as a technique not only forces you to think about the tables and relationships but you'll find that the design almost automatically falls into 3rd normal form. It certainly makes it easier to spot when it isn't.

Going back to the ERD presented last time, and not withstanding the issue with past jockey/horse partnerships, you'll find that the database is in third normal form. This leads me on to another important point about the normalisation process... It makes the expansion of your database beyond the remit of your original specification much easier without the need for a major redesign.

By way of example, at some later date I may decide I wanted to record further information about each track at each course. Off the top of my head: number of jumps; space between jumps; length of run in from last jump; is the course undulating or flat; is the run in up hill and so on. Or perhaps I might want to record information about a horse's breeding or detailed information about a trainer's yard.

To incorporate that data it is simply a matter of adding the additional tables required and creating the necessary relationships between them and the existing tables. The current data remains intact and no redesign is required to the existing structure.

Finally, let me revisit the jockey/horse partnership issue from the current model.

As indicated last time, the best way (I say best way in the context of the current ERD and the fact I can think of no other way within those confines) to handle it would be to treat it in the same way as the trainer/horse partnership and incorporate some date information within the joining table, HORSEJOCKEY. Those dates would reflect the date each partnership took place, i.e. the date of the race in question and therein lies the rub.

That date information is already contained within the EVENT table and incorporating it elsewhere would, while allowing the functionality I'm looking for, create a cardinal sin by storing the same data in multiple places within the database. This can lead to additional work in both programming the database as well as its operation but more importantly, it can lead to errors creeping in when recording and querying data. Every effort must be made to avoid that situation arising by eliminating the possibilty at the design stage. In otherwords, you should always ensure that data is recorded in one place and one place only.

That now leads me to the end of this little diversion and back on track. Next time I'll take a look at the revised ERD I've been promising you.

5 comments:

Dave said...

Great explanation of 3nf, something well worth the effort. My early db designs always think of adding things but having to resign lots of it as not done to 3nf. Certainly worth the extra time to normalise, saves a lot of work in the long run.

Alistair said...

Cheers Dave.

John Smith said...

Need help with automated system on fairbot struggling with set up can you message me a email so I can send you a question please

John Smith said...

Sorry if wrong place to post

Alistair said...

John, if you are looking for Fairbot support can you contact them directly at support@binteko.com