Free preview
10 minER Diagrams and Relationships
Model your domain with Entity-Relationship diagrams before writing a single line of code.
An ER diagram is a blueprint for your database. Design it before writing code to catch problems early:
Entities (tables): Relationships:
User User ──< Order (one-to-many)
Order Order ──< OrderItem (one-to-many)
Product OrderItem >── Product (many-to-one)
Category Product >──< Category (many-to-many)Cardinality notation:
──<= one-to-many (one User has many Orders)>──<= many-to-many (Products have many Categories)──= one-to-one
Multiple choice
An e-commerce site: one User can have many Orders, each Order has many Products. What is the relationship between User and Product?
- AMany-to-many (through Orders and OrderItems)
- BOne-to-many (User directly owns Products)
- COne-to-one
- DNo relationship
Three types of relationships and their table implementation:
-- One-to-One: User has one Profile
CREATE TABLE profiles (
user_id INTEGER PRIMARY KEY REFERENCES users(id)
);
-- One-to-Many: User has many Posts
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) -- FK in the 'many' table
);
-- Many-to-Many: Post has many Tags
CREATE TABLE post_tags (
post_id INTEGER REFERENCES posts(id),
tag_id INTEGER REFERENCES tags(id),
PRIMARY KEY (post_id, tag_id) -- composite PK prevents duplicates
);Multiple choice
In a one-to-many relationship (User has many Posts), where does the foreign key go?
- AIn the 'many' table — posts.user_id references users.id
- BIn the 'one' table — users.post_id references posts.id
- CIn a separate junction table
- DBoth tables need a foreign key
Fill in the blank
A many-to-many relationship requires a ___ table (also called junction, join, or bridge table).
Predict the output
A students table and courses table need a many-to-many relationship. Which table structure is correct?
- Astudent_courses (student_id, course_id, PRIMARY KEY (student_id, course_id))
- Bstudents (id, course_ids[])
- Ccourses (id, student_ids[])
- DAdd student_id to courses table