What’s the best way to handle an 'very large' inventory with SQL?

Question

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.

Answer 1

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.

Answer 2

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.

Nope, not in the way you think. Assuming you use indices, the growth will be logarithmic - NOT linear. More like "twice as long for 25 times the entries".

I was thinking about using something like MongoDB as a NoSQL alternative to help with the potential performance issues that this would cause

Nope. Besides the performance issues mostly being an illusion as I said, nosql is not particualrly helpfull for relational issues and you show a realational problem so far ;)

The 3rd and final idea I came up with was dynamically adding tables. when a player is created (creates an account)

And this is a terminable offense for anyone working in a company. It basicaily makes a ton of analysis totally unusable and results in a LOT many small tables which is generally an antipattern (databases are NOT optimized for this so you WILL end up starting wassting ressources for a solution that in any pro team would get you shown the door.

Use solution 1, done. Your sacaiability issue is not as bad as you thin (particualrly not if you add a sharding layer), and likely you will not run into it at all due to lack of users anyway.