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!
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.