I'm an idiot

[miniGOINGS]

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.

Wow, so many things in that post scream truth.

 

[Stefan]

Summary: You want to make things ugly, contrary to the spirit of databases, to solve a problem that isn't there.

I take that last part back. I was thinking about the speed issue that Jim focused on and with which I disagree. But obviously we *do* have a problem with safety. Though I still disagree about how to fix that. I believe better backups and more backend functionality / less direct database access are the way to go, not tearing the data apart.

 

[LewisJ]

JBCM - while your ideas sound great in certain ways and in writing, I must ask - have you ever done much work with optimizing databases? I guarantee your proposed structure would result in more server load than the current structure. Relational databases simply should not be fragmented as much as you propose - it makes it too difficult to search and select out of a broad range of items.

Regardless of what the current structure is, a good structure for it would have a table for competitors, competitions, events (3x3, 4x4, 3x3 BLD) , competition events (3x3 round 1 at worlds 09, 3x3 BLD at MIT 09), and results with relations between the tables. All competitors, all competitions, all events, all competition events, and all results in each single table with their proper relations between them. This makes almost any realistic foreseen selection quite easy to make.

 

[Stefan]

After thinking some more about Jim's suggestion I think I understand it now, parts I should've understood right away. I still don't like it, but I apologize for being gruff and not thinking about it enough before replying.

Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result. If we had such a table, I'd btw call it "rounds", with the understanding that a row really refers to a specific round (i.e. in a specific competition and event). I think I actually pondered about this but dismissed it, though I don't quite remember why. Probably a mix of not necessary, not worth it, and never change a running system.

 

[LewisJ]

Rounds is definitely a better term, I thought of the idea but couldnt come up with a good name for it. And I do see how it can be decided against - I didnt even think of it until after a minute or two of pondering. It would only be helpful in certain cases but complicates things and thus it really is unnecessary, I just like to be very complete with DB schemas

And as for never change a running system...tell that to half the internet

 

[qqwref]

Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.

Just wondering, but does this mean that the competition names and event names are duplicated many times in the results table?

 

[Stefan]

If a competitor's name needs to be changed, using a more normalized schema it is changed once, not 100 times in some big results table.

So what? Big deal. Btw, we do already have a persons table, and the redundancy is only for efficiency reasons.

Gah, not true. Another big reason are the people who compete for the first time, they make us store their name in addition to their id because at first they don't have an id yet, and the mapping to existing or new ids isn't quite trivial, thanks to typos, different persons with the same name, and persons changing their names.

 

[Stefan]

Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.

Just wondering, but does this mean that the competition names and event names are duplicated many times in the results table?

Not their names, but their IDs. Poor use of the verb "name", sorry, maybe "reference" would be better.

 

[LewisJ]

Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.

Just wondering, but does this mean that the competition names and event names are duplicated many times in the results table?

Round name might be duplicated since there is no dedicated table (i suppose that's more support for having one but it doesn't matter too much either way) but competition name shouldnt be; results should have a column linking to the competition the result is from.
edit: semi-ninja'd :/

 

[Stefan]

Round name might be duplicated since there is no dedicated table

Actually, we do have a table for them, I think with attributes id+name+rank.

 

[JBCM627]

Though I still disagree about how to fix that. I believe better backups and more backend functionality / less direct database access are the way to go, not tearing the data apart.

I agree that better backups and more backend will help. What I proposed would be taking it a bit far in terms of breaking things up, but I still think that it could help if implemented to a further extent than it is now.

JBCM - while your ideas sound great in certain ways and in writing, I must ask - have you ever done much work with optimizing databases?

Not much, no. However, I have implemented a system similar to what I described in the live results system I made. It is filesystem based for portability, but since data is stored in a serialized array, this could (easily?) be transferred to a database structure. I'd like to get it to the point where I can do that, but probably won't until December at the soonest. Granted, live results are somewhat different, since the results system is designed for individual competitions.

I guarantee your proposed structure would result in more server load than the current structure. Relational databases simply should not be fragmented as much as you propose - it makes it too difficult to search and select out of a broad range of items.

So for generating rankings, which is something you need to select a lot of items for, sure. But it would reduce the time needed to execute many other queries.

Round name might be duplicated since there is no dedicated table (i suppose that's more support for having one but it doesn't matter too much either way) but competition name shouldnt be; results should have a column linking to the competition the result is from.

You can also more or less see the current schema in the dumps:
http://www.worldcubeassociation.org/results/wcadump200910.xlsx

 

[keemy]

When I saw your blog entry about this earlier I loled a lot.

 

[LewisJ]

Round name might be duplicated since there is no dedicated table

Actually, we do have a table for them, I think with attributes id+name+rank.

Ah well thats what I meant by competition events...so I guess there IS a table for that. All is complete

JBCM - the time difference in selecting 10 rows from a table of 75k and selecting 10 rows from a table of 100 is pretty much unmeasurable. SQL databases are quick even with tables with hundreds of thousands or millions of rows. Also, simple filesystem based storage is a bit different in what's efficient and what's not from an RDBMS. If I wanted to find the PBs and history of times of all events of a person (like what is displayed on a person's page) with your proposed one-table-per-event structure...I'd need a hell of a lot of queries. The nature of an RDBMS makes minimizing query count a reasonably high priority; there is a certain amount of overhead associated with each query in checking permissions and locking the involved tables for a read. With the current system, it all takes just a couple queries (and if you wanna be extra ninjalike, it can be done with just one if you throw on a good bit of serverside (PHP or whatever) processing and sorting).

 

[Tim Major]

Edit 2: No, only the stats page.

If that's an answer to my clarification request... please don't go back to edit your posts to answer a question asked below. I almost missed it.

Oops, sorry. I was trying a little too hard to keep my post count down.

Posts in off-topic do not count towards your post count.

Basically, I have about 8 posts a day. I didn't consider that this was off-topic. I'm not trying to stop double posting, as much as I'm trying to stop being a post whore.

 

[Stefan]

Round name might be duplicated since there is no dedicated table

Actually, we do have a table for them, I think with attributes id+name+rank.

Ah well thats what I meant by competition events...

I doubt that. Your examples were 3x3 round 1 at worlds 09, 3x3 BLD at MIT 09. Our table only has nine rows, for example id='1', name="First round", rank=20. See the xlsx file. It rather describes the round *type*, not a specific round in some specific competition in some specific event.

 

[hr.mohr]

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".

If only records after Oct. 23, 2008 are counted then Tomasz should be in first place and not second with his 7.91. Right?

 

[Erik]

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".

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

@hrMohr: it says on the top of the page that it's generated on the end of October 2009, Fuglsang was on 31 October/1 November

 

[hr.mohr]

@Erik: yeah, missed that. Also I think that you forgot about Kittikorn's 8.25

Oct. 23 seems kinda arbitrary. Why not display the all time rankings and the season bests just like they do in athletics?

 

[LewisJ]

I doubt that. Your examples were 3x3 round 1 at worlds 09, 3x3 BLD at MIT 09. Our table only has nine rows, for example id='1', name="First round", rank=20. See the xlsx file. It rather describes the round *type*, not a specific round in some specific competition in some specific event.

Ah OK, makes sense.

 

[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?