Teams and Orgs backend

[zlavergne]

We are working on implementing a simple model for teams and organizations to satisfy our internal requirements. It's a simple design that we hope can be the base of future teams/orgs infrastructure.

Our current design creates the following tables:

organizations

list of organizations

Column	Type
id	int
name	varchar
visibility	ENUM

org_admins

list of users who can make changes to organizations

Column	Type
user_id (FK)	int
org_id(FK)	int

teams

list of teams and the org they belong to (team doesn't have to belong to an org)

Column	Type
id	int
name	varchar
org_id (FK)	int

team_members

list of users in a team

Column	Type
team_id (FK)	int
user_id (FK)	int
role*	ENUM

* Roles: manager or editor

projects

Change the current organization implementation

Column	Type
~organization_tags~
organization_id (FK)	int

This is a simple implementation that satisfies our internal requirements. With that, we don't plan on building out all the features of teams but rather create a good base on which the rest of the infrastructure can be built.

I would appreciate any feedback, especially if anyone sees any blockers for iteration.

[pantierra]

Thanks @zlavergne for bringing this up. I would like to ping here some people to contribute with your thoughts: @kamicut, @JorgeMartinezG, @uwaiszaki.

[uwaiszaki]

Thanks @xamanu @zlavergne for giving me the opportunity to work on this. I have some thoughts on this. 1) Would it be good to assign multiple organisations to a single team, this can also help in creating some special teams. Eg-> Team of some project managers who can edit any project data in case of emergency. 2) According to this plan a team can contribute to any project of the organisation. Letting them assign a team to the project can give them better control over the projects.

[pantierra]

How many organizations per team and project

Following on @uwaiszaki first question. I think we need to define, whether a team and project shall be associated with exactly one single organization? Or multiple? And is it valid to not have any connection? The current redesign input proposes to have exactly one. @russdeffner, Do you think we can live with this basic assumption?

Roles on a team level

Just a comment and some thought: for us it is the essential idea of teams to have validator and project manager roles based on teams. I would even think about considering to handle all permissions and roles based on teams for all projects. This would allow us to have a much more consolidated/simpler permission handling, as we won’t have to assume two use cases of global and team based validator and project manager roles.

[pantierra]

Thinking about your second comment, @uwaiszaki, I think indeed it makes sense to connect teams to projects; most likely much more than connecting the organization with the project, which anyway can be derived from the teams table.

At least in the case we are assuming one project having exactly one team, which has exactly one organization. Other options would be to have organizations to be assigned to projects, independently from teams. Or having several organizations per team or project, which would need more thinking.

Visualizing the assumption of one project having one team assigned to it, which belongs to one organization, and added the entity relations:

Editable file with draw.io

[JorgeMartinezG]

@zlavergne I have some questions:

1. The organization visibility term refers to visibility of the organization, or visibility of a project created by users that belong to the org?
2. Is possible to find a user within multiple teams/orgs?
3. Also, a user could be admin on multiple orgs?
4. Are projects visible at different levels? Pubiic (All users), team level, org level or private(just project creator)

[uwaiszaki]

Other options would be to have organizations to be assigned to projects, independently from teams. Or having several organizations per team or project, which would need more thinking.

@xamanu Is this structure valid for assigning teams and organisations to the project independently.

[ramyaragupathy]

While we start with one-to-one relationship between tables, practical implementation might lead us to think of one-to-many relationship in many of these. Outlining my thoughts for further discussion:

• Though a mapping campaign could be a joint effort of multiple organisations, a project technically belongs to just one organisation.
• Each organisation can have multiple admins/project managers, but there is only one author per project.
• I assume the visibility field controls visibility for - the organisation and the projects associated w/ the organisation. May be could be either public or private in which case only invited people can check out on organisation details and projects.
• Teams should be formed out of people invited to an organisation - so teams are internal to an organisation and there could be multiple teams within an org.
• Within an organisation, mappers can belong to different teams: eg: I could be in a team of building mappers and also highway validators. Additionally a mapper could be invited to an organisation but still not be part of any of the team.
• A project can have multiple teams mapped to it.
• A mapper could belong to different organisations and multiple teams within an organisation
• Within the tasking manager environment everyone should be given just one role - mapper.
• Any mapper could create an organisation and automatically become the admin for the org.
• Other roles that we already have should be applicable at organisation level.

I am definitely seeing certain downsides to this approach and I'd like to hear from others how they're thinking through this is in the long run.

