Database design concerns

Databases
1

hello

i’m trying to create a multi-player browser-based RPG style game using php and mysql. being a complete novice progress is slow but i am making some. before i proceed with my latest task, i thought it would be a good idea to check here to see whether i’m approaching the problem in the right way.

i have one table - players - that contains basic information about my users - id number, username, join date, statistics etc.

another table - items - contains details about in-game “items” that will be available to users and can be “owned” by them - itemid, itemname, itemvalue, etc

the thing i’m concerned about is how to actuallly record which items each player owns - each player will potentially own various quantities of hundreds of items.

initially i planned on adding extra columns to the “players” table, but there would be hundreds of new columns - it just didn’t seem right to add hundreds of extra columns especially when a lot of them often wouldn’t even contain any data - i also couldn’t see a way to record the quantity of each item.

if anyone has any suggestions as to the best way to approach this problem, please let me know. i’m at a very early stage so redesigning the whole thing wouldn’t be a problem. i’m open to all ideas and suggestions.

thanks

2

you need a third table – player_items

this table will have a composite primary key consisting of two columns, the player_id and the item_id

there will be as many rows for each player as items for that player

if a player can own several instances of an item, then there would be an additional column in this third table, called quantity or summat

3

thanks r397 - i was thinking that would be the way to do it - until now i’d never even heard of a “composite primary key” though so i was struggling to understand how the table would even “work”. i guess i have another thing to study now!

thanks for your help.