Sub-issue of #3. Builds on the FK plumbing from the many-to-one / one-to-many sub-issue; the most involved increment.
Scope
Support many-to-many (e.g. Student ↔ Course), where neither side holds a scalar FK and the association lives in a separate junction table the library creates and maintains.
Design
- Declaration. A macro on each side (or one declaration generating both directions), e.g.
MEMBER_COLLECTION(Course, courses) on Student and MEMBER_COLLECTION(Student, students) on Course. These are not columns on either table; they declare a link to be backed by a junction table.
- Junction table. The library auto-creates and names a link table — e.g.
Student_Course with columns student_id INTEGER REFERENCES Student(id), course_id INTEGER REFERENCES Course(id), a composite PRIMARY KEY(student_id, course_id), and ON DELETE CASCADE so removing either endpoint cleans up links. Naming must be deterministic and collision-resistant (define ordering of the two record names). This is registered/created alongside user tables in Database::Database, respecting the same dependency ordering from the many-to-one sub-issue.
- Mutation API. Junction rows are managed by the library, not the user:
db.Link(student, course) / db.Unlink(student, course) (insert/delete a junction row), idempotent on the composite key.
- Fetching.
db.FetchRelated<Course>(&Student::courses, student) joins through the junction table to return the related records; the inverse works symmetrically.
- Integrity on delete. Deleting a
Student or Course must remove its junction rows (ON DELETE CASCADE); add a test that a deleted endpoint leaves no orphaned links.
Acceptance criteria
- Declaring a many-to-many on both sides auto-creates a correctly named junction table with composite PK and cascading FKs.
Link/Unlink add/remove associations idempotently; FetchRelated returns the correct set in both directions.- Deleting an endpoint removes its junction rows, leaving no orphans (test included).
- README "Relationships" section extended with the many-to-many form and the junction-table naming rule.
Out of scope
Association attributes (extra columns on the junction row beyond the two FKs) — track separately if needed.
Scope
Support many-to-many (e.g.
Student↔Course), where neither side holds a scalar FK and the association lives in a separate junction table the library creates and maintains.Design
MEMBER_COLLECTION(Course, courses)onStudentandMEMBER_COLLECTION(Student, students)onCourse. These are not columns on either table; they declare a link to be backed by a junction table.Student_Coursewith columnsstudent_id INTEGER REFERENCES Student(id),course_id INTEGER REFERENCES Course(id), a compositePRIMARY KEY(student_id, course_id), andON DELETE CASCADEso removing either endpoint cleans up links. Naming must be deterministic and collision-resistant (define ordering of the two record names). This is registered/created alongside user tables inDatabase::Database, respecting the same dependency ordering from the many-to-one sub-issue.db.Link(student, course)/db.Unlink(student, course)(insert/delete a junction row), idempotent on the composite key.db.FetchRelated<Course>(&Student::courses, student)joins through the junction table to return the related records; the inverse works symmetrically.StudentorCoursemust remove its junction rows (ON DELETE CASCADE); add a test that a deleted endpoint leaves no orphaned links.Acceptance criteria
Link/Unlinkadd/remove associations idempotently;FetchRelatedreturns the correct set in both directions.Out of scope
Association attributes (extra columns on the junction row beyond the two FKs) — track separately if needed.