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!

1 Answer 1

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.

7
  • 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, 2021 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, 2021 at 11:24
  • 1
    One might further consider a first-class notion of Team, that is reusable across Tasks.
    – Erik Eidt
    Mar 21, 2021 at 16:14
  • @ErikEidt: good idea, I think one could simply rename Assignment_User to Team.
    – Doc Brown
    Mar 21, 2021 at 18:10
  • 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, 2021 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.