Designing the schema for an inventory system...should I calculate quantity or keep track of it?

Question

I'm doing an inventory for a game, rather like a FF-style game. I have two design in mind, and wonder which is better

1. Keep track of quantity as a field; the schema would be: item ( <item_id>, <character_id>, quantity ). If a character equip an item, or remove an item from his inventory, I would need to make sure the quantity field is correct.

2. Each item takes up a separate row. I will use GROUP BY to calculate the quantity of each type of item (by item_id). Equipping and removing items become more straightforward, though if the character has ten healing potions they would take up ten rows.

There are current no space constraint or limit on the programming (using MySQL). Which is the better system for the long run?

Answer 1

It sounds like there's a one-to-many relationship between characters and items in there somewhere.

I would recommend starting with quantity as a derived item using GROUP BY. I'd only change it if performance became an issue.

I don't like the design where an item has to keep track of its quantity, but if you had to do it you could accomplish it with a trigger in MySQL to update the quantity automatically on INSERT, UPDATE, or DELETE.

Answer 2

Hi, The design of the database is determined by what sort of queries will need to be executed.

In this case, if all that you will need is to get the quantity for item x for character x, then, option one will work, but if you will need:

the quantity of item x

who's item_attribute1 = x

AND item_attribute2 = X

AND character_attribute1 = x

AND character_attribute2 = x

You will need to go for option 2.

In option 1, all items, and all characters are sharing the same quantity, despite them having different attributes.