0

I'm trying to build a web-based task management system. I want different organizations to be able to access different lists of tasks. There are two ways I can imagine going about setting up the database architecture:

  1. All the tasks for all the organizations are stored in a single table. Task records associated with organization X have a foreign key stored for X's ID number.
  2. Each organization receives its own task table. The table has a prefix to identify it as belonging to that organization. A separate table stores associations between organizations and table prefixes.

What are the advantages and disadvantages of 1. vs. 2.? Which one scales better? Also, I'm considering allowing users to turn off and on certain attributes of tasks, e.g., the ability to keep track of how much time you've been working on a task. I was imagining I could best do this by adding or removing fields from the task table. Since different organizations will have different configuration settings, though, would 2. be better suited for this purpose?

user541020
  • 269
  • 2
  • 10

3 Answers3

2

It doesn't sound a good idea to give each organization its own task table. This would require physical database changes when a new organization comes onboard and potential code changes to read from the new tables (although you could change the prefix dynamically). You also have more overhead if you want to change the structure in the future e.g. adding new fields. This will limit your ability to redesign and improve as time goes on. Also spreading the load over multiple tables means you need to keep indexes and other stuff like that in sync. And backing up is made more cumbersome as there are multiple tables involved.

I would have thought the best solution is to make this Data Driven, with a structure similar to that below:

**OrganisationTable**
ORGANISATION_ID

**TaskTable**
TASK_TYPE

**TasksOrganisationStatusTable**
TASK_TYPE
STATUS (Boolean)
ORGANISATION_ID

**TasksTable**
ORGANISATION_ID
TASK_TYPE
DUE_DATE
etc.

This way you can dynamically change the active tasks, set up new organisations, etc. Use Caching in your app to improve performance.

planetjones
  • 12,469
  • 5
  • 50
  • 51
1

The search term for this is "multitenant database" or "multitenant architecture".

There's a brief overview and a link to a useful article from this StackOverflow question.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

I did this thing where I gave each "organization" its own table, as you describe. When a new group first accesses the function supported by the table, the table is created on the fly as an empty copy of a base table. Thus only the base need be "maintained," and if it is changed, the only overhead is to (remember to) delete the baby tables so that they can be recreated according to the changed mamma table.

It works just fine in this instance.

blah
  • 11
  • 2