Facebook Game database design

Question

I'm currently develop a facebook mafia like PHP game(of course, a light weight version), here is a simplify database(MySQL) of the game

id-a           <int3>  <for index>
uid            <chr15> <facebook uid>
HP             <int3>  <health point>
exp            <int3>  <experience>
money          <int3>  <money>
list_inventory <chr5>  <the inventory user hold...some special here, talk next>
... and 20 other fields just like reputation, num of combat...

*the number next to the type is the size(byte) of the type

For the list_inventory, there have 40 inventorys in my game, (actually, I have 5 these kind of list in my database), and each user can only contain 1 qty of each inventory, therefore, I assign 5 char for this field and each bit of char as 1 item(5 char * 8 bit = 40 slot), and I will do some manipulation by PHP to extract the data from this 5 byte.

OK, I was thinking on this, if this game contains 100,000 user, and only 10% are active, therefore,

if use my method, for the space use,

5 byte * 100,000 = 500 KB

if I use another method, create a table user_hold_inventory, if the user have the inventory, then insert a record into this table, so, for 10,000 active user, I assume they got all item, but for other, I assume they got no item, here is the fields of the new table

id-b     <int3>  <for index>
id-a     <int3>  <id of the user table>
inv_no   <int1>  <inventory that user hold>

for the space use,

([id] (3+3) byte + [inv_no] 1 byte ) * [active user] 10,000 * [all inventory] * 40 = 2.8 MB

seems method 2 have use more space, but it consume less CPU power. Please comment these 2 method or please correct me if there have another better method rather than what I think.

Another question is, my database contain 26 fields, but I counted 5 of them are not change frquently, should I need to separate it on the other table or not?

So many words, thanks for reading :)

Answer 1

First of all, don't skimp so much on space. Even on shared hosting you'll get several GB of space and if you're not storing large binaries or entire books or something, CPU and memory are going to be bottlenecks before disk space is (usually).

I thinking keeping int columns as small as possible is pretty old advice when disk space was more expensive than it is now. Just use regular int, and if needed bigints (or whatever your DB's equivalent is)

You'll definitely want to have a separate table for inventory, because that'll give your more flexibility. If the items have any kind of special attributes you'll probably want the items in the DB as well (not sure from your question whether you were planning that already) that way you can do queries like, get all the items in player x's inventory that can be used while in combat, or something like that.

Edit:
As far as speed goes, with this simple structure, the database is likely not going to be the thing slowing you down. They are generally well optimized if you know how to use them properly, and it's not until you get really complex queries that performance even begins to be an issue. According to this accepted answer on Stack Overflow, 100,000 records is still considered a small database, and large isn't until around 10,000,000 to 1,000,000,000 records, so you should be good for some time.

Answer 2

What you're doing here is premature optimization. Modern RDBMS can handle much more records and are optimized for speed and efficiency.

There's no point in simplifying your DB-design like that, because:

• You'll loose flexibility.
• You will have to do the stuff in your code that was previously handled by the RDBMS (eg. get all inventory items as separate fields?)
• It will needlessly obfuscate your DB-design.

A lot of RDMBS can also do load balancing, so if you're really going to hit a DB performance bottleneck, you could scale up your DB servers.

Answer 3

You might want to look into a noSQL database like mongodb if you work with PHP because of the ready made drivers and because it looks like all you have to save are statistical and property data for which you wouldn't need joins or anything more "complex".

If you arent sure about the size of your app you might be better off starting with a cloud solution. I know many use EC2 but im very happy with rackspace cloud servers. They are decent fast and super easy to deploy. We had our own Facebook app running around two years ago and maxed out around 42 million users on FB. As database storage i used redis because it is very fast (all in memory) and spread the data (simple properties similar to your data) over 20 2GB nodes and hashed the data with the flexihash algo (redis cant shard/hash) with a redundancy of four. That way i was able to scale up and down pretty easy. (Looking back at it it wasn't the smartest solution...)

Just always work with intergers and use caching everwhere (memcached) and you're good. Don't worry too much about the disk space. If you have to handle a LOT of users you should be able to pay for a few more servers.

A little tip for the hosting: If you want a serious hoster, you might want to look into the Softlayer special deals and ask the support if they give you a raid or double disk storage for free. When i got my first Softlayer server around 5 years ago i asked them if they could turn the RAM deal into a Disk deal and got 2x250GB.