I’m struggling with designing a proper database architecture for my problem.
What I have:
Player with unique playerID and other information (in the database). Items with a unique itemID and other information (hardcoded in my app)
What I need:
A database architecture to find out how many items from itemID player xy has in his inventory.
I tried to make a table with the columns:
PlayerID:int, ItemID1:int, ItemID2:int ... ItemIDxy:int
but if I think that’s a bad design, because its kinda annoying to add new items.
Then I came up with the following table:
PlayerID:int, ItemID:int, ItemAmount:int
But then I have multiple (up to 50) rows per User, and if the Usercount gets into the 500k+, won’t I get trouble with speed/size ?
As you can see I’m rather new to the database stuff so I would like some suggestions.
Or do I overreact and my second approach is reasonable?
Advertisement
Answer
One approach is to do as you thought and create a table that’s like PlayerItems, where it’s got two foreign keys in it (PlayerId, and ItemId), and then a third column which is Amount.
Another approach is to use something like JSON or XML column on the Player table. Or you could use your own minified schema that your app knows how to parse.