Best notation to store/read/compute complex relationships

Question

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. ?

• Reverse Polish notation / Postfix notation
• Infix notation
• Polish notation / Prefix notation
• By using database relations instead? If so, how?
• Others?

(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!

Answer 1

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.

• BogisW: 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: PS: I'm afraid I don't have enough reputation to upvote your answer, I'm sorry for that!
• Erik Eidt: One might further consider a first-class notion of Team, that is reusable across Tasks.
• Doc Brown: @ErikEidt: good idea, I think one could simply rename Assignment_User to Team.
• Doc Brown: @ErikEidt: maybe, but the OPs question does IMHO not provide enough context to make a sensible decision wether that's really required or overdesign.