2

I have created an application in which users and tasks can be entered.

A task should be be assigned to one or more users. If more users can do a task, the task should be assigned in such a way that it can be done by person A OR person B, or that person A AND person B have to do the task together.

In addition, more complex combinations are also conceivable: A task could be carried out by (person A AND person B together) OR (person C AND person D together).

My thought on in which format could best save the assignment of people to tasks in a database: I should create a relationship table, which basically shows which people are related to which tasks (m: n). This means that a database query can be used to select which tasks are assigned to a person (together with other people). In addition, I have to save a string with the task that specifies which combination of people are permitted - e.g. (A AND B) OR (C AND D).

In which format or which notation should I save this string in the database, so that also complex relationships can be processed (saved, read) easily, eg. ?

(I'm currently even not sure how I should present this to the user so that it can be understood easily, but that's not the focus of the question here.)

Thank you very much for every hint/thought in advance!

BogisW
  • 131
  • 4

1 Answers1

4

If you already have users and tasks as entities in a relational schema, I think it makes most sense to store the assignments as entities and relations as well. This is pretty straightforward:

  • each task has a list of (potential) assignments

  • each assignment is a set of one of more users (the ones who carry out the tasl together.

For example, your first example describes three potential assignments {A}, {B}, {A,B}, the second one {A,B}, {C,D}.

That leaves you with a db schema like this one:

 +------+     +------------+     +---------------+         +----+
 | Task |----<| Assignment |----<|Assignment_User| >-------|User|
 +------+     +------------+     +---------------+         +----+

This way, you can using standard SQL queries for selecting whatever combination of data you like to retrieve.

An notation which puts the assignments into a string, or one which requires parsing is something I would utilize for input and output with a human, but it would not be my first choice for storing the assignments in the DB.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
  • Thank you very much! In fact, I had thought about even more complex relationshios, like NOT, NAND, and so on. But I think these considerations were too complex, as I didn't even took them into account in my examples. So, your answer made me think easier (reduction of the problem to a series of logical conjunctions, disjunct them) and it solves the actual problem/question. Of course, anything that can be queried with standard SQL is much more performant than anything that has to be computed instead. – BogisW Mar 21 '21 at 11:22
  • PS: I'm afraid I don't have enough reputation to upvote your answer, I'm sorry for that! – BogisW Mar 21 '21 at 11:24
  • 1
    One might further consider a first-class notion of Team, that is reusable across Tasks. – Erik Eidt Mar 21 '21 at 16:14
  • @ErikEidt: good idea, I think one could simply rename `Assignment_User` to `Team`. – Doc Brown Mar 21 '21 at 18:10
  • Don't we need another entity to represent a reusable Team (i.e. to normalize the notion of a team that might be assigned to multiple tasks)? – Erik Eidt Mar 21 '21 at 18:34
  • 1
    @ErikEidt: maybe, but the OPs question does IMHO not provide enough context to make a sensible decision wether that's really required or overdesign. – Doc Brown Mar 21 '21 at 18:49
  • Thank you very much for all your comments, I've updated my database scheme accordingly. Thank you as well for the Team discussion; my database consists of about 40 tables, including teams/groups (which dont [necessarily] relate to the combinations mentioned in my question). My request was simplified in order to address the particular issue. (PS @Doc Brown: I finally was able to upvote your answer, too.) – BogisW Mar 28 '21 at 19:34