Better Table Design

Me and a few others are currently working on a game that will be using MySQL to save characters. We presently have a table that holds character information (username, password, id, inventory, and item storage chest).

We are trying to think of the best way to handle the item storage chest data. As of right now, we just stor开发者_高级运维e it in 2 columns. A character's storage chest will have a capacity to hold around 300 items, with each item being able to have a stack size of the maximum value of an integer. We save item id's in one column and the items respective amounts in another.

However, we have been discussing this design and have been thinking of ways to better it. Right now, we have the following ideas:

• Create a new table just for the item storage. In this table, have a column for the player's id number (a primary key) from the users table previously mentioned, a column for the item id numbers, and another column for the amounts

• Create a new table just for the item storage. In this table, have a column for the player's id number (a primary key) from the users table previously mentioned and a column for all of the id numbers and amounts separated by delimiters.

• Leave it how it is

We are expecting around 10,000 accounts to be made and used, with a good possibility of more being created. We are just unsure of what would be the best performance wise

---

In this case having 1 or 2 tables has more impact in scalability than performance. A better approach is having 3 tables. 1 for players, 1 for items and 1 for the relationship player-item.

player
id   name  

item
id   amount description

player_item
id_player id_item

Having this extra table for items will allow you to have item types. You will be able to add items easly extra information e.g description and you will only need to set the amount once.