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.