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>
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 :)

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.

• Hi Davy8, thanks for your reply, beside the space, I also concern the query is too much, as a result the user will waiting for a long time (I didn't estimate the host will rent, but I'm trying to minimize the things if possible, and I expect when a lot of user playing at the same time :) ). Actually, I put the attribute of the inventory in the PHP array currently, the database is only store the inventory is exist or not for the user. – facebook-100000781341887 Feb 28 '11 at 14:35
• If the queries are slow, you just likely need better indexes. However generally speaking unless you're doing complicated queries the database isn't going to be all that slow. Definitely changing the size of the column isn't going to noticeably affect performance. – Davy8 Feb 28 '11 at 15:07
• thanks Davy8, because my previous experience is on micro controller, we work with the data at bit level, therefore I always think about the bit mask things~ thanks for your explanation. – facebook-100000781341887 Mar 1 '11 at 7:33

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.

• Thanks bummazack, I've learn a new things from you, thanks~:) – facebook-100000781341887 Mar 1 '11 at 7:36

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.

• Hi Tobias, thanks, you just got my mind, I'm now talking with my friend in these issue on my phone. – facebook-100000781341887 Mar 2 '11 at 9:42
• What I prepare in my hand is (PHP+MySQL), and I have 2 choice on the webhosting, build a nginx server at my home(for development currently, but not fast enough), and rent a host(hostgator is now in my plan, VPS 0.5ghz, don't know it's enough or not), I also consider cloud, but it's too expensive for me. One of my corcern of host is the location, because my first game is aim for Asia Chinese user, but later on I will translate it to english version, therefore, I still prefer to choose US server. – facebook-100000781341887 Mar 2 '11 at 9:57
• Hi Tobias, I just read some document of NoSQL(major MongoDB), for my case, is it suitable for me to put all data into MongoDB, if I don't have data will be modify in same time? Actually, my game allow player win money from other player, it may encounter the record will operate in same time, however, the precisely of money really doesn't matter because they will not notice on the small amount change in a big number(say $10000 in$1 billion). – facebook-100000781341887 Mar 2 '11 at 11:33