[zlavergne]

I'll try my best to address everyone's questions and comments. Please let me know if I miss anything!

@uwaiszaki

1. Would it be good to assign multiple organisations to a single team, this can also help in creating some special teams. Eg-> Team of some project managers who can edit any project data in case of emergency.

Rather than adding multiple organizations to a team, we have the ability of duplicating members of a team to a new team that can then be added to an organization. We did keep portability of a team between orgs and projects in mind when designing and I thought that could be added on in the future but wouldn't be blocked by this design. Do you agree?

2. According to this plan a team can contribute to any project of the organization. Letting them assign a team to the project can give them better control over the projects.

I agree but we don't need it for our internal structure. I was actually thinking an ACL structure would be a good way to go forward with those types of permissions.

My basic thoughts on that: teams and projects belong to an org independently and permissions are implemented with an ACL. I should have mentioned this in the original issue since it's what I was thinking would be at the core of future implementations.

@xamanu This may also address a couple of your comments. Something like this would allow a teams to be more transferable between projects (even projects that belong to another org) since we aren't trying assigning a single team to a single project.

@xamanu

Roles on a team level

Depending on how the team-project relationship is implemented, I was thinking that that you could have a team of validators and team of mappers, PM's, etc and that team handled the permissions rather than relying on an additional user roles on each team (which I think is what you mention - just want to clarify) Some benefits I see to this (I might repeat some of yours, sorry):

• a non-regular validator could be put on a validation team and only validate on projects where that team is assigned to validate
• it's a lot less work to control the whole team's permissions rather than each user
• (relate to ^) it could get hard for a user to keep track of their permissions on different teams

@JorgeMartinezG

1. The organization visibility term refers to visibility of the organization, or visibility of a project created by users that belong to the org?

This is talking about visibility of the org. Right now I think the teams and projects would inherit from the org visibility but I think there needs to be more discussion on that part.

2. Is possible to find a user within multiple teams/orgs?
3. Also, a user could be admin on multiple orgs?

Yes and yes!

4. Are projects visible at different levels? Pubiic (All users), team level, org level or private(just project creator)

I'm glad you brought this up. Short answer, yes! Ideally an org could have projects that anyone can edit(public), publicly visible but only editable by team X(partially public), or private.

@ramyaragupathy

• Though a mapping campaign could be a joint effort of multiple organisations, a project technically belongs to just one organisation.
• Each organisation can have multiple admins/project managers, but there is only one author per project.

Exactly what I was thinking.

• Within an organisation, mappers can belong to different teams: eg: I could be in a team of building mappers and also highway validators. Additionally a mapper could be invited to an organisation but still not be part of any of the team.

possible with the ACL implementation I mention above

• Within the tasking manager environment everyone should be given just one role - mapper.

I was actually thinking we should still have "global" roles. I hadn't thought about it much but I imagine we would want to maintain the ability to have a truly public project that doesn't require any permissions beyond what's currently baked into the TM.

I'll add a few more notes that I think may influence future implementations:

• I think for a full implementation on this, there needs to be third tier for "Campaigns" which would be similar to a team in structure. In addition to an additional layer of permissions, this would allow for some large scale stats based on an ongoing campaign
• Could there be a benefit to teams being hierarchical? i.e. a parent team of building team has building mappers and building validators and the building team is assigned to a project. This isn't very necessary with the current design but it may help with transferring teams to projects under another org.. Just a thought.

Thanks for all the great feedback!

[pantierra]

Thanks for all your input. This is a very productive conversation.

I want to recap. To me it seems we have a general consensus and agreement on these basic assumptions:

• Organizations have one or more administrators.
• Organizations have none, one, or many teams.
• Users can be administrator of none, one or many organizations.
• Users can be part of none, one, or many teams.
• Teams have exactly one association to an organization.
• Projects can be configured to be:
  • Public (everybody can map; everybody can validate)
  • Standard (everybody can map; validators of an associated team can validate)
  • Visible (everybody can see; associated teams can do any activities)
  • Archived (everybody can see; nobody can do any activities)
  • Draft (only project managers of the team/organization can see)

Do we agree on these?

And then there are some open questions that I think we should clarify for us:

1. Shall teams handle one role per team or one role per team membership?

• Should teams consist of one role? / Should teams reflect roles under an organization (team for validators, team for mappers, …)? XOR
• Should teams handle roles internally (one team can contain all kind of roles, many mappers, validators)?

