MySQL : Update some fields depending on each other

Question

I have a table with 16 fields as User1, User2, ... User16, ( + some other fields ) , I have a stored procedure named "Register" that get a "User Name" and put it in the first Null field of those 16 fields, for example if User1 and User2 have value, new "User Name" will put in User3, there is no problem

I have another stored procedure named "UnRegister" that get a "User Name" and set to Null the value of field that have given "User Name"

There is no problem in Updating table and putting Null for given "User Name" but the problem is after Updating User(X) to Null, User(X+1) value should put in User(X) field !

An example :

User1 = "U1"
User2 = "U2"
User3 = "U3"
User4 = "U4"
User5 = NULL
...
User16 = NULL

Now , I want to Unregister "U2" and the result values should be like this :

User1 = "U1"
User2 = "U3"
User3 = "U4"
User4 = NULL
User5 = NULL
...
User16 = NULL

I think, I should use 16 CASE statement in an UPDATE statement for all 16 fields and 16 WHEN for each CASE !

Is there another way to do that ?

Is there design problems ?!

In fact, this a tournament with 16 users

thanks ...

Answer 1

It is bad practice to have "arrays" stored as columns.

Instead, have another table, delete the row with 'U2', and number the entries while SELECTing WHERE ... IS NOT NULL.