Mysql – Best database design for website ranking

database-designdatabase-recommendationMySQLperformancequery-performance

I am working on a project in which I need to rank websites in two way – Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 countries for which rank needs to be calculated.

I am using MySQL

I have two questions –

Global rank

Whenever any new website is added –

Either I need to recalculate the ranks for all the website if I store the website rank in database. This requires a lot of processing each time a new website is added.
or the other option is to assign a score to each website and whenever query is made assign rank by doing order by based on score, and returning its position. This doesn't seem scalable way as order by is expensive operation.

Country Rank database and updation

How should I store per country rank of each website ?
How to update all these ranks when a new website is added ? Here the updation will be even more costly than in case of Global rank.
Should I generate it on-the-fly when queried and then cache the result ?

How do websites like play store show personalized and country based results ?

Best Answer

Will you be ranking the billions of web sites? If so, then on-the-fly and recompute-all are out of the question.

Instead, have a background task working on the problem. For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc).

Put the ranking in a separate table, with only website_id and rank. This will make replacing the entire table after the recalc atomic and fast. Perhaps a second table with website_id, country_code (CHAR(2) CHARACTER SET ascii), and rank.

You have not explained how the ranking is calculated, so I can't give you any insight in how to do it as a background task.

Related Solutions

Postgresql – How to design a database for continents, countries, regions, cities and POIs

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J

Sql-server – Best way to rank all the columns in a table and store those ranks in another table

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration.

The ranking processes could be offloaded onto a read-only copy maintained via log shipping on a different box. That would avoid concurrency issues with hitting the live database system of record and provide more server resources for both the intensive batch process and live database. If you are running Enterprise Edition, you could use a database snapshot on the same box as the source if your hardware is sufficiently sized.

You mention truncate and insert. Just want to mention that you could also use SWITCH such that you truncate and insert into a staging table. Once the staging load is completed, truncate Table_Ranks and then SWITCH from the staging table into Table_Ranks in a single transaction. That way, Table_Ranks would never be empty.

Best Answer

Related Solutions

Postgresql – How to design a database for continents, countries, regions, cities and POIs

Sql-server – Best way to rank all the columns in a table and store those ranks in another table

Related Question