2. Are projects connected to teams? And how?

• Shall or must projects have exactly one association to a team?
• Shall or must projects have one or more connected teams?
• Can one project have no team connected to it?

3. Are projects connected to organizations? And how?

• Shall or must projects have exactly one association to an organization?
• Shall or must projects have a connections to organizations through the teams associated with it?
• Can one project have no organization connected to it?

4. Are teams handled separately or do they replace the current “permission system”?

• Do we want to support global roles?
• Can we have teams for everything? (including migration path)

5. How do we use campaigns?

• Are campaigns a group of projects spanning over different organizations and teams?

[kamicut]

I'm going to write some thoughts on teams, summing up ideas from the February TM design meetings, and also pooling from Development Seed's ongoing work on our own "OSM Teams" which are a way to have portable teams across the OSM ecosystem (through OAuth). We want these teams to be compatible with TM teams, so I'm sharing what we've come up with.

orgs

I want to push back on an assumption here, is the need for both organizations and teams. I'm not sure what the pattern is that organizations are solving, but it seems that having hierarchical teams is a solution we came up with during the TM talks.

So if we had TeamX be the overarching team with a certain level of visibility and projects, then TeamX/country would be a subset of members only for that country with specific projects. Or TeamX/country/validators an even smaller subset.

The reason I'm bringing this up is to reduce the friction on new signups to understand the hierarchy before starting to map. If they join say the MissingMaps global team (and not an org, followed by a team), that's all they need to do so that they have access to projects. It's only after further specialization that we can filter their projects.

visibility

For visibility, the way we've thought about it is that it's on the team level, similar to Facebook groups:

1. Public - searchable
2. Private - searchable but members not listed
3. Unlisted - cannot be found in search

joining

A team can be either open or closed to new members joining.

user permissions

A user can allow or deny to be added to new teams without a request to join

permissions

For permissions we've thought of the following:

• The creator of a team is the first moderator of that team
• The admin can assign team moderators and invite mappers to a team For simplification, we are using Whatsapp rules: if an admin leaves and there are no admins it goes to the next person chronologically.

projects (TM specific)

I haven't thought about the visibility of projects within the tasking manager, only the team structure. It seems that teams should have projects with inherited visibility if created as part of a team. I do like @xamanu's configuration.

roles (TM specific)

I think this can be handled with subsets like I've stated above.

N.B: https://github.com/developmentseed/osm-teams has some but not all of our code (specifically the OAuth bits which were pulled somewhere else). We want to open source this soon so if you are interested please let me know.

[pantierra]

Thanks for your input @kamicut! I think the teams of teams ideas is worth while thinking through it. And I tried to mock-up how a compatible structure to your proposal on the TM side could look like.

tm-teams-of-teams.zip (you can edit using draw.io)

@zlavergne what do you think about hierarchical teams? Would this fit for your use case, too? Or are there any downsides that you can see from the perspective of your internal requirements?

---

On the TM side of things, I would like to let us think about where the TM roles are getting applied:

• Team of roles (team of validators, as suggested by @kamicut and @ramyaragupathy)
• Global roles (in all teams being a validator based on the general role)
• Local team roles (validator in team x, normal mapper in team y)
• Project based team roles (for this project, team x is going to be the group of validators)

[JorgeMartinezG]

I think there is a problem when implementing teams of teams, and it is related to permissions. If ACL (https://www.ory.sh/docs/keto/engines/acl) is going to be implemented:

1. Permissions necessarily need to be set at a team (_teamid field from db) level, not user (_userid) level.
2. When checking permissions, this approach will be computationally expensive. For instance, if we have an user that belongs to a team which is two levels below the team with no parent_id, which is the organization, the server will need to check on each level of the hierarchy if is allowed to perform any action of the given resource.

@xamanu related on the TM side of things, team_members table allows us granular roles. For instance, a user could be a validator in tem x, but could be an admin on team y. We could use the function field to set different roles for a same user. I want to confirm if teams implementation is going to be a separate service. If yes, we can keep same user roles for TM forks that do not need teams feature.

Let me know your opinions

[kamicut]

Just to clarify two points:

1. On subteams: subteams are just regular teams that can inherit visiblity and other permissions at creation time. They get their own projects and behave independently; the only validation needed is that the list of members is a strict subset of its parent. This is a UI problem, not a permissions problem. This in my mind simplifies things so that there is no complex ACL going on.

2. On external implementation of teams: Our approach with osm-teams is to create lists of users with simple roles (basically who is allowed to add members), and leave all other permissions to implementing apps. There are two possible ways the TM could integrate with osm-teams (if they so wish): a) by implementing the API calls to create / add / delete members and roles using the osm-teams API or b) creating a syncing or import mechanism. In both cases, the TM specific roles have to be created on the TM side, but TM has flexibility in how loosely or tightly coupled it wants to be to an external service.

