1

My table lists todos. Each consists of a specific task that requires certain prerequisits (other tasks) to be fulfilled before the todo can be processed. It currently uses a simple counter (i.e. initially set to a specific number. and each completed prerequisite reduces it by 1).

tasks:
id, task
1, A
2, B
3, C

todos:
id, user_id, task_id, prerequisites
1, 1, 1, 3
2, 1, 2, 2
3, 2, 1, 3

I now want to also identify which tasks are a prerequsite and therefore thought of the following solution using a binary incrementing identifier for the tasks:

tasks:
id, task, prerequisite_id
1, A, 1
2, B, 2
3, C, 4

todos:
id, user_id, task_id, prerequisites
1, 1, 1, 7
2, 1, 2, 2
3, 2, 1, 5

I can now tell that todo still requires tasks 1,2,3 to be processed. While 2 needs task 2 and 3 needs task 1 & 3.

I know that a many-many table would be the best practice solution but is there any particular reason why above method is a bad idea?

EDIT: Being able to query for the prerequisites colum is not important. I only want to know when the column is 0. I only check the actual value in very rare cases.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Chris
  • 13,100
  • 23
  • 79
  • 162
  • 1
    All disadvantages of such storage are written up in a question on storing data as comma delimited string values: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad Obviously, some of the drawbacks that are related to strings only will not apply. – Shadow Mar 24 '17 at 11:04

2 Answers2

0

This is not a good idea, because:

Imagine having more than 64 tasks or todos. Suddenly, your data no longer fits in an int64 because the prerequisite_id would become higher than 264, the biggest value you can store in an indexable column of numerical type. You would have to start to use a binary data column instead, and in general programming with bignums is a very messy affair, and you should try to avoid it unless you are dealing with a topic where it becomes necessary.

If you use a regular int32 or int64 column, your value may overflow and an addition may result in a 0 result even though the value is actually nonzero. Depending on your implementation, either any relation with an id above 63 will be ignored, or any relation with an id that is integer divisible by 64 will be.

Either case is probably unwanted.

aphid
  • 1,135
  • 7
  • 20
0

MySQL provides a data type for similar work, called the SET data type. The only difference is that in your solution you don't have to define the set. You just assume that the bits correspond to the task ids.

As @aphid mentions, you won't be able to have any task ids over 63 if you do this (in the SET data type, you're still limited to 64 possible entries, but their values can be anything).

I recommend using the many-to-many table. It's much more flexible.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828