Database design: ordered set

Question

task_set is a database with two colums(id, task):

 id    task 
  1     shout
  2     bark 
  3     walk
  4     run

assume there is another table with two colums(employee,task_order)

task_order is an ordered set of tasks, for example (2,4,3,1)

generally, the task_order is unchanged, but sometimes it may be inserted or deleted, e.g, (2,4,9,3,1) ,(2,4,1)

how to design such a database? I mean how to realize the ordered set?

Zohar Peled · Accepted Answer · 2016-01-19 11:02:25Z

1

If, and ONLY if you don't need to search inside the task_set column, or update one of it's values (i.e change 4,2,3 to 4,2,1), keeping that column as a delimited string might be an easy solution.

However, if you ever plan on searches or updates for specific values inside the task_set, then you better normalize that structure into a table that will hold employee id, task id, and task order.

answered Jan 19, 2016 at 11:02

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wen liu Over a year ago

“normalize that structure into a table that will hold employee id, task id, and task order”？ then how to order them?

Zohar Peled Over a year ago

order them by the task order column. tables are unsorted by nature, you must keep a column to use in select statements order by clause if you want to keep the order in the database.

wen liu Over a year ago

the problem is how to order them in task order column? assigning numbers to them? let's say 1, 2,3,4. But now I want to insert an item between 1 and 2, so I have to make 2 as 3, 3 as 4 and 4 as 5?

Zohar Peled Over a year ago

I would use an exact numeric type for that, making your items 1.0, 2.0, 3.0 and 4.0. Then when you need to insert a row between existing rows 1.0 and 2.0 you can simply insert row 1.5...

Collectives™ on Stack Overflow

Database design: ordered set

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related