[zlavergne]

Thanks for the detailed feedback @kamicut.

Organizations

I'm not sure what the pattern is that organizations are solving, but it seems that having hierarchical teams is a solution we came up with during the TM talks.

I think hierarchical teams are a great addition but they aren't necessary for our internal implementation. I do think though that orgs will be useful in the Tasking Manager. Let's assume from here on that teams will be hierarchical.

Having multiple nested teams with one master team acting as the org vs. having a separate org structure is mostly semantics since their purpose is the same. The benefit I see to orgs is that they can function as an umbrella for teams, projects, and campaigns as well as a sort of "logical separation". Each of them (teams, projects, campaigns) will have their own relationships to each other but being able to group them all under a singular organization seems valuable.

IMO it would be semantically confusing (both to developers and users trying to understand the structures) if there was a master team that had campaigns, projects and teams.

@xamanu how does that line up with how HOT organizes everything and what you were thinking?

An important distinction is that teams handle the permissions and orgs function as an umbrella structure to organize projects, teams, campaigns logically and in the future allow for stats at the highest level to be gathered.

The reason I'm bringing this up is to reduce the friction on new signups to understand the hierarchy before starting to map. If they join say the MissingMaps global team (and not an org, followed by a team), that's all they need to do so that they have access to projects. It's only after further specialization that we can filter their projects.

I never thought they would need to join an org then team and other teams in succession. Rather that they would join or be invited to exactly whatever team(s) they wanted to.

Visibility

A team can be either open or closed to new members joining.

@kamicut would this be determined by the teams visibility or separate?

Teams Service

It's great to see your osm-teams work!

b) creating a syncing or import mechanism.

I think this is the better option. My initial thought on the process: you give the TM an optional OSM-Teams API key that polls the api and updates the TM DB. I think it's important to have the option to create a non-OSM-Teams team too.

@xamanu's questions

1. Shall teams handle one role per team or one role per team membership?

I think having roles at the team level would allow for easier modularity and easier transfer of teams. It's also a lot less work to change one role than change 20 different users' roles ;)

2. Are projects connected to teams? And how?
   • Shall or must projects have exactly one association to a team?

I definitely don't think they should be restricted to one team. Especially if the teams handle the roles. You would have to be able to have more than one project<->team association.

• Shall or must projects have one or more connected teams?
• Can one project have no team connected to it?

My initial answer is no because I think to not deter new editors, they shouldn't be required to join a team to map a public project. But perhaps it's a matter of adding them to some general team automatically but still maintaining the requirement on the backend to be related to a team.

Are projects connected to organizations? And how?

• Shall or must projects have exactly one association to an organization?

I have similar feelings as I do above for teams.

• Shall or must projects have a connections to organizations through the teams associated with it?

I think this would just be handled through inheritance, right?

• Can one project have no organization connected to it?

I think so.

4. Are teams handled separately or do they replace the current “permission system”?
   • Do we want to support global roles?

Yes. For the same reason I think projects shouldn't have to be associated with a team.

• Can we have teams for everything? (including migration path)

Perhaps... I brought this up in my response about teams<->projects.

[pantierra]

Thank you all for your feedback and input! It looks like we are moving forward in defining the structure and I see a lot of good agreements.

TM database structure

For the HOT perspective we started writing up some short user stories around different scenarios we see people using teams and organisations in the TM. We validated these user stories against the ideas.

As we are discussing, the initial proposal by @zlavergne makes a lot of sense, and validating it against our user stories and derived requirements, we would like to propose to extend it with some more fields, we talked about here in the conversation. And we think about adding a project_teams table, because we require a bit more flexibility and on the same time being able to define explicitly which teams are going to be connected to a project (compare with user stories number 4 and 5). See the following entity relationship diagram:

tm-teams-and-orgs.zip (you can edit using draw.io)

Statements

