Database question

Hi all,

I have a question about the right way to build the database in my app.

I’m providing organisations with a big list of tasks to complete when they join.

Overall there are 250 tasks for every organisation to complete. For each of those 250 tasks, organisations will always see the same details of that task (e.g. the task name, the description, the tasks steps, the task photo.). This is uneditable for the user and only changed by me.

However, for each organization, they will need to be able to change their own details against their 250 tasks. Things like their task due date , who the task owner is or the task status.

So what’s the right way to build the database?

Is it to have 1 database called “Tasks” with fields including name, description, steps, image etc.
Then another database called “Organizations” - with task due date , task owner or task status? But if that’s the case, how would I connect them up so whenever I change the task name (for example) in database 1, it reflects for all organisations (database 2)?

Currently tearing my hair out! Any ideas really appreciated!

Thank you,
Tom

From the top of my head, I’d look at a 3rd database that connects each specific task to each specific organization. That org-task database would also hold the fields related to an org’s progress, due date, etc on a task.

This is how I would do it too. You’re basically setting up a “junction table” to establish a connection between two data types (the org and the task) and capture additional information specific to that relationship.

Similar use case would be employee to organization… you’d have 3 data types: Person, Company, & Employment. Employment is the junction table, which captures details about a specific person’s employment and the company (start date, position, salary, etc.)

So you’d have 3 data types as well:

  • Organization - fields are whatever info is related to orgs
  • Task - fields: name, description, steps, image, etc.
  • Org-Task - fields: due date, task owner, status (and of course [org] and [task], which are field types “Organization” and “Task”)
2 Likes

Ah yes! Works perfectly! Thank you so much for providing such a clear and helpful answer :smiley:

1 Like