Logiqx
Veteran
This project started when I wanted to determine the relative solve times for 2x2x2 - 7x7x7. The original thread around this topic can be found on the Speedsolving.com forum. To determine relative solve times, I needed a simple way to calculate vigintile rankings for the baseline event (i.e. rankings in 5% bands) and some way to correlate peoples times for different events. Since completing the original investigation the SQL scripts have been tidied up and a few extras have been added to create the WCA SPV.
The WCA SPV is essentially a single table containing one record for each person who has ever competed under the WCA. The results and rankings from the WCA database have been rolled up and pivoted to provide a summarised view for each person. The summary records contain a large number of "facts", summarizing peoples participation and performance in individual events as well as across all events. The main benefits of the summary table are to simplify common analytical queries and assist with queries correlating facts across multiple events. A number of example queries are shown later in this document, demonstrating how the SPV can be utilised.
I've drawn upon my experiences in the workplace where I have been involved in "Single Customer View" projects for a number of UK corporations. The WCA SPV is a lot simpler than a corporate Single Customer View but there are some common principles and themes. Wide tables with lots of columns may seem unusual at first but they can be really useful and effective. The number of people in the WCA is small enough for MySQL to cope with all of the competitors in a single wide table, even on modest hardware. A corporate SCV often contains information about 10's to 100's of millions of people and often requires terrabytes of storage, typically employing alternative database technologies (e.g. column-oriented DBMS). There are far less people in the WCA SPV!
My project can be found on GitHub - https://github.com/Logiqx/wca-spv
The WCA SPV is essentially a single table containing one record for each person who has ever competed under the WCA. The results and rankings from the WCA database have been rolled up and pivoted to provide a summarised view for each person. The summary records contain a large number of "facts", summarizing peoples participation and performance in individual events as well as across all events. The main benefits of the summary table are to simplify common analytical queries and assist with queries correlating facts across multiple events. A number of example queries are shown later in this document, demonstrating how the SPV can be utilised.
I've drawn upon my experiences in the workplace where I have been involved in "Single Customer View" projects for a number of UK corporations. The WCA SPV is a lot simpler than a corporate Single Customer View but there are some common principles and themes. Wide tables with lots of columns may seem unusual at first but they can be really useful and effective. The number of people in the WCA is small enough for MySQL to cope with all of the competitors in a single wide table, even on modest hardware. A corporate SCV often contains information about 10's to 100's of millions of people and often requires terrabytes of storage, typically employing alternative database technologies (e.g. column-oriented DBMS). There are far less people in the WCA SPV!
My project can be found on GitHub - https://github.com/Logiqx/wca-spv
Last edited:
