Database Specifications

This page contains a list of the fields that are used to maintain the records database.

Currently, record keepers maintain their records in Excel spreadsheets. From time to time, record keepers send those Excel files to Justin Kuo. He will extract the data from the spreadsheet, add them to the main records database, export a tab-delimited file containing all the records and upload that file to the USATF web servers. The database specs may be used to create your own Excel file, or database.

Field Position	Field Name	Format/Description
1	Order	nnnn.n Used for sorting the records. These can be any value. To help group records, use the following: 00000 - 09999 Men's Track & Field 10000 - 19999 Women's Track & Field 20000 - 39999 Men's & Women's LDR 40000 - 59999 Masters Track & Field 70000 - 74999 Men's Race Walk 75000 - 79999 Women's Race Walk 80000 - 84999 Youth Boy's Track & Field 85000 - 89999 Youth Girl's Track & Field 90000 - 99999 Handicap Records are sorted numerically by Order. It determines the display order for distance and field events.
2	world/american	choice world, american, championship Note: the choices are in lowercase. Use "championship" for American Outdoor Championship Meet and American Indoor Championship Meet records. You may need to create duplicate records if the mark covers multiple categories. For example, create two nearly identical records if the mark is both a world and american record.
3	indoor/outdoor/road	choice indoor, outdoor, road
4	open/junior/masters[age group]/youth[age group]/all-comers/collegiate	choice open, junior, 30-39, 35-39, 40-44, 40-49, 45-49, 50-54, 50-59, 55-59, 60-64, 60-69, 65-69, 70-79, 70-74, 75-79, 80-84, 80-89, 85-89, 90-94, 95+over, 95-99, 100-104, sub-bantam, bantam, midget, youth, intermediate, young, all-comers, master all-comers Note: although the youth age groups are 8 under, 9-10, 11-12, 13-14, 17-18, we need to use the older group names for uploads.
5	Handicap	choice deaf (currently not printed)
6	men/women	choice men, women
7	status (ratified/best/pending)	choice ratified, pending, best, ratified-s, pending-s, rejected, unvalidatable, vacated note: ratified-s and pending-s indicate the mark has been superceded or bettered by another mark. Records removed due to doping will receive a status of vacated. note: best should not be used here. (jfk 1/2008)
8	distance	nnnnnn.nnnn Metric distance or hours. Enter the full length of the relays. For example, enter 3200 for a 4x800 relay. Record half-marathon as 21.0975 and full marathon as 42.195. For hour events, enter 1, 2, 12, 24, etc. Use 144 for 6-day events.
9	units	choice hr, km, m, mi Used for running events. Units are without a period.
10	type	choice distance medley relay, hurdles, race walk, relay, shuttle hurdles relay, sprint medley relay, steeplechase Example: for 4x100 relay, enter type as "relay", enter distance as "400" and enter units as "m". Example: for 5-leg or 6-leg Ekiden marathon relay, enter "relay" as type, enter "42.195" as distance, enter "km" as units and enter "5-leg" or "6-leg" as Implement height/weight
11	name (for field/combined)	choice decathlon, discus throw, hammer throw, heptathlon, high jump, javelin throw, long jump, mini javelin throw, pentathlon, pole vault, shot put, superweight throw, triathlon, triple jump, weight pentathlon, weight throw
12	Implement height/weight	text Hurdle height, implement weight, new or implement, mini. Example: Enter "3 kg", "4 kg", "5 kg", "16 lbs." for shot put. Note there is no period after the metric implement; there is a period after the imperial. Example: Enter 27", 30", or 33" for hurdle height in Masters T/F. Example: Enter "mini" for Turbojav. Example: Enter 5-leg, or 6-leg for Ekiden relays.
13	marks	numeric Athlete's time, distance or points. With the exception of the aided courses, the marks field is numeric only. Do not include "m" for measured running events like the one-hour race walk. Do not include "pts" for combination events. Example: 9.77, 1:41.11, 8397, 1:08:13a The lowercase "a" indicates a LDR performance "aided" by slope of course or wind.
14	first name	text Athlete's first name. Leave blank if type is relay.
15	last name	text Athlete's last name. Leave blank if type is relay.
16	age	nn (optional) Athlete's age in years The optional age field should contain either a numeric value or be left blank. Non-numeric characters will be removed.
17	affiliation	text Athlete's club or country, or relay team. If country, use the three letter IAAF code. Examples: "adidas", "New England Walkers", 'NZL", "Univ. of Wisconsin", "USA"
18	home city	text Athlete's home city.
19	home state	AA (2 letter USPS code)
20	home country	AA (2 letter ISO 3166 code) Athlete's home country. If there is a two letter home state, then be sure to set the home country to "US".
21	event name	text Event name.
22	event city	text City the event was held. Do not include the event state or event country in this field.
23	event state	AA (2 letter USPS state code)
24	event country	AA (2 letter ISO 3166 code) Country the event was held. Some countries may have both an ISO 3166, 2 letter code and a 2 letter, US Postal Service state code. In that case, the ISO 3166 code takes precedence and the state code should be blank. For example, Puerto Rico has a USPS state code of PR. But, since Puerto Rico has an ISO 3166, 2 letter code, PR, the event state should be blank and the event country should contain PR.
25	event date	YYYY-MM-DD (ISO 8601) Date (or starting date) the event was held.
26	event date - end	YYYY-MM-DD (ISO 8601) If on multiple dates, the final date the event was held.
27	comments	text
28	relay leg 1 first name	text First name of athlete running relay leg 1
29	relay leg 1 last name	text Last name of athlete running relay leg 2
30	relay leg 2 first name	text First name of athlete running relay leg 2
31	relay leg 2 last name	text Last name of athlete running relay leg 3
32	relay leg 3 first name	text First name of athlete running relay leg 3
33	relay leg 3 last name	text Last name of athlete running relay leg 3
34	relay leg 4 first name	text First name of athlete running relay leg 4
35	relay leg 4 last name	text Last name of athlete running relay leg 4
34	relay leg 5 first name	text First name of athlete running relay leg 5
35	relay leg 5 last name	text Last name of athlete running relay leg 5
34	relay leg 6 first name	text First name of athlete running relay leg 6
35	relay leg 6 last name	text Last name of athlete running relay leg 6
36	women's only race	choice yes, no Enter "yes" if the run or race walk was restricted to women, otherwise enter "no".
37	performance	choice record, best Use "best" for marks that do not meet USATF record criterea. Currently, only LDR uses "best".

