mysql field now requires multiple values

toodi4

I've been running a mysql db with php for some time. The db has many fields, but for sake of discussion, there are two important fields:

username (varchar pri)
position (int)

"position" is simply a numerical code. The client now wants each username to have more than one possible position. My initial thought was to have add a separate row for each position and change the primary key. But as I said, there are many other fields and this seems like a lot of duplication just to add additional possible position values.

I know this situation must come up a lot, but I've never known the best way to deal with it. Any suggestions?

Piranha

According to normalization rules you should not add many rows, it causes a lot of duplication and a risk that not all rows get updated. And you should not have many values in one column separated with for example a comma, it will mess up searches. The same applies for or many columns with the same information, and you always risk to have to few columns.

The correct way to do it is with a new table storing the id from your current table and the position. When you want a result you just join the tables.

toodi4

Thanks Piranha. That was actually an option I had considered and it is very likely what I will do.

However, I thought I had seen in past projects I had worked on where a similar situation was dealt with by creating a function in php that converted multiple selections to a unique, larger integer. In my current situation, for example, the position values are 0 to 60. I don't believe they'll ever go over 100. So I thought there might be a way that a combination of values like, 15,22 , could be converted to a unique integer value (of many digits) that would then be stored in the db and decoded when the values needed to be displayed. I might be misremembering this, so this might not be a practical solution.

Piranha

That might be a solution. But you should take a few things into account:

The number you need to handle for 60 choises is 1152921504606846976. For 100 choises it is 1267650600228229401496703205376. Remember that it grows exponentially. This way works well when you have up to 10 choises, where you get 1024 choises. More than that makes it hard to handle.
If it grows to 1000 choises you end up with a number that contains 302 characters. And you don't know if it will stop on 100 choises, or if it might get up to tens of thousands.
You will get in trouble when you want to search from the table. Or when you want to count how many have choise 33.