2

Context

I've been searching the internet to see if I could find any other information or posts about what I'm trying to achieve, but I had no luck. I don't know if there's a specific term or whatever for my problem, so I'm going to try my best to explain it below. I'm sorry if it all sounds pretty vague, it probably is.

Problem

I am currently building a website that is intended for multiple users. Each user belongs to a company, and the goal of the website is to assign these tasks to their users to complete. Tasks are automatically generated and all companies can see them. Now here comes the problem that I'm trying to solve: all modifications of that task (deletion, assignment, progression status) are company specific. What do I mean by that?

For example, if we have a task, and company A assigns a user to that task, it should be specific for that company, and have no visible effect for other companies. So company B should be able to assign a user to that task as well. Or if company A deletes a task, it should be still be visible to company B. Or if company A sets the task to the status done, it should not change the status for company B.

How would one tackle this kind of problem? I know you can make a bunch of tables, like task_deletions for example, with column task_id and company_id, but I have a feeling there is a more complete solution for this problem. Any info/help would be greatly appreciated.

Improve this question
2
  • You may want to read about en.wikipedia.org/wiki/Multitenancy if you haven't already. Commented Mar 8, 2021 at 9:52
  • @bdsl I figured there was a term for this problem but like I said I couldn't find it. This helps, I will read about it. Thank you for your comment :) Commented Mar 8, 2021 at 19:23

2 Answers 2

Reset to default
3

One possible solution is to create N rows in the tasks table for each task - one row for each company.

The tasks table would have the columns:

  • id (the unique id of the row)
  • company_id
  • assignee_id
  • deleted
  • status
  • <additional info about the task>

When a new task is added N rows are added to the table.

For example if there are two companies in the system: FooCorp, BarCorp adding a new task will create two rows:

id company_id assignee_id deleted status
1 <id of FooCorp> NULL False Queued
2 <id of BarCorp> NULL False Queued

When a company makes any changes related to a task only the row for that company is updated.

Example FooCorp assigns the above task to Bob:

id company_id assignee_id deleted status
1 <id of FooCorp> <id of Bob> False In progress
2 <id of BarCorp> NULL False Queued

Avoiding the duplication of common data

If there is a significant amount of common data you can create two tables:

  • tasks_common - one record per task
    • id
    • <any common fields>
  • tasks - N records per task
    • id (the unique id of the row)
    • task_id (refers to the id in the tasks_common table)
    • company_id
    • assignee_id
    • deleted
    • status

Handling adding companies

If there is more than one of something 10 more will be added tomorrow - Andrey Bienkowski, 2021, but I'm sure other people have made similar statements before.

When a new company is added should any existing tasks be available to it? If no than this section is irrelevant.

There are two possible approaches to making existing tasks available to a freshly added company:

  • eager: when a new company is added you append a row to the tasks table for each row in the tasks_common table (possibly after some filtering). Beware: in this case the work to add a new company grows linearly with the number of tasks you want to make available to it when it is added
  • lazy: don't add rows to the tasks table when a task or a company is added. Instead add a row to the table the first time a company modifies a task.
Improve this answer
1
  • Wow, great answer and really detailed. I think this might be the best solution to tackle this problem. When reading the first part I was also thinking that there would be a lot of duplicate data, but you explained it well in the second part. Thank you! Commented Mar 8, 2021 at 19:14
1

If any changes made to a task by company A must not be visible to company B, then you don't really have shared tasks. In that situation, it is far easier to give each company their own copy of each task.

For generating the tasks, just re-run the creation logic for each company.

Improve this answer
2
  • Nice! I believe it is shared because the tasks contains shared data like title. Your answer is similar to that of @andrey-bienkowski, but he also addresses the avoidance of duplicate data. But thank you for your comment, good input :) Commented Mar 8, 2021 at 19:22
  • 1
    @Daniel, what should happen if company A requests the title to be changed slightly (e.g. from US spelling to UK spelling), or if they want to have some company-specific information in the title or description? You have to take such future change requests also into account when deciding if information is common/duplicated or not. Commented Mar 9, 2021 at 8:19

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.