Database Design

What is Normalization in Database?

Normalization is the process of organizing data in a database to reduce redundancy, avoid anomalies, and improve data integrity.

Difference Between SQL and NoSQL Databases

SQL databases are relational, store data in structured tables with a fixed schema, and use SQL for querying; NoSQL databases are non-relational, store data in…

Difference Between Primary Key and Foreign Key

A primary key uniquely identifies each row in a table and cannot be NULL, while a foreign key is a column that references the primary key of another table to l…

What is a Deadlock in Database?

A deadlock in a database happens when two or more transactions each hold a lock the other needs, so every transaction involved waits forever and none can proce…

What is Database Partitioning?

Database partitioning is splitting a large table into smaller, more manageable pieces called partitions, while the table is still queried as one logical unit.

Optimistic vs Pessimistic Locking: What is the Difference?

Pessimistic locking locks a row before reading it so no one else can change it, while optimistic locking allows concurrent reads and only checks for conflicts,…

What is a Trigger in SQL?

A trigger in SQL is a stored piece of code that automatically executes in response to a specific event, such as an INSERT, UPDATE, or DELETE, on a table.

What is Denormalization in Database?

Denormalization is the deliberate process of combining data from multiple related tables back into fewer, wider tables to reduce joins and speed up read querie…

What is a Composite Key?

A composite key is a primary key made up of two or more columns that, together, uniquely identify a row, even though no single column among them is unique on i…

CHAR vs VARCHAR

CHAR is a fixed-length string type that always stores and pads to its declared length, while VARCHAR is a variable-length type that stores only the actual char…

What is Referential Integrity?

Referential integrity is a database rule ensuring that a foreign key value in one table always matches an existing primary key value in the referenced table, p…

CTE vs Temporary Table: What is the Difference?

A CTE is a named result set scoped to a single statement with no storage or indexing of its own, while a temporary table is a real, physically stored table tha…

What is the Document Database Model?

A document database stores each record as a self-contained, semi-structured document, typically JSON or BSON, where related fields and nested data live togethe…

What is a Column-Family Database Model?

A column-family database groups related columns into named families and stores each row's data physically by column family rather than by row, so queries touch…

What is the Graph Database Model?

A graph database stores data as nodes (entities) and edges (relationships between them), both of which can carry properties, and it is optimized to traverse re…

What is a Key-Value Store and When Should You Use One?

A key-value store is the simplest NoSQL model: every item is an opaque value retrieved by a unique key, with no required schema and no built-in support for que…

Wide-Column Store vs Relational Database: What is the Difference?

A wide-column store lets each row hold millions of dynamically named, sparse columns grouped by family and distributed by row key across many nodes, while a re…

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 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 —…