Question about mysql

Okay all, So I’m working on an item system for a gamemode. And I’m trying to figure out the best way to store all the item info. Here are my thoughts:

Have a server, and a client database, have the server send small usermessages to the client when they receive/lose an item. When they hover over it in the inventory, or click a link of it, the client will query the online DB using the itemID the server sent. (Much like WoW)

Questions about that method are:

[li]Could that cause a lot of delay on loading the data from an online database?[/li][li]Would it be a good idea if I were to ‘cache’ the items’ data in a local table?[/li][/ul]
I’m also wondering, if it isn’t a good idea to hold it all online, is there a way to have the item Database local? I’m not sure that I want to use SQLite, because I’d need to somehow update the database when I add/remove/change stuff. (Which is why I’m thinking of keeping it online)

Hope that made sense, just let me know if you need more info. Thanks in advanced all!

Unless you will store 100,000+ items in that database you shouldn’t notice any impact on performance. I’m not sure how familiar you are with SQL servers but you should really put some focus into normalizing the database and stored procedures. It will take some load off your gmod server and let the SQL server do the hard work instead (which it’s designed for). Caching could save some additional resources if you execute it well.

I assume normalizing is using the autoincrement ID so it can search faster? Also, question about if I were to cache it in a table; are the tables stored in the RAM? If I were to stay on the server for hours, and saw plenty of different items, I’d think it would start to clog up the RAM.

Thanks for the fast response :slight_smile:

Here’s a quick guide to normalization, and yes your lua tables are stored in the memory but you can always clear them when a player leaves and let the GC get rid of the unused stuff. So oom dumping shouldn’t be a concern.

Alright, I’ll stick with my idea then. Thank you for all the help! :smiley:


Adding another question to this post. In the Table, I have an items rarity, based on a 1:* ratio. So the rarity field would be 10, 100, 1000, etc. In this particular case, it needs to have at least one item show.

Any ideas?