I would like to express some of the contents of the diagram and some assumptions into human language. This might be helpful for more people to understand the diagram and it is a second path for us to make sure, we are talking the about the same details.

• Teams have exactly one association to an organization.
• Organizations have one or more administrators.
• Organizations have none, one, or many teams.
• Users can be administrator of none, one or many organizations.
• Users can be part of none, one, or many teams.
• Projects can have none, one, or many teams.
• Projects have exactly one association to an organization.
• Organization administrators can create, modify and delete teams of their organization and manage their members.
• Projects assign teams to certain roles (e.g. teams a and b for validators, team z as project managers)

osm-teams

Nice. We are all on the same page, about the desirable integration of OSM Teams, but to realize this an optional extension for the Tasking Manager. The implementation of the component that connects the Tasking Manager to the OSM teams api can follow one of the "two possible ways the TM could integrate with osm-team" that @kamicut mentioned and can map the osm-teams structure to the TM database. There we will have to decide whether organisations are teams with the highest hierarchy, or whether organisations are something the TM handles internally.

[kamicut]

team imports

Quick reply re:

you give the TM an optional OSM-Teams API key that polls the api and updates the TM DB. I think it's important to have the option to create a non-OSM-Teams team too.

The way I'm designing the auth is that a user has their teams, and they assign public vs private. So users would have to have access tokens to pull their own teams that are private. This is how for example you can pull a private photo from instagram into facebook, and so facebook stores instagram scoped tokens for each user (integrated apps).

Given that an imported private team has a specific owner and permissions attached, @xamanu do you think this will map cleanly to a TM team?

visibility vs membership

A team can be either open or closed to new members joining.

@kamicut would this be determined by the teams visibility or separate?

Visibility in my view is whether you can see the members of a team or not (public vs private vs unlisted), it's a privacy setting. Open vs Closed is about whether you need to approve members to join or not. I'm also using Facebook terminology here Privacy Settings, Membership Approval

[verdy-p]

for you info I discussed that by email (on the hot mailing list) and made the schema clearer to interpret graphically (all foreign keys are referencing table from left to right).

This starts from 11 tables (currently presented) with a normalization problem (yellow highlighting)

Then merges two tables (campaign_projects and projects) to solve that normalization ambiguity.

Then merges two other ones (teams and admins) (with shortened table names and some additional fields for managing history/future planning of roles in teams).

[verdy-p]

Annotated version of the last image (schema unchanged). Made a bit clearer by "moving" the "users" table to avoid the "spaghettis".

The yellow highlights show the comments for what I received in emails about the fact that teams are not necessarily attached to an organisation (that's why "teams" are not "relations" and have their own primary key and can then have their own name: they can be created independantly of any organisation; if they are attached to an organisation, they teams may still be made invisible, and unsubscribable, such as a "team" of "admins" for that organisation).

And there are additional date fields that can be added to the merged "members" table (which merges the "organisation_admins" table and the "team_members" table into just one for all) which can be used for history/planning of past/future teams (including teams of admins)

Note that users may not partiticipate to a "project" without first joining a "team" (not necessarily attached to an organisation).

But they won't be able to participate to a "campaign" (which belong to a mandatory organisation), unless

• they are members of a team attached to an organisation (such team is not necessarily a team of "admins") or
• they just show an "interest" via the "campaign_orgs" (I'm not sure about the horizontal link from "user_campaigns" to "campaign_orgs", may be it should just link to "campaign" further to the top right on the graphic schema: there's probably a need to add the two "subscribe" and "visibility" fields in the "campaigns" table as well, like in the "teams" table)

If I take into consideration this comment in the second bullet, the schema is adjusted to:

which is the clearest one. It is not annoted by yellow highlighting arrows but the foreign key reference from "projects" to "campaigns" may also be null (projects are generally created first, then may become part of a campaign for an organisation)

May be we could still abbreviate again the name of "user_campaigns", initially "user_interest_campaign", to just "participates" or "participations" (that table already has a "role" field to give the meaning for a simple interest or a full participation; as well it could have date date fields for participations or interests for each role if we want an history). So that "participations" relation table would be very similar to the "members" relation table, with similar fields, the difference between the two being that these participations are personal voluntary attachment for the user, and not related to the fact of joining any team, possibly part of an organisation)

Finally, note the presence of "roles" or "functions" in multiple tables: they can also become objects in a "roles" table (roles may be independant of organisations, or defined specifically for them)

