Thread: Design and Lookup Help Needed
05-21-13, 08:47 #1Registered User
- Join Date
- May 2013
Unanswered: Design and Lookup Help Needed
To make it clear, I am very much new to databases and so my apologies in advance for any stupid mistakes!
I am trying to create a database to record a lot of data relating to race simulations. My first attempt was a bit of a mess (one table was getting a little out of control!) so I have restarted and am trying to get the design right first time!
At this stage I have three tables:
- Tracks - track id, name and other data
- Season List - race id, season number, race number, track (lookup from tracks table)
- Race results - result id, race id (lookup from season list table), other data
My first problem is whether this is a correct structure - the season list is an interim table as a lot of other data (e.g. car wear) would be associated with it without needing to be part of the race results table (it was getting to 80+ fields otherwise).
Secondly, the race results table looks up from the season list. I have tried to set it up to look at the race id, and to show to track name, but when I do it shows the track id and not the name. I am guessing that this is because it is looking up from the season list, but the track name is in the tracks table - but don't know how to get around it!
My apologies once again for what I am sure is very much a basic question (and if you think I need to start smaller to learn the basics please just say so!), but any help or pointers are most welcome.
05-21-13, 11:22 #2Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
Hello and welcome!
The best way to start designing a database like this is with a pencil and a big sheet of paper. Sketch out the objects that will be modelled in the database along with their attributes/properties, and then start drawing links between them. These will be your tables and their columns. If you find that one or more of them begin to contain repeating or semi-independent columns, you need to break out more tables from them. For example, in the tables that you've listed above, I would suggest that you break out a race table and a season table from the season list. (I did this last week for an ongoing project, and what I thought would be two tables ended up being four.)
If you find yourself numbering fields (e.g. Race1, Race2, Race3), that's usually a sign that your design premise is flawed. If that happens, take a step back and think about your goal. There are many articles out there on database normalisation and normal forms - a Google search will turn up more reading material than you can stand.
As you begin drawing the links between them, you will see places where new tables are required for the express purpose of joining other tables together. Again, from the above example, your season list table should be an intersection of season, race and track.
If you get stuck, zip up a bare-bones copy of the database (all tables and other objects that you've created thus far, plus enough non-sensitive data to illustrate your points) along with what you want to achieve. Someone here will most likely deconstruct it and let you have some pointers.10% of magic is knowing something that no-one else does. The rest is misdirection.
05-21-13, 13:58 #3Registered User
- Join Date
- May 2013
Many thanks for your comments. I think that going back to pen and paper might be needed; even with my second attempt I am beginning to see things that I have 'forgotten'.