Welsh Castles Relay Results — About This Database

WCR Emblem

About This Database

The Welsh Castles Relay Historic Results Database catalogues stage-by-stage results for every running of the Welsh Castles Relay from 1983 onward — every team, every runner, every stage.

For the uninitiated, the Welsh Castles Relay is a 20 stage running relay from Caernarfon Castle to close to Cardiff Castle. It is not now a continuous ''touch'' relay, rather 20 races over the first weekend of every June. It can quite justifiably be called the premier league of road relays.

In case anyone wants to know the technical details (you probably don''t). This website runs on a Raspberry Pi 5 with 8GB RAM and a NVME drive (because I am not a total madman). It runs command line 64 bit Debian as the OS. The application is Python with Flask using Postgres as the database. I am using Cloudflare to look after the tricky bits. My internet connection is 300MBits/sec down and 48 MBits/sec up which is probably my limiting factor. I don''t know at which point my ISP will decide that I have breached the TOS for traffic. Fortunately there is nothing heavyweight being served so we might get away with it. What did this all cost? Mainly my time, the Pi was already controlling my NAS, my network and my VPN and the Domain is $5 per year.

The underlying schema is shown below as a Mermaid entity–relationship diagram. Referential integrity, partial unique indexes and constraints enforce data quality; the application layer handles normalisation of club names, athlete merges and the carry-forward of stage records across distance eras.

erDiagram ATHLETE ||--o{ RESULT : "runs in" STAGE ||--o{ RESULT : "produces" IP_CACHE ||--o{ PAGE_VIEW : "matches ip_address" RESULT { int result_id PK int year FK int stage_number FK int athlete_id FK text club int club_running_number int time int position text race_category text age_category } ATHLETE { int athlete_id PK text athlete_name UK text former_name char sex date dob text uka_no } STAGE { int year PK int stage_number PK text stage_name numeric distance int male_record text male_record_holder int female_record text female_record_holder int start_elevation int end_elevation int elevation_gain_m int elevation_loss_m int net_elevation_change_m text strava_activity_url numeric start_latitude numeric start_longitude numeric end_latitude numeric end_longitude char mountain_stage char jw bool manually_corrected } OVERALL_CATEGORY { int year PK int club_running_number PK text category } IP_CACHE { inet ip_address PK text country text hostname text city text isp text asn timestamp cached_at bool is_bot } PAGE_VIEW { int id PK timestamp viewed_at text ip_address text label int duration_ms } EDITABLE_PAGE { text slug PK text title text body timestamp updated_at } FEEDBACK { int id PK timestamp submitted_at text name text email text comment }