DynamoDB Single-Table Pattern: Many-to-Many Relationships
A user belongs to many teams. A team has many members. In SQL you’d reach for a join table. DynamoDB has no JOIN, but the well-established solution is the adjacency list pattern with a GSI inverse.
The same approach handles followers, tags, permissions, enrollments, and product categories. Get this pattern right once and you’ll recognise it everywhere.
Access patterns
| # | Access Pattern | Operation | Notes |
|---|---|---|---|
| AP1 | Get all members of a team | Query | Team management page |
| AP2 | Get all teams for a user | Query (GSI1) | User profile, permission checks |
| AP3 | Check if a user is in a team (get their role) | GetItem | Auth middleware |
| AP4 | Get team metadata + all members together | Query | Dashboard load |
| AP5 | Remove a member from a team | DeleteItem | Admin action |
Five access patterns. Three entity types: Team, User, Membership. One table, one GSI.
Entities
The Membership entity is the edge in the graph. It lives under the TEAM#<id> partition (so AP1 is a single Query) and projects onto GSI1 keyed by USER#<id> (so AP2 is a single Query on the index).
- Team: team metadata, stored under its own partition
- User: user profile, stored under its own partition
- Membership: the join record, stored under the team partition and indexed by user
Table design
Primary key structure
| Entity | PK | SK | GSI1 PK | GSI1 SK |
|---|---|---|---|---|
| Team | TEAM#<teamId> | #METADATA | — | — |
| User | USER#<userId> | #METADATA | — | — |
| Membership | TEAM#<teamId> | MEMBER#<userId> | USER#<userId> | TEAM#<teamId> |
Membership records live under the team’s partition key. The TEAM#<id> partition contains the team metadata record (sk=#METADATA) and all membership records (sk=MEMBER#<userId>). One Query on that partition returns the team metadata and its full member list (AP4), useful for dashboard loads.
The GSI inverts the relationship. gsi1pk = USER#<userId> means querying GSI1 by user ID returns all teams that user belongs to (AP2).
No data is duplicated. Each membership is one record. The GSI projection is all attributes, so the role and joinedAt are available from either the primary index or the GSI.
GSI design
| GSI | PK | SK | Serves |
|---|---|---|---|
| GSI1 | USER#<userId> | TEAM#<teamId> | AP2: all teams for a user |
Sample data
| pk | sk | gsi1pk | gsi1sk | Attributes |
|---|---|---|---|---|
TEAM#t_eng | #METADATA | — | — | { name: "Engineering", createdAt: "..." } |
TEAM#t_design | #METADATA | — | — | { name: "Design", createdAt: "..." } |
USER#u_alice | #METADATA | — | — | { name: "Alice Chen", email: "alice@..." } |
USER#u_bob | #METADATA | — | — | { name: "Bob Park", email: "bob@..." } |
TEAM#t_eng | MEMBER#u_alice | USER#u_alice | TEAM#t_eng | { role: "owner", joinedAt: "..." } |
TEAM#t_eng | MEMBER#u_bob | USER#u_bob | TEAM#t_eng | { role: "member", joinedAt: "..." } |
TEAM#t_design | MEMBER#u_alice | USER#u_alice | TEAM#t_design | { role: "admin", joinedAt: "..." } |
Alice is in both teams (owner of Engineering, admin of Design). Bob is in Engineering only. Each relationship is one record, with no duplication. The GSI columns are populated only on Membership records.
Resolving each access pattern
AP1 — Get all members of a team:
Query(pk=TEAM#t_eng, sk begins_with MEMBER#)Returns Alice (owner) and Bob (member). Sorted by userId lexicographically. Use a ULID for userId if you want join-date ordering from the primary key.
AP2 — Get all teams for a user:
Query(GSI1, gsi1pk=USER#u_alice)Returns TEAM#t_eng and TEAM#t_design. Each result includes the role and joinedAt from the membership record.
AP3 — Check if a user is in a team:
GetItem(pk=TEAM#t_eng, sk=MEMBER#u_alice)Returns the membership record if it exists (Alice is an owner), or a miss if the user isn’t a member. This is the correct pattern for auth middleware: O(1) lookup, never a scan.
AP4 — Get team metadata and all members:
Query(pk=TEAM#t_eng)Returns the #METADATA record and all MEMBER#<userId> records in one call. The __edb_e__ attribute (ElectroDB’s entity discriminator) lets you separate the Team record from the Membership records on the client. Or use an ElectroDB collection (see below).
AP5 — Remove a member:
DeleteItem(pk=TEAM#t_eng, sk=MEMBER#u_bob)One delete. The GSI row is automatically removed by DynamoDB.
ElectroDB entity definitions
export const TeamEntity = new Entity({
model: { entity: "team", version: "1", service: "app" },
attributes: {
teamId: { type: "string", required: true },
name: { type: "string", required: true },
createdAt: {
type: "string",
required: true,
default: () => new Date().toISOString(),
readOnly: true,
},
updatedAt: {
type: "string",
required: true,
default: () => new Date().toISOString(),
set: () => new Date().toISOString(),
watch: "*",
},
},
indexes: {
primary: {
pk: { field: "pk", composite: ["teamId"], template: "TEAM#${teamId}" },
sk: { field: "sk", composite: [], template: "#METADATA" },
},
},
}, { client, table })ElectroDB collection for AP4
Because Team and Membership share the same PK prefix (TEAM#<id>), you can define an ElectroDB collection to fetch both in one query:
const AppService = new Service({
teams: TeamEntity,
memberships: MembershipEntity,
})
// AP4: team metadata + all members, one DynamoDB call
const { data } = await AppService.collections
.teamWithMembers({ teamId: "t_eng" })
.go()
// data.teams[0] → Team record
// data.memberships → Membership[] recordsFor this collection to work, both entities need to be in the same ElectroDB Service and the Membership’s primary index PK must be compatible with the Team’s PK template. The schema above is designed for exactly this.
Why this design
GSI inverse instead of duplicated records. An alternative many-to-many approach stores two records per relationship: one under the team’s partition (TEAM#<id>) and one under the user’s partition (USER#<id>). Both directions are on the primary key, so no GSI is needed. The cost: double the write units per membership change, and two deletes to remove a member instead of one.
The GSI approach stores one record and inverts it via a global index. One write, one delete, simpler consistency. The GSI does cost read units when you query the user’s teams, but at typical membership scales this is negligible. Use the duplicate approach when you’re write-constrained and cannot afford a GSI, or when you need strongly-consistent reads for the user’s team list (GSI reads are eventually consistent).
Membership in the team’s partition. Membership records live under TEAM#<id>, not USER#<id>. This makes AP1 (get all members) the primary access pattern and AP2 (get all teams for a user) the secondary, served by GSI1. The choice of which direction is primary depends on which query you run more often and which you need with strong consistency. Most applications check “is this user in this team” (AP3) and “who are the members of this team” (AP1) more frequently than “what teams is this user in” (AP2). If your access pattern frequency is reversed, flip the primary/GSI relationship.
Role stored on the edge, not the node. The role attribute (owner, admin, member) lives on the Membership record, not on Team or User. The role is a property of the relationship, not of the person or the team. Alice might be an owner in Engineering and an admin in Design. Same person, different roles, different edges.
Deletion is clean. DeleteItem on the Membership record removes the relationship. DynamoDB automatically removes the item from GSI1. No cascade logic needed, no orphaned GSI entries.
The same pattern applies to
User ↔ Tags (tagging). Tag records stored under TAG#<tagId> with a GSI keyed by ITEM#<itemId>. Items query the GSI to get their tags; tags query the primary to get tagged items.
User ↔ User (follows). A Follow entity with pk=USER#<followedId>, sk=FOLLOWER#<followerId> (who follows this user) and a GSI gsi1pk=USER#<followerId> (who does this user follow). Same adjacency list, social graph variant.
Student ↔ Course (enrollment). Enrollment entity stored under Course, GSI by Student. Add enrolledAt and completedAt on the edge for learning progress.
Product ↔ Category (taxonomy). Stored under Category, GSI by Product. Allows “all products in category X” and “all categories for product Y” without a separate category lookup.
The schema shape is always the same: one entity as primary, the other as GSI, the relationship record carries edge attributes (role, joinedAt, status).
Design this visually → coming soon
Visualising adjacency lists (the arrows in both directions, which partition holds what, where the GSI points) is exactly the kind of thing that’s hard to reason about in text and obvious in a diagram. That’s what I’m building at singletable.dev.
Part of the SingleTable pattern library.
