Forgive me for how exhaustive this explanation is... but I don't want to just give you an answer and not explain the reasoning behind it. Beware for those who enter this post, I'm going to be writing a lot.
Here's my suggestion; but first my assumption: You're using MySQL of some flavor, probably 4.
If you want to track monster-to-item statistics, you don't need to insert into the database every time someone kills a monster. Likewise, you would not want to insert multiple rows for each item quantity. Doing either is a surefire way to quickly bloat your database and surely lag your server.
MySQL is developed with a few optimizations in mind you should take into consideration while developing a plan for interacting with it. While it is fast, its optimized for performance in SELECT queries, not UPDATEs and certainly not INSERTs.
Lets assume you have 10 users on the site and each person is killing 2 monsters every minute with each monster dropping 3 items (5 gold, 1 sword and a stylish ring). If we were to insert a row for each monster-to-item record, we would be making 140 INSERT queries every minute, assuming we went with the one row-per item/monster relationship model previously mentioned. That's disastrous, just think about 100 users. Additionally, as you pointed out, we would have a monster kill count integrity issue by inserting new rows for each relationship. So lets think of a different way to do this...
We need to prioritize what we're looking to actually track here: It's the rarity of items and percentage chance that a player will receive A or B item while killing X or Y monster.
That calls for 3 tables. "monsters", "items" and "items_monsters"; where items_monsters represents a one-to-one relationship in the table. So for example: 100 items, 100 monsters, potentially 10000 items_monsters rows; something MySQL can easily handle if we're using integers for our record IDs.
Why are integers so important? It all boils down to how MySQL searches through it's rows. When using a SELECT query, the WHERE clause will search non-integer values on a bit-by-bit basis rather than the string as a whole (indexes excluded, but not always). This means, if you were to make an id "1-19", it would then do something like this:
MySQL: "Give me all of the records with an ID of 1*."
MySQL: "Give me all of the records with an ID of 1-*."
MySQL: "Give me all of the records with an ID of 1-1*."
MySQL: "Give me all of the records with an ID of 1-19."
MySQL: "Okay, PHP, here you go, I found your row(s)!"
Whereas with integers it looks at the value as a whole, so a record ID of 879 will result in:
MySQL: "Give me all of the records with an ID of 879."
MySQL: "Okay, PHP, here you go, I found your row(s)!"
Now you know, and knowledge is power. Lets get onto the schema.
Code:
CREATE TABLE `monsters` (
`id` int(10) unsigned auto_increment,
`name` varchar(32) null,
`killed` int(10) unsigned default 0,
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
) ENGINE=InnoDB;
CREATE TABLE `items` (
`id` int(10) unsigned auto_increment,
`name` varchar(32) null,
`dropped` int(10) unsigned default 0,
PRIMARY KEY (`id`)
UNIQUE KEY (`name`)
) ENGINE=InnoDB;
CREATE TABLE `items_monsters` (
`items_id` int(10) unsigned default 0,
`monsters_id` int(10) unsigned default 0,
`count` int(10) unsigned default 0,
PRIMARY KEY `item_monster` (`item_id`, `monster_id`)
) ENGINE=InnoDB;
ALTER TABLE `items_monsters`
ADD CONSTRAINT `items_monsters_ibfk_1` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `items_monsters_ibfk_2` FOREIGN KEY (`monsters_id`) REFERENCES `monsters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
First thing is first, as an added bonus I figured I'd toss in the InnoDB Storage Engine which allows for much greater control over the integrity of relational data, the ADD CONSTRAINT statements mean that if a monster or item is updated or deleted, it will recursively update or delete any entries inside of the "items_monsters" table. Good thing to have, no reason keeping relational data if one part of the relationship is deleted. Let MySQL do that work; PHP should pay no mind.
Secondly, UNIQUE KEY on the column "name" in the tables "items" and "monsters". It'd be a shame if we had two separate entries for "Staff of Legendary Destruction". That's just an extra measure of precaution.
Also, having the "unsigned" attribute on integers is great for primary keys and counts since they'll only be going up. Integer values, by standard, support negative and positive numbers. There won't be any negative number record ID's, so unsigned makes that datatype positive integers only. Good stuff.
Finally, explanation of why I personally like this more (not to say it can't be improved upon, because it can... just wait).
When items are dropped, you can check to see if the relationship already exists. If it doesn't, you make an INSERT for that relationship, otherwise you UPDATE a pre-existing row. Eventually as your database fills up with information, you'll be making less costly INSERT queries.
When it comes to your SELECT queries, yes, you will have to make some more complex queries with this set up, but you at least won't be going through hundreds of thousands of rows to get your results. Its the lesser of two evils, in my opinion. Something like this...
Code:
SELECT (`items_monsters`.`count` / `monsters`.`killed`) AS `ratio` FROM `items_monsters`
LEFT JOIN `monsters` ON `items_monsters`.`monsters_id` = `monsters`.`id`
WHERE `items_monsters`.`item_id` = 32;
One of the more simple queries, will get the drop ratio for a particular item for all monsters that have dropped that item. The stopping point on this implementation is it becomes difficult when you're trying to get drop ratios of all monsters, ie: how likely is it that I'll get some item out of all the monsters ever killed? Some homework for you.
Hope this helped! Sorry if I explained too much (or not enough on the homework <3 ) of what you already knew. Just kinda figured someone might stumble upon it and find some useful info! :)
What is the best way to structure this database?
Linear Mode
