I have a existing DB that is on the to-be-replaced list, that is using the option 1 structure, at the time it worked but now that it has several thousand rows and the equivalent assigned_to row has hundreds of references it is a pain to delete a single assigned_to value from record. It is also becoming slower... and slower... and ...

To find a single assigned_to I have to looking up all assigned_to for a single user involves wildcard query, ('assigned_to' like '%...%'), which did need its own index. Also searching for records is tricky, 1,2,3 would work for '%2%' but 1,22,3 would also get caught. My keys are not single numbers so I have this problem as much.

Deleting a record requires me to query for all records that have the assigned_to value of "x", using the wildcard query, then loop the records, explode to array and remove, then implode and update the data.

In option 2, I see a lot simpler query structure, at the cost of more db rows. You can also build in the relationship (BelongsTo,MorpyToMany, etc) in to Laravel better with this option. (My original code does not use Laravel.)

So I like option 2, but I could be bias.

Hope that helps

You can store the id of the user who assigned a task as user_id in the tasks table. Then you can reference it within your Task model with relationship like this:

public function user() {
    return $this->belongsTo('User');
}

In that second table you can only store id, task_id, and assigned_to rows.

There is a relatively easy solution. Use a modified JSON structure in your assigned_to and use the InStr() function for queries.

For example, your values for id 1 would be {1}{3}{6}{100}, then you could search for all rows containing 1 by using the query SELECT * FROM mytable WHERE InStr(assigned_to, '{1}') > 0.

You could easily do an update as well, which could be used to delete or add a value.

The only thing you need to ask yourself is whether or not you will ever need to sort on this column, if you do, then this way would be a bad idea because it can't be easily or efficiently sorted. If it is simply a reference, then this is an easy way to store the values.