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

Design the relational tables for a many-to-many relationship, including a join table with a composite primary key, and then write queries against the schema. The interview asked for the schema of a many-to-many relationship with an explicit join table and composite primary key, followed by queries. The source does not name the two entities or the exact query. For practice, use **documents** and **tags**: a document can carry many tags, and a tag can be attached to many documents. The queries in Part 2 are practice stand-ins for the unreported query, and they exercise the same schema. ```hint Where the relationship lives Neither entity table can hold the relationship without repeating data. Think about what one row of a third table represents and which columns identify that row. ``` ### Constraints and Clarifications - Use PostgreSQL syntax. - A document has an ID and a title; a tag has an ID and a name. Tag names are unique. - Attaching the same tag to the same document twice must be impossible at the database level, not just in application code. - Queries must be read-only `SELECT` statements. ### Clarifying Questions - Does the relationship itself carry data, such as when a tag was attached or who attached it? - When a document or a tag is deleted, should its associations disappear automatically, or should the deletion be blocked? - Are tag names compared case-sensitively? - Which access direction is more frequent: tags for a document, or documents for a tag? ### Part 1 — Write the Schema Write the `CREATE TABLE` statements for the two entity tables and the join table. Explain your choice of primary key for the join table, the foreign keys and their delete behavior, and any additional index you would add. ```hint Column order matters A composite key is also an index. Consider which lookups its leading column serves and which lookups it does not. ``` #### What This Part Should Cover - A join table whose composite primary key prevents duplicate associations. - Foreign keys with a deliberate delete behavior. - Index coverage for both access directions. ### Part 2 — Write the Queries Using your schema, write: 1. The tag names attached to one given document, sorted alphabetically. 2. Every tag with the number of documents that carry it, including tags that are attached to no documents. Sort by count descending, then by tag name ascending. 3. The documents that carry **both** of two given tags. ```hint Counting with an outer join When a tag has no matching rows, think about which expression inside `COUNT` still returns zero instead of one. ``` #### What This Part Should Cover - Correct join paths through the join table. - Correct handling of zero-match rows in an aggregation. - A set-based way to require multiple related rows. ### What a Strong Answer Covers - A normalized three-table design in which the database enforces uniqueness and referential integrity. - A justified key and index layout for the workload's access patterns. - Queries that return correct results for edge cases such as untagged entities and duplicate-free counts. - Awareness of alternatives, such as a surrogate key on the join table or storing tags in an array column, along with their trade-offs. ### Follow-up Questions 1. The product now records who attached each tag and when. What changes in the schema, and does the primary key change? 2. The documents-per-tag count is displayed on every page load for millions of associations. How would you keep it fast? 3. How would you allow the same tag to be attached twice to one document with different attributes, and what would that do to the key?