Storing toptimes in database

[Bonsai]

Hey everyone,

I'm wondering about the best way to store toptimes in a database.

The toptimes resource that comes with MTA creates a new table for each map, containing the toptimes.

This seems to be an easy solution but it doesnt feel right to dynamically create a table like that.

Does anyone have a alternative design how to do it?

My concern is that looking up toptimes might take too long when they are all stored in one table.

I have about 8000 maps. When each has 100 toptimes average at some point, you end up with 800.000 entries.

 

Bonsai

[koragg]

Check this out : https://github.com/JarnoVgr/Mr.Green-MTA-Resources/tree/master/resources/[race]/race_toptimes

Idk what method is used but maybe it's what you're looking for. 

[Simple0x47]

You could use the dynamic tables to compare at the end the results with the top ones and if there's any change. Get the actual data in JSON and add to it the values that you want from the table converting them also to JSON. And then just store again the JSON.

[Bonsai]

1 hour ago, koragg said:

Check this out : https://github.com/JarnoVgr/Mr.Green-MTA-Resources/tree/master/resources/[race]/race_toptimes

Idk what method is used but maybe it's what you're looking for. 

That also creates a new table in the database for each map.

41 minutes ago, Simple01 said:

You could use the dynamic tables to compare at the end the results with the top ones and if there's any change. Get the actual data in JSON and add to it the values that you want from the table converting them also to JSON. And then just store again the JSON.

Uhm, I'm not sure what u mean.

Its only about the best way of storing toptimes.

I would prefer to not have a seperate table for each map.

But I dont know if that works, performance wise.

I already have a table containing all maps data, eg. name, creator etc.

Would be nice to use their key in another table that only has the toptime entries.

Maps:

Key Name Creator ...

1 Map1 Bonsai ...

2 Map2 Bonsai ...

Toptimes:

Key MapKey Player Time

1 1 Bonsai 70000

2 1 ccw 70001

3 2 Bonsai 90000

 

and so on.

 

The toptimes table would become pretty big and also frequently used.

[Simple0x47]

The most optimized thing you can do is to make a local table which holds on the top times of every map with a key which is the name of the map, and then in the same script add some functions which allows to edit the data in there, and when the resource is stopped save these tables with JSON into a database for getting sure you won't lose any info.

[pa3ck]

As you said 2 tables with primary and foreign key would be the best performance wise imo. Remember that SQL was made to handle such queries, just make sure you have index on the right column(s) and be smart with your queries. If you already know what's the next map going to be, you can get the result before the map actually ends and cache it. You can also normalise your data structure a bit more so that for example you won't have the map creator name for every map but a foreign key to a creators table, so whenever you're querying, it wouldn't need to look through all that useless data. And of course, relational database like SQL should not have duplicate data anyway.