Katkat
Back

Many-to-Many Schema Design With a Join Table, Composite Primary Key, and Queries

ScribdSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteData Manipulation (SQL/Python)

Last updated

Design a relational schema modeling a many-to-many relationship between documents and tags using a join table with a composite primary key, index both lookup directions, and write queries handling outer joins and relational division.

Problem Statement

A digital library platform needs to organize its catalog of documents using a flexible tagging system. A document can have multiple tags, and a tag can be associated with multiple documents.

Your task is twofold: define the relational schema with proper keys, constraints, and indexes, and then write two SQL queries based on this schema.

Part 1: Schema Design (DDL)

Define three tables: documents, tags, and document_tags.

Requirements:

  • documents: contains id (integer primary key), title (text, non-null), and created_at (timestamp, non-null).
  • tags: contains id (integer primary key), name (unique text, non-null).
  • document_tags: represents the association table.
    • Must use a composite primary key (document_id, tag_id).
    • Enforce foreign key constraints referencing documents(id) and tags(id) with cascading deletes.
    • Ensure efficient lookups in both directions: finding all tags for a document, and finding all documents for a tag.

Part 2: SQL Queries

Write queries for the following scenarios:

  1. Document Tag Counts (Query A): Return document_id, title, and tag_count for all documents in the system. Documents with no assigned tags must appear with a count of 0. Order the results by tag_count descending, then by title ascending.
  2. Multi-tag Search (Query B): Find the id and title of all documents that are tagged with both 'audiobook' and 'bestseller'.

Example Schema Output

-- documents: (1, 'Sapiens'), (2, 'Steve Jobs'), (3, 'Clean Code')
-- tags: (10, 'audiobook'), (20, 'bestseller'), (30, 'tech')
-- document_tags: (1, 10), (1, 20), (2, 10), (3, 30)

  • Query A Output:

    • (1, 'Sapiens', 2)
    • (2, 'Steve Jobs', 1)
    • (3, 'Clean Code', 1)

  • Query B Output:

    • (1, 'Sapiens') (only document with both 'audiobook' and 'bestseller')

Hints

Hint 1

A composite primary key on (document_id, tag_id) automatically creates an index on (document_id, tag_id), but queries filtering by tag_id will require a separate secondary index.

Hint 2

For Query A, use a LEFT JOIN from documents to document_tags and count the non-null tag references using COUNT(document_tags.tag_id) rather than COUNT(*).

Hint 3

For Query B, filter tags to the target list, group by document, and check that the count of distinct matching tags equals the size of the required tag list.

Follow-up questions

  • How does composite primary key column ordering `(document_id, tag_id)` vs `(tag_id, document_id)` impact storage and B-tree index reuse in PostgreSQL or MySQL?
  • If users need to query documents matching 5 or more required tags simultaneously, how would the relational division query perform compared to an array/JSONB approach or a full-text search engine?
  • How would you adjust the schema and queries if tags can be soft-deleted without deleting the underlying documents?