This is exactly the same schema as just before except that "enum" types for "role/function" fields become foreign key references (role_id) to a new "roles" table. And each organisation can have its own roles, separate from roles used by other organisations, and roles for the general public (for teams or independant users, not attached to any organization).

These "roles" are also always independant of "teams" (attached to an organisation or not). The same role will be shared by several teams of the same organisation, or will be shared by all teams not attached to any organisation. Such roles can be some adminship or responsability/function: e.g. chief leader (of the organisation), team creator/leader, legal contact, communication, coordinator, moderator, technical support... So any user that is member of any team can have a role defined in that team.

As well a role can be assigned to independant users participating to a campaign (at top of the shema), without being part of any organisation that created or endorsed that campaign, where the endorsement is indicated by the "campaign_orgs" relation table, which may also have additional fields such as dates of endorsement and some other data, probably some freeform text in a blob, or an URL to some internally hosted webpage).

Such roles (assigned "user_campaigns" may be "interested in", active, coordinator..., frequently these roles will be temporary or will change over time, or may be abandonned, so the user_campaigns should have dates fields: if the date_max is given, that role has ended but if resurrects, a new "user_campaigns" entry will be made with a new "date_min" field where the user is accepted again in the same campaign). Roles assigned to users joining any campaigns are also independant of roles of users within teams (even if the team is attached to the same organisation), because campaigns are already endorsed by multiple teams.

As well it is possible to create a "rejected" or "banned" role (each organisation can define their own rules for this which can be shared by all its teams). Another role useful will be "standard member" (with no specific adminship), but if this is the default for any kind of memberships, we can make all the "role_id" foreign keys allwoed to be null. Users may then have multiple memberships for the same team, but with distinct roles.

The memberships for any user can viewed as a grid for each organisation: in rows the list of teams of that organisation, and in columns the roles they take in that team. This eliminates completely the need to have "groups of groups" (and complex recursion), by just using two foreign keys instead of just one for a single group and will allow efficient queries.

Outside organisations (everywhere the organisation_id foreign_key is null, notably for indepedant teams, or for the "user_campaigns"relation) the roles also form a simple 2D grid (the non-organisational teams to which users can join with one or more roles). Here again, no need for recursive and hierarchical "groups of groups", just use two foreign keys (here also the "role_id" foreign key may be null to mean a default role for standard members with just a basic "interest" but no other role desired).

The roles assigned to users will be first dependant to "projects" (created for non-organisational teams) but will change if the project is endorsed by one or more organisations assigning the project to a "campaing", but these roles for users will always independant of organisations (which can only control their own teams, and which "users" can be members of these teams).

[verdy-p]

Note: I used MS access locally only because it allows fast simulation with real schemas (that can be tested with random data). I don't intend to propose you to use Access of course for this development. I don't care much about the symbology of the graphic schema, as it does not matter for this discussion.

(There are different graphic conventions used depending on countries/cultures, MS Access is using an oversimplified presentation which unfortunately does not mark or differentiate the cardinalities in both directions of the rendered links, and not even the "one-to-zero-or-one", "one-to-one", "one-to-zero-or-many" and "one-to-one-or many" , which are necessary for normalized schemas, in only one direction of links).

In all my graphic schemas above, the direction of links are to be read left to right (in other words, foreign keys are used in the table to the left of the link, and they are refering to the primary key of the table on the right of the link, so that all the unannotated left-to-right links can be read as "zero-or-many to zero-or-one"; they could be restricted to "zero-or-many to one", or "many to one" for short, by differentiating only the foreign key fields whose type can be null).

Other graphic convention use the reverse direction for the presentation, and their left-to-right links are read as "one to zero-or-one" (or "one to one" for short if we ignore nulls) or "one to zero-or-many" (or "one to many": this term is ambiguous as it does not state that there should exist at least one, but most database engines do not enforce it, except by enabling runtime triggers, or by running external batches that will delete the remaining unlinked data for enforcing a strict "one-to-many" relation, or that will some add "phantom" entries for the missing item in the left part; generally this is not needed because databases queries can perform outer joins).

You can redraw the schema as you want with your prefered graphic tool and you are free to use any database engine that matches your goals.

[pantierra]

I want to report back some progress:

• The backend work from @zlavergne and @uwaiszaki has been merged into the code base #1858
• The frontend and user flow has been designed by @hexiaok and we have a click-dummy for you to check out.

This functionality is planned to be included into the 4.0 release of Tasking Manager.