Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,417
    Thanks
    68
    Thanked 102 Times in 101 Posts

    Database Normalizing

    Hello, I am a beginner at normalizing and trying to figure it out. Here is the setup I have so far.

    Table > useitems
    id
    playerid
    itemtype
    amount
    itemid
    I have id set as auto increment.

    So I insert a the players id, and the type of the item, how many they have of it, and which item it is by the id.

    Then when a player updates or removes it, it alters that record. If there is zero items, when they removed it all, it deletes the record.

    It'd add a new record for that players id for every new item that doesn't already exist in the table.

    Would this be normalizing the proper way?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,340 Times in 4,306 Posts
    Short answer: Almost surely yes.

    I wonder about the need for ITEMTYPE if you already have ITEMID. Can't you get ITEMTYPE via the link to the ITEMS table implied by ITEMID?

    There's also no real need for the auto_increment ID field. You will surely never have any real use for it. Doesn't hurt, but kind of useless.

    If you want a *GOOD* UNIQUE key for these records, you could create a composite key on ( playerid, itemid ).

    That is. you should never have more than one record with playerid=817 and itemid=2188 (example only).

    Finally, you don't really *need* to ever update one of these records (it's okay to do so, but it may be easier to always do
    Code:
        DELETE FROM useitems WHERE playerid = 817 AND itemid = 2188;
        INSERT INTO useitems( playerid, itemid, amount ) VALUES( 817, 2188, 40 );
    And you can simply ignore and hiccups from the DELETE: If there were no such records to delete, the delete wiill do nothing at all.

  • #3
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,417
    Thanks
    68
    Thanked 102 Times in 101 Posts
    Well I decided to go ahead and rewrite the whole item usable page and delete the current item table.

    itemtype
    1 = healing
    2 = fish to sell
    etc

    So you're saying that when you have a record of

    playerid = 45
    itemid = 3
    amount = 109

    and they use 4 of that item, to delete the record, and insert

    playerid = 45
    itemid = 3
    amount = 105

    ? Is that what you mean? rather than update to change it?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,340 Times in 4,306 Posts
    I didn't say you *had* to do that. I just meant that in some scenarios it's easier to do delete and insert than to figure out if you *can* do just an update.

    I would think in the situation where you know the user had *SOME* abmout of that item that just doing an update might well be easier.

    Not trying to suggest a universal solution.

  • #5
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,417
    Thanks
    68
    Thanked 102 Times in 101 Posts
    I am a beginner at normalizing, so I will take all the help I can get. Thank you

    I was going to do a check to see if the record turned up empty first, before an update.

    However I just had a great idea, wouldn't this be better?

    playerid
    itemtype
    itemid1
    itemid2
    itemid3
    itemid4
    etc

    That way it's only one record, and you keep updating one single record? Each itemid would be the amount.

  • #6
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by myfayt View Post
    I am a beginner at normalizing, so I will take all the help I can get. Thank you

    I was going to do a check to see if the record turned up empty first, before an update.

    However I just had a great idea, wouldn't this be better?

    playerid
    itemtype
    itemid1
    itemid2
    itemid3
    itemid4
    etc

    That way it's only one record, and you keep updating one single record? Each itemid would be the amount.
    No that is the exact opposite of normalization. Think about it this way if you had 10 itemid columns, what happens when you a player wants 11 items? You'd have to add a new column which would be terrible.

    Don't be afraid of having lots of rows unless it will create 500 million rows or something, you got nothing to worry about. Having one row for each item a player has and the quantity of that item is fine. (To be clear you don't need to have a row for each instance of said item in a player's inventory, you can have a column for the quantity.)

    And you still shouldn't need to store the itemtype, isn't that already specified in your items table? Don't duplicate data.
    OracleGuy

  • #7
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,417
    Thanks
    68
    Thanked 102 Times in 101 Posts
    I suppose I could define itemtype in the script


  •  

    Posting Permissions

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