Modeling an inventory system in SQL

Question

I'm working on a multiplayer game played through the browser. It's not a real-time game which is why I decided to go with a database, however I'm pretty weak with SQL and starting to run into problems.

Right now I'm working on the inventory system, the requirements being:

• A Player can have 0 or more of each item
• Certain items have a max limit
• Certain items can only be bought with a minimum XP

I have a table for my Player and Item. How should I store the inventory relationship? I feel like I'll need a separate table for this, rather than a simple ManyToMany relationship, but I'm not sure what it would look like. My first thought is a table that has a player ID and the counts of each item that player has. I would need a column for each item I have which doesn't seem very maintainable... does that seem right?

And what's the best way to ensure the max limit and minimum XP constraints? The proper way seems to be a constraint in the database, but I can't think of a way to query items that the player is eligible to buy.

Thanks!

Answer 1

You will need three tables: Player, PlayerInventory and ItemType.

ItemType stores the shared information by all instances of that item, like the name, maximum stack size, minimum exp, appearance and other properties. Primary key should be an integer-id. The advantage of storing these properties centralized is that if you decide to change the game balance and modify the value of "Gold Bar" from 500 to 600, you only need to modify one line of one table and not run a job over your whole inventory data.

PlayerInventory then represents what item stacks the players actually have with the current amount for that player. Depending on whether you want an auto-ordered inventory or a manually ordered inventory with n slots, primary key would be PlayerId, ItemType with another column Amount or PlayerId, SlotNumber with two additional columns ItemType and Amount.

To get information about the complete inventory of the player, you would do an inner JOIN:

SELECT ItemType.Name, PlayerInventory.Amount
FROM PlayerInventory
JOIN ItemType ON PlayerInventory.ItemType = ItemType.Id
WHERE PlayerInventory.PlayerId = ?;

The maximum stack limit and minimum exp stuff are business rules I would enforce on the application layer. Constraints are a more advanced feature of SQL I wouldn't recommend to bother with when you are "pretty weak with SQL". They are also not easy to deal with because you usually want to handle a constraint violation in your application anyway.

If you want all item types a player id x can buy, you would do:

SELECT * FROM ItemType WHERE MinExp <= ( SELECT Experience FROM Player WHERE PlayerId = ?);

But usually you might also want to show the player the neat stuff they could buy when they progress further in the game, so you would often select all anyway and then check on the application which ones are displayed as unavailable.

Answer 2

How should I store the inventory relationship?

In the table for Inventory, have a column for PlayerID and one for ItemID, either use rows in the inventory table or have one more column for Count or something. Have the game logic set the restriction as in how many of each item a player can have.

And what's the best way to ensure the max limit and minimum XP constraints?

Are you refering to an item level, that a player needs to be at least level N to be able to use? In the Item table, have a column for minimum level a player needs to have to be able to use that said item. Same thing applies here, have the game logic set restrictions.

An example query can be as simple as: select ItemName, ItemImageUri from Items where ItemLevel < 15