I've posted this in the MySQL forum but I have recently stumbled across this forum and it looks far more active so I'm hoping for a response here:
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:
USER
user_id*
user_name
user_pass
user_email
USER_STATS
(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*
user_level
user_health
user_alignment
CREATURE
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_id*
creature_name
creature_type
creature_slots (number of inventory slots)
CREATURE_STATS
(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? )
creature_id*
creature_level
creature_str
creature_dex
creature_luk
USER_INVENTORY (unequipped items, and items that don't equip at all)
user_id*
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?)
CREATURE_INVENTORY
creature_id*
user_id*
(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)
creature_slot1
creature_slot2
(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:
USER_CREATURE_LINK
user_id*
creature_id*
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.
Thank you for your time!