Databases and SQL

Databases and SQL: Relational Design, Queries, and Common Engines

A relational database organizes data into tables with rows and columns, enforces relationships between tables through foreign keys, and provides transactions to keep data consistent. SQL (Structured Query Language) is the standard language for querying and manipulating relational data. Understanding data modeling, the core SQL statements, and indexing is the foundation for working with any relational database engine.

Relational data modeling

A relational database stores data in tables. Each table has columns (attributes) and rows (records). A primary key uniquely identifies each row within a table. A foreign key in one table references the primary key of another, creating a relationship. This structure avoids duplicating data: instead of storing a customer's full address in every order row, you store the customer ID and join to the customer table when you need the address.

Normalization is the process of organizing tables to reduce redundancy and improve integrity. First Normal Form (1NF) requires each column to hold atomic values and each row to be unique. Second Normal Form (2NF) requires all non-key attributes to depend on the whole primary key. Third Normal Form (3NF) requires non-key attributes to depend only on the primary key, not on other non-key attributes. In practice, most schemas aim for 3NF and selectively denormalize for performance when necessary.

Core SQL statements: SELECT, INSERT, UPDATE, DELETE

SELECT retrieves data from one or more tables. The basic form is SELECT columns FROM table WHERE condition. JOIN combines rows from two tables based on a related column: INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table and matching rows from the right (with NULLs where there is no match). GROUP BY aggregates rows sharing a value; HAVING filters grouped results. ORDER BY sorts the output; LIMIT restricts the number of rows returned.

INSERT adds new rows: INSERT INTO table (col1, col2) VALUES (val1, val2). UPDATE modifies existing rows: UPDATE table SET col1 = val1 WHERE condition. DELETE removes rows: DELETE FROM table WHERE condition. Omitting the WHERE clause on UPDATE or DELETE affects every row in the table, which is a common and painful mistake. Most databases support transactions: BEGIN wraps a set of statements; COMMIT saves them; ROLLBACK undoes them if something goes wrong before the commit.

Indexes, performance, and EXPLAIN

An index is a data structure that allows the database to find rows matching a condition without scanning every row in the table. Without an index on the column in a WHERE clause, the database performs a full table scan. With an index, it can jump directly to the matching rows. Indexes speed up reads but slow down writes because the index must be updated when rows are inserted, updated, or deleted. The primary key is always indexed; other columns should be indexed based on the queries you run most often.

The EXPLAIN statement (or EXPLAIN ANALYZE in PostgreSQL) shows how the database plans to execute a query, including whether it uses indexes and in what order it joins tables. Learning to read EXPLAIN output is the primary tool for diagnosing slow queries. Common problems revealed by EXPLAIN are full table scans on large tables (missing index), inefficient join order, and unnecessary sorts. Adding the right index, or rewriting the query, often reduces execution time by orders of magnitude.

Common database engines

PostgreSQL is a full-featured open-source relational database known for standards compliance, advanced data types (JSON, arrays, geospatial), and robust support for complex queries. It is widely used in production web applications and is the recommended choice for new projects where performance, correctness, and advanced features matter. MySQL and its fork MariaDB are also widely used, particularly in web stacks (the M in LAMP). MySQL has historically been faster for simple read-heavy workloads but has fewer advanced features than PostgreSQL.

SQLite is a file-based database engine with no separate server process; the database is a single file on disk. It is ideal for embedded applications, mobile apps, desktop software, and development environments where a full server is unnecessary. It supports most standard SQL but lacks some features (such as ALTER TABLE column modification and certain join types). Microsoft SQL Server is the dominant enterprise relational database on Windows-centric stacks, with deep integration into the .NET and Azure ecosystem.

Key concepts

+ Primary and foreign keys: Primary keys uniquely identify rows; foreign keys link rows in one table to rows in another.
+ Normalization: Organizing tables to reduce redundancy; 3NF is the practical target for most schemas.
+ Joins: INNER JOIN returns matched rows from both tables; LEFT JOIN returns all left rows including unmatched ones.
+ Indexes: Speed up reads significantly; the cost is slower writes and additional storage overhead.
+ Transactions: BEGIN/COMMIT/ROLLBACK ensure that a set of changes either all succeed or all fail together.
+ EXPLAIN: Shows the query execution plan; essential for diagnosing and fixing slow queries.

Frequently asked questions

What is the difference between a SQL and a NoSQL database?

SQL databases are relational: structured tables, fixed schemas, and SQL for queries. They enforce referential integrity and support transactions. NoSQL databases use different data models: document stores (MongoDB, Couchbase), key-value stores (Redis, DynamoDB), wide-column stores (Cassandra, HBase), and graph databases (Neo4j). NoSQL databases often trade strict consistency for horizontal scalability and schema flexibility. The choice depends on the data model and access patterns of your application, not on which is generically 'better'.

What is an ORM and should I use one?

An Object-Relational Mapper (ORM) is a library that maps database rows to objects in your programming language, letting you query and manipulate data without writing SQL directly. Examples include SQLAlchemy (Python), Hibernate (Java), and ActiveRecord (Ruby on Rails). ORMs speed up development and prevent some SQL injection vulnerabilities. They can also generate inefficient queries; for complex reporting or high-performance applications, writing SQL directly (or using an ORM's escape hatch for raw queries) is often necessary.

What is a database transaction and why does it matter?

A transaction is a sequence of SQL statements that executes as a single atomic unit. Either all statements commit successfully or none of them do. Transactions maintain data consistency: if you are transferring money between accounts, you need the debit and the credit to both succeed or both fail. The ACID properties (Atomicity, Consistency, Isolation, Durability) define what a correct database transaction guarantees.

What is SQL injection and how do you prevent it?

SQL injection is an attack where an attacker inserts SQL code into an input field that is then concatenated into a query, allowing them to read, modify, or delete data. Prevention requires using parameterized queries (also called prepared statements), which separate the SQL structure from the user-supplied data so the data cannot be interpreted as SQL. Never build queries by concatenating user input directly into a SQL string.