How do you implement a many-to-many relationship in a database?

Instruction: Describe the process of modeling a many-to-many relationship between two entities in a relational database.

Context: This question tests the candidate's understanding of relational database design, particularly in handling complex relationships between data entities.

Updated

Official answer

Use a junction table whose rows represent associations. If each product-category association may occur only once, the pair can be its single composite primary key:

CREATE TABLE practice_products (product_id integer PRIMARY KEY);
CREATE TABLE practice_categories (category_id integer PRIMARY KEY);
CREATE TABLE practice_product_categories (
  product_id integer REFERENCES practice_products(product_id),
  category_id integer REFERENCES practice_categories(category_id),
  PRIMARY KEY (product_id, category_id)
);

A product may appear in several categories, and a category may contain several products. The composite primary key prevents a duplicate pair and makes both components non-null. There are two foreign keys, not two primary keys.

Association-specific attributes belong on the junction row. If the same pair can recur over separate time periods, define that new grain and its key explicitly; do not silently discard history as a duplicate. Add a reverse lookup index only when the actual workload warrants it.

Source

PostgreSQL 16: composite keys and relationships.

Related Questions