Skip to content
Advertisement

What’s the best way to handle an ‘very large’ inventory with SQL? [closed]

I am looking into building an online card game like that of Hearthstone. Players will have an unlimited inventory to store all the cards they own.

With this method there would be a very large their amount of data 1 row for every player card combination thus growing exponentially.

Originally, I wanted to have 3 tables:

  • A Player_Table

    | Player_ID | Player_Name | Rank | Icon | ect...

  • A Card_Table

    | Card_ID | Card_Name | Attack | Defence | ect...

  • And then an Inventory_Table for them

    | Player_ID | Card_ID | Quantity |

Then I could use a statement like SELECT Card_ID FROM Inventory_Table WHERE Player_ID=YourID

Obviously, there is a scaling issue here where the more cards I add, and the more players join the longer this will take to get your card list.

I was thinking about using something like MongoDB as a NoSQL alternative to help with the potential performance issues that this would cause but then I found out its not free for commercial use unlike mySQL so I abandoned that plan.

The 3rd and final idea I came up with was dynamically adding tables. when a player is created (creates an account) I could just add a table with the name “Player_Cards_” + Player_ID (E.G. Player_Cards_318) Something is telling me this is a bad idea but I’m not sure.

Please could someone point me in the right direction please.

Advertisement

Answer

Millions of rows in a table is usually not a problem. Billions of rows gets exciting, but not necessarily impossible. Please do the math and come up with a crude estimate.

Meanwhile, please provide SHOW CREATE TABLE so we know the datatypes, engine, and indexes involved.

Do not create a new table for each user (or each whatever). This is an often asked question; the answer is always “no”.

SELECT Card_ID FROM Inventory_Table WHERE Player_ID=YourID

Is a very basic query. Any index starting with Player_ID will allow for executing that query very fast (milliseconds). INDEX(Player_ID, Card_ID) is likely to be even faster. What other common queries will you have?

Scaling… A simple Rule of Thumb is “100 bytes per row”. Calculate how many bytes you will need for all the rows in all the tables; will that fit on the disk you have? (I suspect it will.)

You will be reaching into 2 or 3 of those tables in a single SELECT. So learn how to do an SQL JOIN.

Re “logarithmic”: A “point query” in a trillion rows will take about twice as long as in a million rows.

My opinion of “no sql” — You have to re-invent SQL to get the task done. In the process, you will learn a lot about optimization techniques that an RDBMS would otherwise simply do for you.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement