...

View Full Version : Database Normalizing



myfayt
06-30-2011, 01:19 PM
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?

Old Pedant
06-30-2011, 10:45 PM
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

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.

myfayt
07-01-2011, 02:44 AM
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?

Old Pedant
07-01-2011, 04:08 AM
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.

myfayt
07-01-2011, 12:46 PM
I am a beginner at normalizing, so I will take all the help I can get. Thank you :thumbsup:

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.

oracleguy
07-01-2011, 05:17 PM
I am a beginner at normalizing, so I will take all the help I can get. Thank you :thumbsup:

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.

myfayt
07-03-2011, 03:04 AM
I suppose I could define itemtype in the script



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum