BRC Race Database application
Preface
Warning! The text that follows contains a lot of technical language. If you have never made a database or complicated spreadsheet you will probably get a lot more benefit from the information on this page.
That said you are more than welcome to have a gander at the design spec below, I have attempted to use as much plain English as possible.
As this is a wiki I expect the stuff below to evolve. This is not the finished article, just something to start some debate 
.
Concept
The idea is to have a web-based database that is accessible from any Internet connected PC. This should allow a member to review his/her race history or that of a team-mate, it could contain a calendar function that could show what races/events are happening in a month and what members are due to attend. It should also allow reports to be made on basis of category (age), sex, distance, type (x-country, road, ultra, tri etc). A 'PB' view should also be available.
Design
Security
No application is secure by default. It pays to be pessimistic when dealing with computers Some things to think about:
Back up procedure
We will be holding a lot of historical data, some of which may not be recorded elsewhere, on a server we don't own or maintain. A regular weekly back-up of, at least, the database is recommended.
Personal information
The database, as initially conceived, will hold limited 'personal' information. However a consensus needs to be sought as to what level that information should be protected. This can go from simply not displaying the information publicly (i.e. you need to login to access the info) to encrypting the information in the database itself.
Login privilege
What level of control a logged in user is allowed should also be debated.
- The ability to change their own records seems reasonable to me.
- However a limited time to change race result details also seems like a good idea. Should race result records be 'Read Only' a month after the race?
- Should the user be able to enter their results for individual races?
- Should the user be able to delete their results for individual races?
- Should the database record the last person to update the record and the date/time this was performed?
Reports
Each report should to be designed in advance. This will allow the database schema to be detailed enough to get around the issue of 'feature–creep'. This doesn't mean that the database is too inflexible to produce off the cuff reports, post-design reports will be possible.
Designing reports isn't rocket science; anyone that has produced an Excel sheet of the information they would like to see together on one page has designed a report! In this respect we should ask for as many 'report' ideas as possible in the initial stages.
Database
The database used is likely to be MySQL simply because it is widely available, is easily up to the job and works well with the PHP scripting language which will produce our front-end.
An initial table layout is proposed below:
The People table
The People table will hold the personal information on members and entrants. I expect that this database only needs to have BRC members in it but having the flexibility to have non-members listed means that it can handle prospective members and be used as a results service for the Benfleet 15?
There should only be one record for each person.
| People table | ||||
| item | type | size | Null? | description |
| personId | long int | auto | N | Primary key for the people table. |
| login | varchar | 30 | Y | Members login |
| password | varchar | 150 | N | Members password.(encrypted) |
| varchar | 60 | Y | Members e-mail address. | |
| firstName | varchar | 30 | N | Persons first name. |
| knownAs | varchar | 30 | Y | Preferred first name. |
| LastName | varchar | 30 | N | Persons surname.(indexed) |
| dob | date | - | Y | Members date of birth (used to work out their category). |
| gender | char | 1 | N | 'M' or 'F'. |
| pic | blob | - | Y | Members picture (portrait, set dimensions). |
| current | char | 1 | Y | Currently a member? 'Y' or 'N'. |
| club | varchar | 180 | Y | Name of affliated club. |
| admin | char | 1 | Y | Classed as an Administrator? 'Y' or 'N'. Default = 'N' |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | varchar | 30 | Y | Last person to change the record (personId). |
The Race table
This table will hold the basic race details. These details shouldn't change from one year to the next and hence it has no date information.
There should only be one record for each Race i.e. Flora London Marathon (raceId=7) has only one record.
| Race table | ||||
| item | type | size | Null? | description |
| raceId | long int | auto | N | Primary key for the race table. |
| name | varchar | 180 | N | Name of race.(indexed) |
| distance | number | 3,2 | N | Decimal of the total official distance. |
| measureId | int | - | N | Foreign Key to scalez table. |
| type | varchar | 30 | Y | Options: xc, road, ultra, tri, dua etc. |
| event_flag | tinyint | - | N | If it's a race or not. |
| tri_type | varchar | 30 | Y | Options: indoors, open water etc. |
| webAddress | varchar | 180 | Y | Official web address of the race (minus http://). |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | long int | - | N | Last person to change the record (personId). |
The Eventz table
This table will hold the instance of the race details. So each race will have more than one record in this table as it includes the date the race was run. The stage fields allow recording of distances for each stage if issued (the scale will be taken from the Race table).
There will be one or more records for each record in the Race table i.e. raceId: 7 will have many records as FLM is run annually & members always participate.
There won't be a record for each runner in the event.
| Eventz table | ||||
| item | type | size | Null? | description |
| eventId | long int | auto | N | Primary key for the events table. |
| raceId | long int | - | N | Foreign key for the race table. |
| eventDate | date | - | N | Date of the event.(indexed) |
| eventEndDate | date | - | Y | Date event finished.(defaults to eventDate) |
| stage1Distance | number | 3,2 | Y | Decimal of the 1st stage (of a multistage race). |
| stage2Distance | number | 3,2 | Y | Decimal of the 2nd stage (of a multistage race). |
| stage3Distance | number | 3,2 | Y | Decimal of the 3rd stage (of a multistage race). |
| stage4Distance | number | 3,2 | Y | Decimal of the 4th stage (of a multistage race). |
| stage5Distance | number | 3,2 | Y | Decimal of the 5th stage (of a multistage race). |
| stage6Distance | number | 3,2 | Y | Decimal of the 6th stage (of a multistage race). |
| stage7Distance | number | 3,2 | Y | Decimal of the 7th stage (of a multistage race). |
| stage8Distance | number | 3,2 | Y | Decimal of the 8th stage (of a multistage race). |
| stage9Distance | number | 3,2 | Y | Decimal of the 9th stage (of a multistage race). |
| stage10Distance | number | 3,2 | Y | Decimal of the 10th stage (of a multistage race). |
| weather | varchar | 30 | Y | Options: windy, hot, calm, perfect, sunny, cold, wet (others?) |
| winningTime | time | - | Y | Official time of winner (could be used to rank performance). |
| finalTime | time | - | Y | Latest registered time (could be used to rank performance). |
| noFinishers | long int | - | Y | Official number of finishers (could be used to rank performance). |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | long int | - | N | Last person to change the record (personId). |
The Timez table (he he)
This table will (finally) hold the actual times recorded by the entrant. It looks a mess due to the multiple Stage times — I couldn't think of a better way of recording this sort of information (damn you triathlon). The majority of 'normal' races will only use the finish time field.
This table will hold one record per person, per event.
This will be the largest table on the database but at least it isn't holding 'heavy' data.
| Timez table | ||||
| item | type | size | Null? | description |
| timeId | long int | auto | N | Primary key for the times table. |
| eventId | long int | - | N | Foreign key to the events table. |
| personId | long int | - | N | Foreign key to the people table. |
| raceNo | long int | - | N | Entrants race number. |
| stage1Time | time | - | Y | Split time of 1st stage (of a multistage race). |
| stage2Time | time | - | Y | Split time of 2nd stage (of a multistage race). |
| stage3Time | time | - | Y | Split time of 3rd stage (of a multistage race). |
| stage4Time | time | - | Y | Split time of 4th stage (of a multistage race). |
| stage5Time | time | - | Y | Split time of 5th stage (of a multistage race). |
| stage6Time | time | - | Y | Split time of 6th stage (of a multistage race). |
| stage7Time | time | - | Y | Split time of 7th stage (of a multistage race). |
| stage8Time | time | - | Y | Split time of 8th stage (of a multistage race). |
| stage9Time | time | - | Y | Split time of 9th stage (of a multistage race). |
| stage1Trans | time | - | Y | Transition time of 1st stage (of a multistage race). |
| stage2Trans | time | - | Y | Transition time of 2nd stage (of a multistage race). |
| stage3Trans | time | - | Y | Transition time of 3rd stage (of a multistage race). |
| stage4Trans | time | - | Y | Transition time of 4th stage (of a multistage race). |
| stage5Trans | time | - | Y | Transition time of 5th stage (of a multistage race). |
| stage6Trans | time | - | Y | Transition time of 6th stage (of a multistage race). |
| stage7Trans | time | - | Y | Transition time of 7th stage (of a multistage race). |
| stage8Trans | time | - | Y | Transition time of 8th stage (of a multistage race). |
| stage9Trans | time | - | Y | Transition time of 9th stage (of a multistage race). |
| finishTime | time | - | Y | Finishing time. (hh:mm:ss allows for 4 days).(indexed?) |
| completedRace | varchar | 1 | Y | 'Y' or 'N'. Defaults to 'Y'. |
| catId | long int | - | Y | Foreign Key to cat table. |
| catPos | long int | - | Y | Final Category position. |
| finOverallPos | long int | - | Y | Finishing position. |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | long int | - | N | Last person to change the record (personId). |
| Scalez table | ||||
| item | type | size | Null? | description |
| scaleId | long int | auto | N | Primary key for the scales table. |
| scale | varchar | 20 | N | Options: miles or kilometers. |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | varchar | 30 | N | Last person to change the record (Login name). |
| Category table | ||||
| item | type | size | Null? | description |
| catId | long int | auto | N | Primary key for the category table. |
| category | varchar | 20 | N | Options: Junior (under 18), Senior (18-35), Vet35, Vet40, Vet50, Vet60, Vet70. |
| date_entered | date | - | N | Date of entry to the system. |
| last_updated | date | - | N | Date last changed. |
| changed_by | varchar | 30 | N | Last person to change the record (Login name). |
Use
Data input and amendment
- Data, when available in a suitable electronic format, should be able to be input in bulk (i.e. a whole list of race results). The data may need some manual 'tweaking' (maybe moving columns in Excel) before it is loaded into the database but this should still be less work than manually entering each persons result.
- As a back-up and to enable amendments to erroneous data there should be a manual method of input, probably using a web-based form.
- Initial data input should only be performed by suitably trained administrators. This will limit the amount of 'garbage' data entering the system.
- Corrections should be able to be made by individuals to their own records as necessary. There should be a back-up system available for those that don't have access to, or inclination to use, a computer. This would probably be an administrator job.
Reports
The reports will be accessible via an initial webpage. The detail shown there initially could be the filtered by a period of time (possibly last month). There should be a method of logging in to amend the records as necessary.
