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 intautoNPrimary key for the people table.
login varchar30YMembers login
password varchar150NMembers password.(encrypted)
email varchar60YMembers e-mail address.
firstName varchar30NPersons first name.
knownAs varchar30YPreferred first name.
LastName varchar30NPersons surname.(indexed)
dob date-YMembers date of birth (used to work out their category).
gender char1N'M' or 'F'.
pic blob-YMembers picture (portrait, set dimensions).
current char1YCurrently a member? 'Y' or 'N'.
club varchar180YName of affliated club.
admin char1YClassed as an Administrator? 'Y' or 'N'. Default = 'N'
date_entered date-NDate of entry to the system.
last_updated date-NDate last changed.
changed_by varchar30YLast 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 intautoNPrimary key for the race table.
name varchar180NName of race.(indexed)
distance number3,2NDecimal of the total official distance.
measureId int-NForeign Key to scalez table.
type varchar30YOptions: xc, road, ultra, tri, dua etc.
event_flag tinyint-NIf it's a race or not.
tri_type varchar30YOptions: indoors, open water etc.
webAddress varchar180YOfficial web address of the race (minus http://).
date_entered date-NDate of entry to the system.
last_updated date-NDate last changed.
changed_by long int-NLast 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 intautoNPrimary key for the events table.
raceId long int-NForeign key for the race table.
eventDate date-NDate of the event.(indexed)
eventEndDate date-YDate event finished.(defaults to eventDate)
stage1Distance number3,2YDecimal of the 1st stage (of a multistage race).
stage2Distance number3,2YDecimal of the 2nd stage (of a multistage race).
stage3Distance number3,2YDecimal of the 3rd stage (of a multistage race).
stage4Distance number3,2YDecimal of the 4th stage (of a multistage race).
stage5Distance number3,2YDecimal of the 5th stage (of a multistage race).
stage6Distance number3,2YDecimal of the 6th stage (of a multistage race).
stage7Distance number3,2YDecimal of the 7th stage (of a multistage race).
stage8Distance number3,2YDecimal of the 8th stage (of a multistage race).
stage9Distance number3,2YDecimal of the 9th stage (of a multistage race).
stage10Distance number3,2YDecimal of the 10th stage (of a multistage race).
weather varchar30YOptions: windy, hot, calm, perfect, sunny, cold, wet (others?)
winningTime time-YOfficial time of winner (could be used to rank performance).
finalTime time-YLatest registered time (could be used to rank performance).
noFinishers long int-YOfficial number of finishers (could be used to rank performance).
date_entered date-NDate of entry to the system.
last_updated date-NDate last changed.
changed_by long int-NLast 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 intautoNPrimary key for the times table.
eventId long int-NForeign key to the events table.
personId long int-NForeign key to the people table.
raceNo long int-NEntrants race number.
stage1Time time-YSplit time of 1st stage (of a multistage race).
stage2Time time-YSplit time of 2nd stage (of a multistage race).
stage3Time time-YSplit time of 3rd stage (of a multistage race).
stage4Time time-YSplit time of 4th stage (of a multistage race).
stage5Time time-YSplit time of 5th stage (of a multistage race).
stage6Time time-YSplit time of 6th stage (of a multistage race).
stage7Time time-YSplit time of 7th stage (of a multistage race).
stage8Time time-YSplit time of 8th stage (of a multistage race).
stage9Time time-YSplit time of 9th stage (of a multistage race).
stage1Trans time-YTransition time of 1st stage (of a multistage race).
stage2Trans time-YTransition time of 2nd stage (of a multistage race).
stage3Trans time-YTransition time of 3rd stage (of a multistage race).
stage4Trans time-YTransition time of 4th stage (of a multistage race).
stage5Trans time-YTransition time of 5th stage (of a multistage race).
stage6Trans time-YTransition time of 6th stage (of a multistage race).
stage7Trans time-YTransition time of 7th stage (of a multistage race).
stage8Trans time-YTransition time of 8th stage (of a multistage race).
stage9Trans time-YTransition time of 9th stage (of a multistage race).
finishTime time-YFinishing time. (hh:mm:ss allows for 4 days).(indexed?)
completedRace varchar1Y'Y' or 'N'. Defaults to 'Y'.
catId long int-YForeign Key to cat table.
catPos long int-YFinal Category position.
finOverallPos long int-YFinishing position.
date_entered date-NDate of entry to the system.
last_updated date-NDate last changed.
changed_by long int-NLast person to change the record (personId).
Scalez table
item type size Null? description
scaleIdlong intautoNPrimary key for the scales table.
scalevarchar20NOptions: miles or kilometers.
date_entereddate-NDate of entry to the system.
last_updateddate-NDate last changed.
changed_byvarchar30NLast person to change the record (Login name).
Category table
item type size Null? description
catIdlong intautoNPrimary key for the category table.
categoryvarchar20NOptions: Junior (under 18), Senior (18-35), Vet35, Vet40, Vet50, Vet60, Vet70.
date_entereddate-NDate of entry to the system.
last_updateddate-NDate last changed.
changed_byvarchar30NLast person to change the record (Login name).

Use

Data input and amendment

  1. 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.
  2. 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.
  3. Initial data input should only be performed by suitably trained administrators. This will limit the amount of 'garbage' data entering the system.
  4. 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.

 
project/brc/brc_db2.txt · Last modified: 2006/10/27 13:03 by jackl
 
Recent changes RSS feed Creative Commons License Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki