Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2010
    Posts
    7

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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).

  4. #4
    Join Date
    Mar 2010
    Posts
    7
    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?
    Last edited by misticism; 03-03-10 at 20:25.

  5. #5
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  6. #6
    Join Date
    Mar 2010
    Posts
    7
    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.

  7. #7
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  8. #8
    Join Date
    Mar 2010
    Posts
    7
    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.
    Last edited by misticism; 03-03-10 at 21:01.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MarkATrombley View Post
    I don't know the syntax in mysql ...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2010
    Posts
    7
    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.
    Last edited by misticism; 03-04-10 at 12:30. Reason: Added a thought

  11. #11
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  12. #12
    Join Date
    Mar 2010
    Posts
    7
    Quote Originally Posted by r937 View Post
    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.

    Thanks again!
    Last edited by misticism; 03-04-10 at 19:24. Reason: added more stuff to read :)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by misticism View Post
    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?
    yes, the latter
    Code:
    CREATE TABLE user_creature_link 
    ( user_id INTEGER NOT NULL 
    , creature_id INTEGER NOT NULL 
    , PRIMARY KEY ( user_id , creature_id )
    , FOREIGN KEY ( user_id ) REFERENCES users (id ) ON DELETE CASCADE
    , FOREIGN KEY ( creature_id ) REFERENCES creatures (id ) ON DELETE CASCADE
    );
    Quote Originally Posted by misticism View Post
    ... it seems like a very important part of MySQL
    foreign keys are an essential part of all relational databases, not just mysql
    Last edited by r937; 03-04-10 at 19:32.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •