Database Build - Game concept / inventory management
I've posted this in various other forums including the MySQL forum and just looking for helpful responses.
Some notes: MySQL database, MyISAM type (debatable, I don't know why I would use InnoDB or how it differs from MyISAM, but the book I am learning from uses MyISAM, granted it is an older book.)
Hey guys, I hate to be the nubcake but I am. So I am still learning php and as a side project as I learn skills I intend to build an online game. Obviously, this needs to be database driven. This is about the time I learned about normalization. Then I started to normalize my data and got even more confused. Note, current concept of the game doesn't involve any real strong user to user interaction.
Needs of my particular Database:
Hold user information (name, password, email)
Link that user its various creatures it owns
Link that user to it's inventory (which should be easily changed)
Link the various creatures to its equipped inventory
Hold the various creatures statistics
so far I've gotten as far as this:
(perhaps this and the table above could be merged? I imagine additional statistics will eventually be added to each user, would it really make any difference to have the tables merged? )
user_id* (according to the laws of normalization this seems wrong because there would be lots of redundant data here. Is that right? Users can own multiple creatures)
creature_slots (number of inventory slots)
(Again, perhaps this and the table above could be merged? I imagine additional statistics will eventually be added to each creature, would it really make any difference to have the tables merged? )
USER_INVENTORY (unequipped items, and items that don't equip at all)
user_gold (holds number of how many this user has)
user_healthpots (holds number of how many this user has)
user_ressurectpots (holds number of how many this user has)
(not sure how to manage the above table because there are going to be items that some users may never have access to. So it might be nicer to have item_ids, but I need to link that also with how many of each, each individual user has. Also, each user may have none, or many of the various items that can be acquired. So having a very large empty table may not be the best way to approach this. Also I would think that as the game grows, new items will be available to users (as I grow the game). If I were going to have an unset number of unique items for each user and be able to track which items and how many of each, each user individually owns, how would I approach that?)
(I'm thinking it doesn't actually need the user_id* column since the creature table links the user and creature, and there would be redundant data here anyway)
(I'm struggling with how to manage this because not all creatures will have the same amount of inventory slots, and I would like the ability to upgrade the number of carrying capacity as they level up. This table has the same conceptual problem as the user inventory as I don't think making a column for each "possible" item hold the equipped number for each creature is the right solution. But again, I don't know a solution to make this more streamlined.)
So there are my thoughts and I hope it all makes sense, if you have any questions for me to answer to allow you to better understand my current database structure let me know. Any help will be incredibly appreciated.
After the original posting I had some thoughts later that night:
Upon further thinking about this database I believe the user_id foreign key should be removed from the CREATURE and CREATURE_INVENTORY tables and a new table USER_CREATURE_LINK should be created as follows:
Does that seem like a good idea?
I'm still on the ropes about merging USER and USER_STATS together and CREATURE and CREATURE_STATS together. Thoughts and suggestions?
What are best practices for that kind of thing? Users will have different stats then creatures so USER_STATS and CREATURE_STATS can't merge together.
Also still don't know how to manage the inventory of the user and creatures best.
Does any of this make sense? Thank you for your time!
Thanks for the replies guys. I'm starting to see since there is a 1 to many relationship between user to creatures, but not a many to many relationship between the two.
Also the user-stats and user tables I was starting to think should be merged to simplify things. Same with creature and creature_stats.
As far as the inventory goes though. The user inventory will be how much of something a user has minus the amount equipped to all of his creatures. User inventories will be much larger than a creatures.
For an example users inventory
health potions: 3
resurrect potions: 5
red stone: 3
green stone: 1
blue stone: 1
yellow stone: 3
where as a creature may only have:
green stone: 1
blue stone: 1
Would it best to set up an inventory table that has everything in it as a column? I feel this type of table would get rather large very quickly. Also I would want it easily editable, So if I wanted to change a items name or add new items it could get pretty crazy.
I was thinking of having a table setup such as this:
user_id* (not unique since many users will have many types of items, but can be null since we don't want to associate a creatures items to a user as well)
creature_id* (again not unique, but can be null as well. Also user_id OR creature_id must exist but BOTH can't exist)
item_stock* (integer of how many he possesses)
then there would be an items table
I think something like this would be much easier to manage and grow in the end. The Users and creatures would have a line item for each item they have but wouldn't have a large table with a bunch of empty values to start with.
And the inventory would have many relationships. What are your thoughts?
Better, but I wouldn't merge both the inventories together. I would have a UserInventory and a CreatureInventory. You don't need user_id in the creature inventory table since you can get to the user via the creature table.
If you merge both inventories together you will have rows with a user id but no creature id (the inventory specific to the user) and many of the sql engines don't like nulls in indexes, or at least unique indexes and you are going to want a unique index on user_id, creature_id, item_id.
What I meant was the combination of user_id and item_id must be unique. You want a unique index built on the two columns in combination. I don't know the syntax in mysql but every other database allows an index to be built on multiple columns where the combination of the columns is unique.
Ok, in that case, yes the combinations of those two keys must be unique.
This also works nicely because I eventually want equipment that alters stats and gives new abilities to the creatures and all that information could be held in the INVENTORY_KEY table.
Cool, I think we are making some progress. On another forum someone was suggesting merging all the stats and items into one table and using flags to delineate what each thing is. Personally that kind of seems like a pain in the butt. Why use flags when the table itself can be a flag. Wouldn't it produce more efficient queries if there are multiple tables more like I have it set up? However, I could treat the individual stats like we have decided to treat inventory here. Not sure if it is necessary. Would it make for more confusing queries? Or would it actually be more efficient, I could much more easily edit statistics. Ultimately stats are somehow going to be placed into formulas anyway in the code and the new stat_key table could hold easily adjustable portions of that formula.
If deciding to go this route though it would not be a good idea to merge user and user_stats.
Last edited by misticism; 03-04-10 at 12:30.
Reason: Added a thought
The stats in this case have a one to one relationship with the user, the inventory items have a zero to one relationship (every user will have a health level but they may or may not have a blue stone). Therefore the stats should be attributes of the user and the inventory items should be a separate table.
better yet would be to make that pair of columns the primary key
R937: Do you mean make each of those items a primary key, or do you mean to some how link them so that they are as a group a single primary key? If the later, how do I do that?
Thanks for your help everyone, I feel like I'm understanding the structuring a bit better. Additionally, is there a website or something I can be directed to that really does a good explanation of what Foreign Keys are, How they function, and How to use them? I only have a general understanding from the book I am learning from and it seems like a very important part of MySQL.
Last edited by misticism; 03-04-10 at 19:24.
Reason: added more stuff to read :)