Schema Design

How Do You Design Document Schemas in MongoDB?

MongoDB document schema design means choosing, for each relationship in your data, whether to embed related data inside a single document or reference it from…

How Do You Choose Numeric Data Type Precision in a Database?

Choosing numeric precision means matching a column’s storage size and rounding behavior to the exact range and accuracy the data needs — using fixed-point type…

DECIMAL vs FLOAT: How Do They Store Numbers Differently?

DECIMAL stores numbers as an exact sequence of base-10 digits with a fixed precision and scale, while FLOAT stores numbers as an approximate base-2 (binary) re…

How Should Databases Handle Dates, Times, and Time Zones?

The safest approach is to store every timestamp in UTC using a timezone-aware type, and convert to a user's local time zone only at the presentation layer, so…

When Should You Use a JSON Column Type in a Relational Database?

A JSON column makes sense for semi-structured, sparse, or frequently changing attributes that do not fit cleanly into fixed columns — such as user preferences,…

ENUM Type vs Lookup Table: Which Should You Use?

An ENUM type is best for a small, genuinely fixed set of values that almost never changes and needs compact, fast storage, while a lookup table (a separate ref…

What Is a CHECK Constraint and When Would You Use One?

A CHECK constraint is a rule attached to a column or table that the database evaluates on every insert or update, rejecting any row whose values do not satisfy…

What Is the Difference Between a UNIQUE Constraint and a UNIQUE Index?

A UNIQUE constraint is a schema-level rule declaring that a column or set of columns must contain no duplicate values, while a UNIQUE index is the physical dat…

How Should You Decide Which Columns Get a NOT NULL Constraint?

A NOT NULL constraint should be applied to any column whose absence would make a row meaningless or would break downstream logic, such as identifiers, foreign…

How Does a DEFAULT Value Constraint Behave in SQL?

A DEFAULT constraint supplies a predetermined value for a column automatically whenever an INSERT statement omits that column entirely, but it never overrides…

What Do ON DELETE CASCADE, SET NULL, and RESTRICT Mean for Foreign Keys?

These are referential actions attached to a foreign key that tell the database what to do to child rows when the referenced parent row is deleted or updated: C…

What Are 1NF, 2NF, and 3NF in Schema Design?

1NF, 2NF, and 3NF are progressive normalization rules that remove repeating groups, partial key dependencies, and transitive dependencies from a table, each le…

What is Boyce-Codd Normal Form (BCNF)?

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF requiring that for every non-trivial functional dependency X to Y in a table, X must be a superkey —…

What Are the Basics of Entity-Relationship (ER) Modeling?

Entity-relationship modeling represents a database's structure using entities (things like Customer or Order), attributes (properties of those entities), and r…

OLAP vs OLTP: What is the Difference?

OLTP (Online Transaction Processing) is optimized for many short, concurrent read/write transactions on individual rows, while OLAP (Online Analytical Processi…

Schema-per-Tenant vs Shared-Schema: Which to Choose?

Schema-per-tenant gives each tenant their own set of tables (a namespace) inside one shared database instance, while shared-schema puts every tenant’s rows int…

What is a "God Table" Anti-Pattern and Why is It a Problem?

A god table is a database anti-pattern where a single, overly wide table absorbs responsibility for many unrelated entities or concerns — often via dozens of n…

How Do You Design a Good GraphQL Schema?

A good GraphQL schema models the domain as a graph of types connected by meaningful relationships, favors nullable fields and explicit input types over overloa…