In a relational database, what does a column represent?

A column is a named attribute with a specific data type; each row provides a value for that column.

Which of these correctly describes a row in a database table?

A row is a single record with one value (or NULL) for each column in the table.

What is the minimum information a column definition requires?

A column must have a name and data type; constraints and defaults are optional additions.

You create a table with columns: id (INT), name (TEXT), and age (INT). What is this structure called?

The entire structure defining table columns, their types, and constraints is the schema.

Which data type should you use for email addresses that must fit in a database column?

TEXT stores variable-length strings like emails; INT, BOOLEAN, and TIMESTAMP are for different data.

What is the primary advantage of using TEXT over VARCHAR(255) for storing user descriptions?

TEXT has no fixed upper limit, so you don't need to predict maximum length in advance.

A UUID looks like: 550e8400-e29b-41d4-a716-446655440000. How many bits is it?

A UUID is 128 bits, displayed as 32 hexadecimal characters plus 4 dashes.

When storing monetary amounts (e.g., salary), why should you use BIGINT for cents instead of DECIMAL?

Integers avoid floating-point precision loss; storing cents prevents rounding errors in financial calculations.

Why is TIMESTAMPTZ preferred over plain TIMESTAMP?

TIMESTAMPTZ anchors times to UTC and converts automatically; plain TIMESTAMP ignores time zones.

A column is defined as: is_active BOOLEAN DEFAULT true. What does this mean?

DEFAULT true means: if you omit the value during insert, it automatically becomes true.

What is the primary purpose of a primary key?

A primary key uniquely identifies each row, ensuring no duplicates exist.

Which is a disadvantage of auto-incrementing integer primary keys?

Sequential integers are predictable; an attacker can enumerate resources.

You are building a microservices system where different servers create records that will eventually be merged. Which primary key type is safer?

UUIDs are globally unique; integers from different servers collide when merged.

What advantage does UUID v7 provide over UUID v4?

UUID v7 is time-ordered, reducing B-tree fragmentation; v4 is random.

Can you generate a UUID on the client side before inserting into a database?

UUIDs are globally unique by design; you can generate them client-side.

What does the NOT NULL constraint do?

NOT NULL rejects any NULL value; the database enforces this rule automatically.

A column is defined: status TEXT NOT NULL DEFAULT 'pending'. If you insert without specifying status, what happens?

DEFAULT 'pending' provides a fallback; NOT NULL is satisfied by the default value.

What does the UNIQUE constraint prevent?

UNIQUE prevents duplicate values but allows multiple NULLs (in most databases).

You create a constraint: UNIQUE (user_id, job_id). What does this enforce?

Composite UNIQUE prevents duplicate combinations; individual columns can repeat.

What does ON DELETE CASCADE do in a foreign key?

ON DELETE CASCADE deletes child rows when parent is deleted, maintaining referential integrity.

Why should constraints be enforced at the database level rather than only in application code?

Database constraints protect data even if application code has bugs or is bypassed.

In a notification system schema, what should the status column default to?

New notifications should be pending (unprocessed); workers will update to sent or failed.

What does a nullable sent_at timestamp tell you in a notification record?

sent_at is NULL until the notification is actually delivered; then set to delivery time.

Why use a UNIQUE(user_id, type) constraint on notification_preferences?

This prevents duplicate preference rows; a user has exactly one setting per channel.

In a notification system, what does a NULL scheduled_at mean?

NULL scheduled_at indicates no future scheduling; send the notification now.

What does the application status 'wishlist' represent?

Wishlist is the initial state; user hasn't applied yet, just bookmarked the job.

Why store salary_min and salary_max as separate INT columns (in cents)?

Integers avoid rounding errors; storing cents enables exact comparisons and sorting.

What is the relationship between job_applications and application_notes?

Each job application has a foreign key to one user; notes table has FK to applications (one-to-many).

Why use ON DELETE CASCADE for the user_id foreign key in job_applications?

ON DELETE CASCADE ensures orphaned applications are removed when user account deleted.

What is the purpose of the problem_tags junction table?

Junction table allows each problem to have multiple tags and each tag to apply to multiple problems.

Why does the problem_tags table have PRIMARY KEY(problem_id, tag_id)?

Composite PK ensures you cannot accidentally link the same problem to the same tag twice.

What does is_correct BOOLEAN in the attempts table track?

is_correct indicates if the user's submitted code passed all tests for that problem.

In the saved_problems table, why use PRIMARY KEY(user_id, problem_id)?

Composite PK ensures a user can save each problem at most once.

What data does the attempts table capture that the problems table does not?

Attempts record user behavior (code, result, duration); problems define the challenge.

If you want to find all problems tagged with 'arrays', what tables must you query?

You need tags to find 'arrays', problem_tags to map to problems, and problems for full data.

Schema Design Fundamentals — System Design Concept

🍪 TechCookies

Free

📚Schema Design FundamentalsFree

10 sections

~29 min total

35 quick quizzes

3 SD challenges linked

0 of 10 done·~27 min left

Concepts›Schema Design Fundamentals›What is Schema Design Fundamentals?

0 / 10

10 sections~29 min

What is Schema Design Fundamentals?

Design database schemas using tables, columns, data types, and constraints effectively

Read

~3 min

⋯

Tables and Columns — The Building Blocks

Tables organize data into named columns and rows, forming the foundation of relational databases

ReadQuizCode

~3 min

⋯

Data Types — What Kind of Data Goes in Each Column

Choose appropriate data types (UUID, TEXT, INT, TIMESTAMPTZ, BOOLEAN) to store data efficiently and safely

ReadQuizCode

~3 min

⋯

Primary Keys — UUID vs Integer

Primary keys uniquely identify rows; choose between auto-incrementing integers and globally unique UUIDs

ReadQuizCode

~3 min

⋯

Constraints — Enforcing Rules at the Database Level

Constraints like NOT NULL, DEFAULT, and UNIQUE enforce data quality rules automatically

ReadQuizCode

~3 min

⋯

System Design: Notification System

Schema for sending and tracking notifications across email, push, and SMS channels

ReadQuizCode

~3 min

⋯

System Design: Job Application Tracker

Schema for tracking job applications with statuses, deadlines, and notes

ReadQuizCode

~3 min

⋯

System Design: Interview Prep Platform

Schema for storing coding problems, user attempts, tags, and saved favorites

ReadQuizCode

~3 min

⋯

Constraints Summary Table

Quick reference table comparing NOT NULL, DEFAULT, UNIQUE, and other constraint types

Read

~3 min

⋯

Practice test

35 questions

~12 min

Section 1 of 10Read

What is Schema Design Fundamentals?

Design database schemas using tables, columns, data types, and constraints effectively

~3 min read

Schema design is the foundation of every database-driven application. Before you write a single line of application code, you need to decide how your data will be structured, what types of values each field will hold, and what rules will keep that data clean and consistent. Think of a schema like the blueprint of a building — get it right early and everything built on top becomes easier; get it wrong and you'll be fixing structural problems forever. These notes will walk you through tables, columns, data types, constraints, and key design decisions in a beginner-friendly way, complete with real-world examples from systems you might actually build.

Mark this section complete.

Notes

🔍

Loading…