0

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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
sam
  • 69
  • 6
  • 3
    "infinite" is pretty big. I'm not sure if I know of any computer that could handle that effectively. – Gordon Linoff Mar 09 '20 at 18:58
  • You are right! poorly worded on my part, I am so sorry, question updated – sam Mar 09 '20 at 19:00
  • 2
    Define 'very large' – Strawberry Mar 09 '20 at 19:02
  • You are right, I added in the definition of "With this method there would be a very large their amount of data 1 row for every player card combination thus growing exponentially." – sam Mar 09 '20 at 19:05
  • 1
    Is your current dataset so large that the queries are currently slow? Or do you just anticipate that it will *eventually* get that large? If it's the latter, I would recommend the "cross that bridge when you come to it" approach when it comes to scaling. – Jon Warren Mar 09 '20 at 19:05
  • I can confirm it is the latter. – sam Mar 09 '20 at 19:06
  • A scalable solution means a distributed solution. – David דודו Markovitz Mar 09 '20 at 19:07
  • What have you tried so far? "Very large" hasn't been defined yet, and MySQL should be able to run pretty good on hundreds, thousands, millions of lines using the proper indices – Nico Haase Apr 15 '20 at 07:50

2 Answers2

1

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.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • > 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". Are you sure it will be Logarithmic, not exponential? 1 card and 1 player are 1 row, 2 cards and 2 players are 3 rows, 3 cards and 3 players are 9. That’s like 2^X. – sam Mar 09 '20 at 19:20
  • 1
    Yes. Basically it depends when new levels are aded to the index. And integer indices fit a LOT of range on a page. Read up on how finding values in tree index structure works. – TomTom Mar 09 '20 at 19:24
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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Sadly, I don’t have a SHOW CREATE TABLE as I am still in the design phase. But a crude estimate is 500 cards, 500 players is way beyond what this project will get but will give me good max figures. This means the linking table would have 250,000 rows (by far the biggest table) with 8 bytes per row as its only 2 INT values that’s only 2 megabytes? That’s small! I think I’ve underestimated how much computers can handle again.even if the other tables are 100 bytes per row that's 0.2 megabytes more. A total of 2.2 megabytes.... is that right? – sam Mar 09 '20 at 19:58
  • Also the most complex query we will ever do is SELECT * FROM Inventory_Table INNER JOIN Card_Table ON Inventory_Table. Card_ID = Card_Table. Card_ID WHERE Player_ID=YourID – sam Mar 09 '20 at 19:58
  • 1
    Small amount of data; simple enough queries. So, "don't worry". – Rick James Mar 09 '20 at 20:41