General Table Design Question

[flatline]

Hi Folks-

I have a design-type question for you all.
I've been working on a database to track racing events.
There are several Heat races with first, second, third place etc. finishers in each.
Each finishing place has a point value associated with it.
The point values are different depending on the Heat race.
I have a table; tblRaceResults, where I will enter the results manually.
What I would like to be able to do is have two fields where I enter the Heat and Place and have the proper Points values found automatically.

What I can't determine is the best way to set up my tables for the Heat, Place and Points.
Initially, I started out with a single table;

tblPoints:
idsPointsID
chrHeat
lngzPlace
lngzPoints

This table contained every possible Heat/Place combination and its associated Point value.
I set a lookup to this table so that i could select the proper Heat/Place combination from a pulldown in tblRaceResults.
This pretty much worked but it seemed a very clumsy way to do this and I had to scroll through a large list to find the right combination.

So, I tried creating two different tables;

tblHeats:
idsHeatID
chrHeat

and

tblPlace:
idsPlaceID
lngzPlace

tblHeats had the list of possible heat values; 1A, 1B, 1C, 2A, 2B, 2C, BRONZE, SILVER, GOLD
and tbl Place had the list of possible places; 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

I was hoping to be able to enter the Heat and Place in tblRaceResults [using a lookup to tblHeats and tblPlace] and use those values to look up the proper Point value. But, I can't seem to get my feeble head around how to do this.

I'm sure this is a fairly simple thing and I'm just missing it conceptually.
I'm fairly new to all this.

Thanks very much,

flatline_____

 

[Parker]

All these numbers first thing in the morning--------very confusing

Zip your db and post it lets have a looksee

 

[flatline]

All these numbers first thing in the morning--------very confusing

Zip your db and post it lets have a looksee

Hehe, sorry 'bout that.

OK, you asked for it! The relationships are kind of a mess because I have two versions of the tblRaceResults. Also, there's a bunch of other stuff in there which relates to a Roster of Pilots etc. etc.

The .zip was too large [900K] due to a very large table. I've posted it on a website which you can get to here:

http://mysite.verizon.net/res0tatc/FILES/PROPS_DB.zip

I certainly appreciate any input you can give.

Thanks very much,
flatline_____

 

[Parker]

Ouch

OK I've just picked this up and I can see your biggest problem straight away.

Normalisation.

Just take a look at one table - tblPoints

You have every heat and every result listed causing loads of repeated information.

Split it up. You only need to store each race once. i.e:

tblHeat
RaceID
RaceName

You don't need to store the position or the points awarded in this table. You will end up with 9 entries.

After each person has completed a heat you then, presumably, have to tell the db what position that they finished in----Yes?

In which case you already have this information stored. All you need to do is to get the db to work out the points that should be awarded.

I would do this in code

i.e.

If heat = 2c finishposition = 3 then points = 6

In this way you can get the db to work out the points awarded. You don't even need to store this information because it is a calculation----all you need to do is display it.

Get the idea?

Read up on normalisation first then sort out your table structure. Go through every table and remove duplicated information. Remove any unecessary tables. You will then find the problems, like your db will shrink somewhat in size and complexity.

HTH

 

[flatline]

Exactly, The fact that tblPoints had so much duplicate information was my main reason for wanting to do this differently. I used it initially as a brute-force way to solve the problem but I knew there was a more elegant way to go about it.
I intended to eliminate that redundancy by creating tblHeats and tblPlace but I still had the problem of looking up the proper points total using the Heat and Place information.
I could do that in code as you mention. I'm assuming it would be just a long string of nested IF THEN statements to take into account every possibility, right?
But, wouldn't this cause problems if the point values changed since they'd be hard-coded into the IF THEN statements?
This is something I have to consider and it lead me to the decision to include all combinations in one table even though it was redundant.
This is the circular argument I've been having with myself. Is hard-coding the preferred way to solve this? Or is there another option?

Thanks very much for taking the time to look through this and offer your advise.
I'm glad to have the ability to talk through these things with others.

 

[Parker]

Hard code it as a calculation and include a routine to change the points allocation.

If you store the points value as strings then you can do a routine to change the values without ever having to go back into the code.

If other people have access to this you can p/word the routine through a form to stop someone else changing it.

Another way would be just to store the points allocation in a seperate table and use a query to allocate the points. Either way it will mean a much more reasonable table structure.

As I said, you do not need to store this information--------If you tell your db that Fred Blogs has just compleated heat 2A in first position then this should be the only information the db needs to store. It should know to apply Xpoints and, at the end, a simple calc. query should tot up the points for each pilot and give an overall position.

 

[flatline]

Got it!

Thanks a bunch.