Many-to-many through a join table

What this is about

In an n:m relation (many-to-many), there are many objects on both sides: a group has many roles, and a role is in many groups. A relational database maps this with a third table, the join table: one row per link, with the two ids.

CDMS has two ways to do this:

Two ways to n:m

	MANYTOMANY	Join model
modeled as	a pair of fields of type `MANYTOMANY`	a separate model with two `MANYTOONE` fields
join table	the generator creates it, you do not see it	is a normal model with an API and roles
own fields on the link	no	yes, e.g. valid from/to
in JSON	`"roles": [{ "id": … }]`	`"groups": [{ "group": { "id": … } }]`
good for	a plain assignment, e.g. groups and roles	assignments with properties, e.g. a membership with a time range

Way 1: MANYTOMANY

erDiagram
    ROLE ||--o{ SYSTEM_ROLE_GROUPS : "groups"
    GROUP ||--o{ SYSTEM_ROLE_GROUPS : "roles"
    ROLE {
        uuid id
        string name
    }
    GROUP {
        uuid id
        string name
    }
    SYSTEM_ROLE_GROUPS {
        uuid role_id
        uuid group_id
    }

In the hub, you create a field of type MANYTOMANY on one of the two models. The other side is created with the same type. The generator creates the join table (here system-role-groups). It only has the two foreign keys.

Create a group with two new roles

Request

POST /api/rest/system/group/create
{
  "data": {
    "name": "Vertrieb",
    "roles": [ { "name": "angebot-lesen" }, { "name": "angebot-schreiben" } ]
  },
  "response": ["id", "name", { "field": "roles", "response": ["id", "name"] }]
}

Response

{
  "data": {
    "id": "g1…",
    "name": "Vertrieb",
    "roles": [
      { "id": "r1…", "name": "angebot-lesen" },
      { "id": "r2…", "name": "angebot-schreiben" }
    ]
  },
  "meta": { "error": false }
}

This works because roles on the group has the flag CREATE. You link an existing role with its id: "roles": [{ "id": "r1…" }]. It works the same way from the other side: "groups": [{ "id": "g1…" }] on a role.

Way 2: a join model

erDiagram
    USER ||--o{ GROUP2USER : "groups"
    GROUP ||--o{ GROUP2USER : "users"
    USER {
        uuid id
        string lastname
    }
    GROUP2USER {
        uuid id
        datetime rangeFrom
        datetime rangeTo
    }
    GROUP {
        uuid id
        string name
    }

Here the link is a separate model Group2User with two required fields of type MANYTOONE (user, group) and its own fields (rangeFrom, rangeTo). User.groups and Group.users are 1:n lists that point to the join model, each with CREATE, UPDATE and DELETE.

- name: "Group2User"
  fields:
    - name: "user"
      relationship: "MANYTOONE"
      reference: { id: "m-user", name: "groups" }
      rules: { required: true }
    - name: "group"
      relationship: "MANYTOONE"
      reference: { id: "m-group", name: "users" }
      rules: { required: true }
    - name: "rangeFrom"
      "@type": "DateTimeField"
    - name: "rangeTo"
      "@type": "DateTimeField"

Assign a user to a group

Request

PATCH /api/rest/user/update/u1…
{
  "data": {
    "id": "u1…",
    "groups": [
      { "group": { "id": "e882…" }, "rangeFrom": "2026-10-01 00:00:00" }
    ]
  },
  "response": ["+", { "field": "groups", "response": ["+",
                { "field": "group", "response": ["name"] }] }]
}

Response

{
  "data": {
    "id": "u1…",
    "lastname": "Müller",
    "groups": [
      { "id": "m1…", "rangeFrom": "2026-10-01 00:00:00", "rangeTo": null,
        "group": { "id": "e882…", "name": "Vertrieb" } }
    ]
  },
  "meta": { "error": false }
}

Each entry in groups is a join row. Without an id, CDMS creates it. You do not send the required field user of the row. CDMS sets it itself, because the row is created through the user. group is a plain reference by id.

Removing a link, deleting one end

What happens when you remove something

MANYTOMANY without DELETEMANYTOMANY with DELETEJoin model

When: You leave a role out of roles, and the relation has no DELETE flag.

1

CDMS→Database
deletes the row in the join table
2

CDMS
the role itself stays, and so do its links to other groups

Result: Only the link is gone. This is the usual case.

When: You leave a role out of roles, and the relation has the DELETE flag.

1

CDMS→Database
deletes the row in the join table, just as without the flag
2

CDMS
the role itself stays, and so do its links to other groups

Result: DELETE has no effect on n:m: a role can belong to other groups as well, so CDMS only removes the link. When the group is deleted, its roles stay too. If a role really has to go, you delete it through its own endpoint.

When: You leave a join row out of groups, and User.groups has the DELETE flag.

1

CDMS→Database
deletes the join row Group2User
2

CDMS
the group stays, because Group2User.group has no DELETE flag

Result: If the user is deleted, CDMS also deletes the user's join rows. The groups stay.

Pitfalls

Where to go next

Relation types and flags: Relation types and recursive flags
Which side to write from: Both sides of a relation
What happens to removed members: Lists as target state