I'm an idiot

[LewisJ]

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?

This would end up in a lot of repeated data for events and rounds and such; the events and round type tables cut much of this out.

You only NEED 1 table for the whole thing, but that's even more spreadsheet-like and less database-like than doing it with 2, etc for 3, but with 5 it's taking advantage of how relational databases work to store less repeated data and make selecting specific bits of that data easier.

 

[jazzthief81]

Yah I misread there, but it instantly rises the question:
Why would it be convenient to see this rankings since October 23 2008??? What's the idea behind this? Shouldn't it display the current combined rankings then? Nonetheless it should say 2nd soon then with 8.33...
Weird weird weird

The ranking is based on all results over the past year (notice how the page hasn't been regenerated since 23rd October 2009).

The idea behind it is that it gives a good idea of who are the best all round cubers on current form.

 

[KwS Pall]

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?

This would end up in a lot of repeated data for events and rounds and such; the events and round type tables cut much of this out.

You only NEED 1 table for the whole thing, but that's even more spreadsheet-like and less database-like than doing it with 2, etc for 3, but with 5 it's taking advantage of how relational databases work to store less repeated data and make selecting specific bits of that data easier.

how do you repeat data in these tables? just give me an example

 

[tim]

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?

This would end up in a lot of repeated data for events and rounds and such; the events and round type tables cut much of this out.

You only NEED 1 table for the whole thing, but that's even more spreadsheet-like and less database-like than doing it with 2, etc for 3, but with 5 it's taking advantage of how relational databases work to store less repeated data and make selecting specific bits of that data easier.

how do you repeat data in these tables? just give me an example

Events and rounds are repeated, like he said. And i've learnt that using a string as a (foreign) key isn't the best idea performance-wise.

I've seen someone arguing about performance: Cubemania's times table has about 1.1 million entries (user_id, time, date, scramble, puzzle_id, average_id) and selecting the last 100 solves of person x takes only a few milliseconds. It's just a matter of proper indices.

 

[LewisJ]

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?

how do you repeat data in these tables? just give me an example

Basically, every single result (all 75k of them) would have to store:
-Name of the round it is from ("Combined Final" "First Round" etc)
-Name of the event ("3x3 Multi BLD" "Master Magic" etc)
Rather than:
-ID of the round type (1, 2, 3, 4, 5, 6, 7)
-ID of the event (1, 2, 3, ...however many events there are)

And as tim said, using strings (varchars, chars, whatever you choose) as a foreign key is rather suboptimal performancewise (thus you wouldnt use WCA ID or competition name for the primary key; they would be a unique keys, but a simple integer index would be much faster); same goes for matching specific strings in a SELECT; an integer key for event linked to a table of possible events not only reduces data redundancy but also improves SELECT performance when you have conditions related to events and rounds (which pertains to a significant portion of SELECT queries in this case)

 

[AvGalen]

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?

This would end up in a lot of repeated data for events and rounds and such; the events and round type tables cut much of this out.

You only NEED 1 table for the whole thing, but that's even more spreadsheet-like and less database-like than doing it with 2, etc for 3, but with 5 it's taking advantage of how relational databases work to store less repeated data and make selecting specific bits of that data easier.

how do you repeat data in these tables? just give me an example

Events and rounds are repeated, like he said. And i've learnt that using a string as a (foreign) key isn't the best idea performance-wise.

I've seen someone arguing about performance: Cubemania's times table has about 1.1 million entries (user_id, time, date, scramble, puzzle_id, average_id) and selecting the last 100 solves of person x takes only a few milliseconds. It's just a matter of proper indices.

Data would surely be repeated because in every round there are several solves (2 or 3 for blind, 3 for megaminx, 5 for most others)

Having lots of small tables for similar data is a horrible idea and doesn't benefit performance. It does duplicate a lot of information (table-structure) and makes writing queries really ugly (select * from "a union of all tables that have a name that starts with results_" :fp)
Having less columns in a table is only important if you have a database that doesn't understand "sparse columns" (storage) and if you use "select *" instead of "select col1, col2, col3" type queries (memory/cpu)

I think the sum of 345ranks should be based on all results ever, not on all results from last year. What if someone did really well in the past, but hasn't been participating in a year? Adding a "recent" section could be an option like it has been for longest blindfolded succes streak

Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes
Why are cubes now named "4x4"? I can imagine calling a cube "4" or "4x4x4" but "4x4" is just stupid.
And finally, why aren't statistics and records recalculated automatically after entering data from a competition? It seems like all queries on the results table are select-queries untill new competition data is entered and at that moment all pre-calculated data should be renewed as well. Or is recalculating that data really such a long process?

 

[Swordsman Kirby]

And finally, why aren't statistics and records recalculated automatically after entering data from a competition? It seems like all queries on the results table are select-queries untill new competition data is entered and at that moment all pre-calculated data should be renewed as well. Or is recalculating that data really such a long process?

What's interesting is that my records table has my 5x5 PB from the last competition, but it doesn't have my clock records.

 

[hr.mohr]

Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes

OK I'll bite.

phpMyAdmin uses auto commit so even if all tables where using innodb then the idiot in this case would still have made the fatal error.

 

[LewisJ]

Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes

OK I'll bite.

phpMyAdmin uses auto commit so even if all tables where using innodb then the idiot in this case would still have made the fatal error.

Exactly. Based on hearing foreign key thrown around by stefan and others it seems as though the database does use InnoDB tables, so it is possible to use transactions on it, but as you said, a lot of the issue with this was also the web interface; phpMyAdmin is a dangerous thing for someone that doesn't know exactly what they're doing.

 

[Dene]

Just a little question for you Stefan, do you recall ever double-posting yourself?

Sure. Happens often. Most recent example on the previous page of this thread. I also edit often, but if I have something significant to add and it's been more than a few seconds since my previous post, then there's a considerable risk that people who already saw the original post would miss the edit. Hence I often double post. And it really pisses me off when people get attacked for clearly legitimate double posts. Well, I *don't* recall people attacking *me* for it, I assume that's because they're not only morons but also cowards.

And what's wrong with editing a post quickly to add something like:
"POST IN EDITING, WAIT BEFORE REPLYING"?

I don't see any reason to double post. (Dene refuses to be called a moron and a coward, and also feels like an argument... I bet he won't rise to the biat though ).

 

