Hi,

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