Jump to content

How to organize database for large number of pets?


Kesstryl

Recommended Posts

What would be the best way to structure a database so that users can have large numbers of pets, especially when you have stats attached to individual pets?  I could see this getting really unwieldy over time and would love to learn strategies for handling this in a scaleable way.

Link to comment
Share on other sites

Are the stats static (i.e. you will only have a max of 5-10 different ones)?

CREATE TABLE pets
(
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , 
  `user_id` BIGINT UNSIGNED NOT NULL , 
  `species_id` INT UNSIGNED NOT NULL , 
  `name` VARCHAR(50) NOT NULL ,
  `strength` INT UNSIGNED NOT NULL , 
  `intelligence` INT UNSIGNED NOT NULL 
)

Something like the above should be sufficient.

If there are going to be more dynamic ones (or ones that only take place in certain situations), then you may want to make a 1:many table but I won't go there for now because this will provide your most value.

  • Like 1
Link to comment
Share on other sites

I already use this exact type of table for my rpg that allows collecting pets, but players get only one of each and in the end complete a collection of pets they can swap out for stats to their players, but the collection system will limit how many individual pets each player has.  I've seen pet sites where players can have over 50k pets, won't one table of this bog down the DB?  I've never run a game with that many things in one table so I have no clue.  This is why I'm looking for insight.

Link to comment
Share on other sites

1. A database table can handle millions of rows easily.

2. Proper indexing will also help any potential slow down when fetching data.

I will also add on, that if you are the developer, you can just... not allow a user to have 50k pets ? If there is a worry about table size, you could just limit the max number of pets a user can have.

  • Like 2
Link to comment
Share on other sites

As @crotanite said, don't fear "filling up the database", it's meant for storing and retrieving data quickly.  I was doing some recent work for @Vix and on my local computer, their admin panel for "inventory" would time out on my computer after 30+ seconds of running.  I then made a single index and it came back instantly.  There is so much you can do with it.

I've had to deal with millions+ rows per day in a database (with weekly/daily reporting [think point of sale data]) so feel free to ask any performance related questions! I love to help!

  • Like 2
Link to comment
Share on other sites

Seconding what was said about indexing! I've had the same experience with having so many rows in a database table that eventually the features started slowing down, but as soon as I added some proper indexing, it turned lightning fast. 

Indexing can do wonders for performance. 

Link to comment
Share on other sites

16 hours ago, Hare said:

Seconding what was said about indexing! I've had the same experience with having so many rows in a database table that eventually the features started slowing down, but as soon as I added some proper indexing, it turned lightning fast. 

Indexing can do wonders for performance. 

Yes - but they are not a silver bullet, and actually can cause performance issues as well.  For example, if you happen to have 10 indexes on a table, and you do a bulk update of data, the update will take longer because while it's updating the rows it also has to touch the indices.  Just something to keep in mind when adding them :)

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...