1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Game Inventory Database Design.

Discussion in 'MySQL' started by alanchavezv, Feb 17, 2012.

  1. #1
    Hey pals,
    This is my first time I've ever been in a forum this big.
    I tried to search for a similar topic, to see if a similar question has been asked before, but didn't have any luck.

    The thing is that I'm developing a video game (just for the sake of learning) and I am designing the database for the game inventory, but I have no idea what's an efficient way to keep track of what items own each user.

    I have a table full of items, let's say:

    id, item
    1, sword
    2, shield
    3, red potion
    4, blue potion
    5, green potion
    6, bow
    7, slingshot

    and the table for each user's character.
    id, username
    1, john1
    2, john2
    3, john3
    4, john4
    5, john5
    6, john6

    So I thought that I could create another table called inventory more or less like:
    user_id, item_id, quantity
    1, 1, 1 // john1 has 1 sword
    1, 3, 2 // john1 has 2 red potions
    2, 2, 1 // john2 has 1 shield
    2, 6, 1 // john2 has 1 bow

    but that will have a bunch a repeated records and will have a huge impact on performance as the number of users grow large, besides it goes against the rules of normalization.

    Then I thought that the user table, may have a column called 'inventory' and all the item IDs separated by commas, and treat them as a CSV, but then I face the problem on how to keep track of the amount of items each user has.

    id, username, inventory
    1, john1, (1,2,3,5)
    2, john2, (2,1,3)
    3, john3, (4,2)

    So I'm kinda stuck here, I'm still looking, and reading MySQL and Database books trying to find an example that more or less suits me needs, but I haven't found any yet.

    Someone has ever faced a situation like this? If this question has been already answered, could you please point me to the right post?
    alanchavezv, Feb 17, 2012 IP
  2. EricBruggema

    EricBruggema Well-Known Member

    Likes Received:
    Best Answers:
    Trophy Points:
    Well databases are meant for a storing LOTS of information in tables, so if you have a table with like 10.000.000 records it should run fine if you set the right INDEXES

    The setup you are giving is just a tiny table and should run fine (as stated above if you have your indexes right).

    and about normalization, LOL, this has nothing to do with normalization, please google much more.

    I've got tables with millions of records (linking tables, witch links one table to another table) and its working fine and processes 100.000 hits a day without any performance problems. So this should not be your problem at this moment. If you have like 10.000+ players a day then you have to worry... not at this time!

    b.t.w it sounds like maplestory (my kids are playing it)
    EricBruggema, Feb 18, 2012 IP
  3. Rukbat

    Rukbat Well-Known Member

    Likes Received:
    Best Answers:
    Trophy Points:
    Index the inventory table on user_id, then a select where user_id=X will run pretty fast, and return you all the inventory that user X owns. You don't want to do it your way - one row with all the inventory one user has - because that's how to slow things to a crawl. As Eric said, databases are designed to store lots of data. Also, they're designed to retrieve it very quickly if you design the database right.
    Rukbat, Feb 25, 2012 IP