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

Does any of this make sense? Thank you for your time!

user_creature_link would be necessary only if two different users could own the same creature

compare to cars... two users can own ~a~ ford pinto, but not the ~same~ ford pinto, unless you design it so that they can

make sense?

Actually a man and wife could both own the same car, but for this games purposes I agree with Rudy.

I see no reason to have separate stats tables, merge user stats into user and creature stats into creature (unless there is more than a one to one relationship).

I see no reason to have separate inventory tables, merge user inventory into user and creature inventory into creature (again, unless there is more than what I am seeing above).

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.

For example:
stone_green
stone_blue
stone_red
stone_yellow
boots_+1
boots_+2
boots_+3
gloves_+1
gloves_+2
gloves+3
ect.

I was thinking of having a table setup such as this:

INVENTORY
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_id*
item_stock* (integer of how many he possesses)

then there would be an items table
INVETORY_KEY
item_id*
item_name

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.

Ok, I understand splitting user and creature inventories. Also would make the queries shorter because you will know if you are looking for items for a user or creature ahead of time.

However, I don't think I can have user_id and item_id unique items within that table

Example
USER_INV
user_id
item_id
item_stock

Will yield entires like this:
user_id: 1
item_id: 1
item_stock: 30

user_id: 1
item_id: 2
item_stock: 12

user_id could not be unique in the case above as the same user has two different items

the same table will have entries such as:
user_id: 1
item_id: 1
item_stock: 30

user_id: 2
item_id: 1
item_stock: 12

in this case item_id couldn't be unique since multiple users will have some of the same items.

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.

ALTER TABLE foo ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] ( user_id , item_id )

better yet would be to make that pair of columns the primary key

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.

USER_STATS
user_id
stat_id
stat_quantity

STAT_KEY
stat_id
stat_name
stat_desc

If deciding to go this route though it would not be a good idea to merge user and user_stats.

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.

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.

Thanks again!

yes, the latter
foreign keys are an essential part of all relational databases, not just mysql