Many-to-many with GSI overloading in DynamoDB

Introduction

Many-to-many with GSI overloading helps you connect two types of items in one table using a single index, making queries simple and fast without extra tables.

You want to link students and courses they attend in one table.

You need to find all orders for a customer and all customers for a product.

You want to store different relationships in one place and query them easily.

You want to avoid multiple tables and keep your data in one DynamoDB table.

Syntax

DynamoDB

Table with PK and SK attributes
Global Secondary Index (GSI) with GSI_PK and GSI_SK attributes
Store relationship items with overloaded GSI keys
Query GSI with GSI_PK to get related items

GSI overloading means using the same GSI keys for different item types to represent relationships.

PK and SK are primary keys; GSI_PK and GSI_SK are keys for the secondary index.

Examples

An item representing a student with metadata.

DynamoDB

PK = STUDENT#123
SK = METADATA
GSI_PK = STUDENT#123
GSI_SK = METADATA

An item representing a course with metadata.

DynamoDB

PK = COURSE#456
SK = METADATA
GSI_PK = COURSE#456
GSI_SK = METADATA

A relationship item linking student 123 to course 456, overloaded in GSI to query by course.

DynamoDB

PK = STUDENT#123
SK = COURSE#456
GSI_PK = COURSE#456
GSI_SK = STUDENT#123

A relationship item linking course 456 to student 123, overloaded in GSI to query by student.

DynamoDB

PK = COURSE#456
SK = STUDENT#123
GSI_PK = STUDENT#123
GSI_SK = COURSE#456

Sample Program

This example creates a table with a GSI to store students, courses, and their many-to-many relationships. The last query finds all students in course 456 by querying the GSI.

DynamoDB

CREATE TABLE School (
  PK STRING,
  SK STRING,
  GSI_PK STRING,
  GSI_SK STRING,
  Data STRING,
  PRIMARY KEY (PK, SK)
);

-- Create Global Secondary Index
CREATE GLOBAL SECONDARY INDEX GSI1 ON School (GSI_PK, GSI_SK);

-- Insert student
INSERT INTO School VALUES ('STUDENT#123', 'METADATA', 'STUDENT#123', 'METADATA', 'Alice');

-- Insert course
INSERT INTO School VALUES ('COURSE#456', 'METADATA', 'COURSE#456', 'METADATA', 'Math 101');

-- Link student to course (query by course)
INSERT INTO School VALUES ('STUDENT#123', 'COURSE#456', 'COURSE#456', 'STUDENT#123', NULL);

-- Link course to student (query by student)
INSERT INTO School VALUES ('COURSE#456', 'STUDENT#123', 'STUDENT#123', 'COURSE#456', NULL);

-- Query all students in course 456
SELECT PK, SK FROM School WHERE GSI_PK = 'COURSE#456' AND GSI_SK <> 'METADATA';

Important Notes

Use clear prefixes like STUDENT# and COURSE# to avoid key collisions.

GSI overloading lets you query relationships from either side easily.

Remember to insert both directions of the relationship for full querying.

Summary

Many-to-many with GSI overloading stores relationships in one table using a special index.

You can query related items from either side by using the GSI keys.

This method avoids extra tables and keeps queries simple and fast.