Comments:

Be sure the field does not contain unnecessary spaces. Check and remove blank characters that may appear before or after a text field.
Look for extra characters that should not be there. For example, a mark of "5:21:21." has a period that should be removed.
Track Relays: Enter a choice of relay, distance medley relay, shuttle hurdles relay, or sprint medley relay as type. Enter the distance of the enter relay. For example a 4x800 relay should be 3200 as the distance. For distance medley relay enter 4000 as the distance. Enter the relay team name in the affiliation field. Enter the member names in relay leg 1 first name, relay leg 1 first name, etc.
Note: Open track relay records may include two relay records for the same distance. The USA National Team relay is made up of any US athlete, regardless of the club. A club relay is made up of members belonging to the same club team.
Road Relays: Enter relay as type. Enter a choice of 5-leg or 6-leg as Implement height/weight. Enter distance as 42.195. Enter units as km. Enter the relay team name in the affiliation field. Enter the member names in relay leg 1 first name, relay leg 1 first name, etc.
Women's Only Races should be indicated by a "yes" in column 36. If the race has mixed genders, indicate with a "no" in column 38, or leave the field blank. The reference is typically used for women's road races, but may apply for track races of 5000 meters and up. In road events, there may exist two records for the same distance -- one for women's only races and one for mixed races. In track events over 5000 meters, you would have just one record for a specific distance, regardless if the race included mixed genders.
If the home state is a two letters, then enter the home country as US.
If the event state is a two letters, then enter the event country as US.
The event city and event state and event country fields should be seperate. For example, for Ottawa, CAN, enter Ottawa for the event city and CAN for the event country.
You may need to duplicate records. Example 1: An all-comers record, that is also an american record would need two nearly identical records. The only difference would be the fourth column would contain "open" in one record and "all-comers" in the other record. Example 2: A 40 year old bettered the open 10-km road record. He or she would have duplicate records. Column 4 would contain "open" in one record and "40-44" in the other record.
When sending an Excel file to Justin, give it a unique name that includes the date. For example, 2014-12-31-LDR-records.XLS, or 2014-09-25-mastersRW.XLS. That'll help prevent the incorrect file from being used.
When e-mailing a file, consider compressing the file first and sending it as a .ZIP, .RAR or .7z file. As an example, the LDR Excel file is 358 KB uncompressed, 100 KB complressed as a .ZIP file and 58 KB compressed as a .RAR or .7z file. The compact, compressed file will mail quicker and with fewer problems.

Comments? E-mail USATF-NE at webmaster at usatfne.org with suggestions for this page. Thanks!