[Kian]

I know the statistics page is only updated once in a while, but because of some weird reason at the "Sum of 3x3/4x4/5x5 ranks (since Oct 23, 2008 - Single and Average)" page, it says that I have the 12th single time in 3x3

Do you think this is wrong? What should it be?

Erik has the best single. He should rank 1, not 12.

If you say that because of his 7.08, that was not "since Oct 23, 2008".

My apologies, you are certainly correct. I misread.

 

[AvGalen]

Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes

OK I'll bite.

phpMyAdmin uses auto commit so even if all tables where using innodb then the idiot in this case would still have made the fatal error.

My mistake for not mentioning `a transactional database with a full recovery plan` as I did before.
Not only can transactions be rolled back, but you can also do `point in time´ recoveries. That basically means that Tyson writes down the date/time of the mistake and you restore the database untill 1 second before that mistake.

 

[LewisJ]

Ah I see, wasn't thinking of point in time. Point in time recovery is very doable with MySQL's binary log provided everything is done with transactions.
http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html
is a good article explaining how to setup and use it.
*cough stefan ron etc cough*

Wouldn't it be funny if it HAD been setup like this and nobody here or among the WCA guys had thought to use it....

 

[AvGalen]

Ah I see, wasn't thinking of point in time. Point in time recovery is very doable with MySQL's binary log provided everything is done with transactions.
http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html
is a good article explaining how to setup and use it.
*cough stefan ron etc cough*

Wouldn't it be funny if it HAD been setup like this and nobody here or among the WCA guys had thought to use it....

excellent tutorial you linked to and exactly what I meant. Hopefully this is usable on a hosted server as well

 

[Stefan]

And what's wrong with editing a post quickly to add something like:
"POST IN EDITING, WAIT BEFORE REPLYING"?

That doesn't help, does it? The people who have already loaded the page with your post on it will still miss your edit.

Will look at that Point-in-Time Data Recovery...

 

[Dene]

And if you make your second post before they make their reply to the first, they will probably completely overlook that too. So it's a lose-lose situation. I know which option I prefer regardless.

 

[AvGalen]

And what's wrong with editing a post quickly to add something like:
"POST IN EDITING, WAIT BEFORE REPLYING"?

That doesn't help, does it? The people who have already loaded the page with your post on it will still miss your edit.

Will look at that Point-in-Time Data Recovery...

I am curious about the outcome of this.

I also think something is still wrong with the database. I just checked http://www.worldcubeassociation.org/results/statistics.php and saw that it was "Generated on November 29 2009." yet I still see this: "Arnaud van Galen 47". But when I look at my profile page I can see 51 competitions just for 3x3x3. I think the statistics haven't been updated on november 29 after all

 

[Stefan]

Will look at that Point-in-Time Data Recovery...

I am curious about the outcome of this.

Didn't get to that yet. I do have recent backups, though, and I guess Ron does, too.

I also think something is still wrong with the database.

You're right, the statistics look old, something got mixed up. Btw, the last few days I've been working a lot on the statistics, to make them better and a lot faster. Almost finished with that, and then I'll clean up. I'll let you know when I'm done, probably later today.

 

[Giacomo Itam]

Mauro è bello

 

[Zeke Mackay]

Thanks for the 10 year bump