MySQL PHP从数据库中选择和编辑值[关闭]

Question

I have a database containing a list of characters for a game. The user is required to search for 5 characters and assign a position to each selected character. This list of 5 with positions needs to be saved so that it can be retrieved later.

In VB.NET I simply created a file that contained the selected 5 with their assigned positions and read the data from this.

Now I am trying to create a web-based version I am struggling as to how I should save the data. The large database is in a MySQL table. I am guessing that it wouldn't make sense to create a new table for each user and have individual rows for their selected characters? My thinking is to have a single table called 'users' then store the selected characters in a column with their position e.g. "player1:pos4,player56:pos302,player322:pos392' etc. then I can split the values later.

Does anyone have any more efficient ideas on how to approach this? I am very new to all this thanks!

Answer 1

I would create 3 basic tables: users, characters, positions, and 1 lookup table: usercharpos with 3 columns: user_id, char_id, pos_id. Each user, character and position should have a unique id as primary key. Then, in the usercharpos lookup table I would associate a user with the 5 chosen characters and the position of each one. For example, we have a user with id = 5, that chose some characters with ids = 1,2,3,4,5 and chose some positions for them with ids = 2,3,6,7,8. The usercharpos would become:

user_id   char_id   pos_id
   5         1        2
   5         2        3
   5         3        6
   5         4        7
   5         5        8

That way, you keep your database normalized and each entity has its own table without multiple values in a field.