Facebook Game database design
- by facebook-100000781341887
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 :)
