Interesting Database Problem... Bet you can't solve it :)

Need help Database
1

Forgive me, I couldn’t think of a more interesting subject line…

I have a database challenge I’ve been unable to solve.

This is for a project management software I built that includes Project Roles and Project Templates.

Here is what I want to do (I’ll explain why I don’t think it’s possible) I want to create a project template. In the project template I designate which roles are required for this project (Designer, writer, developer, etc)

Then, on the template, I want to assign the tasks to the project role (Design the website > Designer)

Then, when I am working on a live project based on that template, I want to be able to dynamically update tasks so that any task that is assigned to the projects Designer will get changed if I update who the designer is (the user)

So, theoretically…
A Project has ROLES

The role NAME is set on the template (Designer, Developer, Writer)
The Role USER is set on the project

Here comes the problem…

If project roles is a data type, consisting of ROLE NAME and USER…

I will have as many Designer roles as there are projects (if I want them to be unique)

or I can have 1 Designer Role - and it’s always the same designer and it effects all projects that have the role “Designer”

The best solution (that I can think of, and isn’t possible in bubble) is if I could edit the Projects Fields from the front end to add or remove roles

My ultimate workaround was a version of this, I made fields for my common roles and did a text match to update the roles per project (when projects “designer” is updated, do a search for tasks where “Role Name’s Text” matches “designer”)

If anyone has an idea for how to do this better. let me know. Complicated situation and complicated issue (I think)

2

Basically Community | Odoo. Allows you to do pretty much everything you described using the Projects module

3

You can have a datatype for roles and use datatype instead of the text
And use the same datatype in the user’s role, and in the project role
The match will be when project’s role = user’s role … and you can run it on as many roles as you want

4

Is this built on bubble?

5

I think I understand what you are saying, but if multiple users all share the same role…?

6

This is mostly a business decision, how are you defining which designer should be the designer for this project?

7

There is a pool of designers and the creative director assigns the designer based on workload.