All 15 Databases questions
More in Databases
What is database normalization, and what specific problems can over-normalization or under-normalization cause? What is database migration, and why is it important to manage schema changes through versioned migration files rather than manual changes? What is the N+1 query problem, and how does it commonly arise in a Node.js application using an ORM? What is a database seed script, and how does it differ from a migration? What is a database transaction, and how would you implement one in Node.js to ensure multiple related writes either all succeed or all fail together? What is database indexing, and how does an index improve query performance in a Node.js application's database layer? What is database replication, and how does a Node.js application typically route reads to replicas while sending writes to a primary? What is optimistic concurrency control, and how would you implement it to prevent lost updates when two users edit the same record simultaneously? What is database sharding, and what problem does it solve that replication alone doesn't address? What is a database connection leak in a Node.js application, and how does it typically happen when using a connection pool?
Advertisement
Back to results for “Databases & ORMs (MongoDB/Mongoose”
Node.js › Databases

What is the difference between a one-to-many and a many-to-many relationship in a relational database, and how is each modeled?

Intermediate

Answer

A one-to-many relationship (like one author having many books) is modeled with a foreign key on the 'many' side's table pointing back to the 'one' side. A many-to-many relationship (like many students enrolled in many courses) requires an intermediate junction table containing foreign keys to both related tables, since neither table alone can hold a foreign key representing multiple associations on both sides.
-- One-to-many: books.author_id references authors.id
CREATE TABLE books (id SERIAL PRIMARY KEY, author_id INT REFERENCES authors(id));

-- Many-to-many: a junction table
CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);
Real-world example A course-registration system models the many-to-many relationship between students and courses using an 'enrollments' junction table, which also conveniently stores relationship-specific data like the enrollment date and grade that wouldn't fit naturally on either the students or courses table alone.

Common follow-ups: What additional data can a junction table hold beyond just the two foreign keys, and why is that useful?;How does an ORM like Sequelize or a Mongoose implementation model a many-to-many relationship in code?

SQL Queries;Databases & ORMs (MongoDB/Mongoose SQL/Sequelize)
All 15 Databases questions