help for design for a special table

gvanaco

I am a climber and collect a lot of climbing areas.

On every climbing area into my database, I put the amount of climbing routes into the local grading system.
The system is a normaly numeric system.
Now the british people, the have a special system
More explanation about the system: http://www.rockfax.com/publications/grades.html

An example is to create an overview from the next page http://www.rockfax.com/databases/results_buttress.html?id=922

The solution is:
2x E2 5c
1x E1 5a
2x VS 4c

And so an.

How should I create my database that I don't have 60 columns in my table
Also, I don't won't to create a form with more than 100 fields to fill in.

Can someone help me to setup

Any help is apreciated.
thanks

bastien

since the euro ratings have an equivalency chart (with some overlap) into the US systems, you can convert on the fly when entering it or showing it to change it to include the us system.

shouldn't need that many fields

what is your current structure like?

gvanaco

The example structure from the UIAA scale looks like this

CREATE TABLE `cot_uiaa` (
  `cot_uiaa_id` int(7) NOT NULL auto_increment,
  `cot_uiaa_rotsn` int(5) NOT NULL default '0',
  `cot_uiaa_4min` int(3) NOT NULL default '0',
  `cot_uiaa_4` int(3) NOT NULL default '0',
  `cot_uiaa_4plus` int(3) NOT NULL default '0',
  `cot_uiaa_5min` int(3) NOT NULL default '0',
  `cot_uiaa_5` int(3) NOT NULL default '0',
  `cot_uiaa_5plus` int(3) NOT NULL default '0',
  `cot_uiaa_6min` int(3) NOT NULL default '0',
  `cot_uiaa_6` int(3) NOT NULL default '0',
  `cot_uiaa_6plus` int(3) NOT NULL default '0',
  `cot_uiaa_7min` int(3) NOT NULL default '0',
  `cot_uiaa_7` int(3) NOT NULL default '0',
  `cot_uiaa_7plus` int(3) NOT NULL default '0',
  `cot_uiaa_project` int(3) NOT NULL default '0',
  PRIMARY KEY  (`cot_uiaa_id`)
) TYPE=MyISAM COMMENT='Sportclimbing Grades of the International Alpine system'  ;

After is I put only the amount of routes in my table.
The difference whit this table is the 1 - 1 relation.
By the britisch system there are two variables first the technical grade, second the difficulty grade.

bastien

Seems to me that most of those fields should be collasped into one field 'ratings' and what it stores is the rating for that climb. Doing this will make any searched that much easier since you are using 1 field to search on and not 20

gvanaco

bastien on my last post it is a view of a normal metric tabel that already is in use.

It gives you a view what I have done with the other 13 metric grading systems.

Otherwise you can also take a look to the site I have made .
www.worldtopo.com

Why I post this question?
I would create a table that I can show the amount of route in the britisch grading system. The table should be fast to do searches and easy to fill in.

Already thanks in advances

gvanaco

My question looks very complex

gvanaco

could someone take a look to my question.

bastien

I would let the code handle most of the raings issues and have a table that looks like this

route Id
grade_uiaa
grade_difficulty
grade_usa
grade...

Then you can show the equivalency for all the ratings in one swoop.
Arrays in the code take care of mapping one route to its other equivalencies, or you could do a look up on some master fact table that holds nothing but the ratings and the equivalents.

That would be my approach. Then the user could simply enter the route data, choose the rating system they are comfortable with and enter the rating...code takes care of the rest...