2

I'm trying to build a database where an admin

Can create lists that have can have tasks, now the tricky part is, all users can set tasks as completed, but only for himself.

Here's my current scheme

enter image description here

Is this a correct way of handling this?

Miguel Stevens
  • 8,631
  • 18
  • 66
  • 125
  • *"all users can set tasks as completed, but only for himself."* This would be part of the application logic, the database wouldn't need to do this, *right?* Or is your question to incorporate this logic into the database layer? – ʰᵈˑ Jan 11 '17 at 12:55
  • Thanks for your response. I mean the completed field shoud be unique for the user_id and the task_id. – Miguel Stevens Jan 11 '17 at 12:56
  • ...as long as you have a `UNIQUE` constraint on `user_id` and `task_id` in `user_tasks` table, then you're good. http://stackoverflow.com/q/635937/3000179 – ʰᵈˑ Jan 11 '17 at 12:57
  • So it's ok to have an ID field in a many-to-many table and relate that to the user_tasks table? Never done this before, but I don't see any other way. Thanks! – Miguel Stevens Jan 11 '17 at 13:00

1 Answers1

0

You don't really need the completed flag, as there wouldn't be a point in adding a row if the task wasn't completed

create table user_list_tasks_completion (
  user_id int references users(user_id),
  list_task_id int references list_tasks(id),
  primary key (user_id, list_task_id)
);
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152