Explain difference between 1:N and M:N relationships

One-to-Many (1:N): One parent, many children

Example: One customer places many orders

Implementation: FK in "many" side (orders.customer_id)

Many-to-Many (M:N): Both sides have multiple relationships

Example: Students enroll in many courses, courses have many students

Implementation: Junction table with two FKs (enrollments table)

Key difference: 1:N uses single FK, M:N requires junction table.

How would you design schema for social media platform?

USERS (user_id PK, username, email)

POSTS (post_id PK, user_id FK, content, timestamp)

COMMENTS (comment_id PK, post_id FK, user_id FK, content)

FRIENDSHIPS (friendship_id PK, user_id_1 FK, user_id_2 FK, status)

LIKES (like_id PK, post_id FK, user_id FK)

Relationships:

• Users → Posts (1:N)

• Posts → Comments (1:N)

• Users ↔ Friends (M:N via FRIENDSHIPS)

• Users ↔ Liked Posts (M:N via LIKES)

What is difference between CASCADE and RESTRICT?

ON DELETE CASCADE: Auto-delete children when parent deleted

Example: Delete customer → auto-delete all orders

Use when: Children meaningless without parent

ON DELETE RESTRICT: Prevent deletion if children exist

Example: Cannot delete customer with existing orders

Use when: Want explicit control, prevent accidental data loss

ON DELETE SET NULL: Delete parent → set FK to NULL

Example: Delete customer → orders.customer_id = NULL

Use when: Want to keep historical data

How do you implement one-to-one relationship?

One-to-One (1:1): One record relates to exactly one record

Example: User ↔ UserProfile

Implementation:

USERS (user_id PK, username, email)

USER_PROFILES (profile_id PK, user_id FK UNIQUE, bio, avatar)

Key: UNIQUE constraint on FK ensures 1:1

Alternative: Merge into single table if always accessed together

USERS (user_id PK, username, email, bio, avatar)