TS-43: Relational databases and SQL

This technical standard covers general best practices for working with relational databases and writing SQL. This is not specific to any particular database engine, although some specific guidance for PostgreSQL, MySQL, and others may be given along the way.

Representation is the essence of programming.

– Fred Brooks

Sharding

This section sets out guidelines for the planning, design, and maintenance of sharded databases.

What is sharding?

In large-scale applications, as data and traffic grows, the database can become a bottleneck – in terms of either storage capacity or performance, or both.

Sharding is a database architecture pattern that helps to maintain high performance at scale. This is done by spreading both data and load across multiple databases running on multiple servers.

Database sharding is a horizontal scaling technique. Data is partitioned into "shards", each of which is responsible for a specific subset of the overall data. Each shard is stored on a separate database, running on a dedicated server or cluster. As data and traffic grow, additional shards are added with the aim of further increasing the distribution of both data and load. (This is in contrast to vertical scaling techniques, which involve increasing the capacity of a single server, or reducing the load on it by introducing caching layers between the database and application.)

Sharding is particularly beneficial in applications with very large (and indefinitely growing) datasets, and systems with high transaction volumes. It can also be useful where there is a requirement to distribute data geographically, for example for the purpose of regulatory compliance.

Types of sharding

The two main approaches to sharding are called horizontal and vertical sharding.

Horizontal sharding, also known as range-based sharding, involves partitioning data by rows. Each shard stores a range of rows, usually based on a range of values in a specific column (the "shard key").

In vertical sharding, tables are partitioned by columns, where each shard contains a subset of columns for each row.

A sharding strategy may combine elements of both horizontal and vertical sharding techniques, known as a hybrid approach.

Shard keys

For horizontal sharding, the shard key is a column, or set of columns, used to determine which shards individual records belong to.

Shard keys may be:

• Range-based: This is the simplest and most common type of shard key. It requires values that have a natural order, such as dates or timestamps, or numerical auto-incrementing IDs.
• Hash-based: Hash keys are created by applying a hash function to the values of one or more columns.
• Composite: Shard keys are composed from the values of multiple columns.

Shard routing

When an application needs to query a sharded database, it must first determine which shard to connect to. In horizontal sharding, the lookup is determined by the shard key. In vertical sharding, the lookup is based on the data types being queried.

The process of resolving a query to a shard is called shard routing. Implementation strategies include:

• Application-level routing: The logic for determining which shard to query is built directly into the application code.
• Middleware and proxy-based routing: An intermediary layer between the application and database is used to handle the shard routing.
• Database-level routing: The database itself handles the routing of queries to the appropriate shards. This approach is common in database management systems that natively support sharding.

Designing a sharding strategy

Sharding strategies should be designed with consideration of the data types and access patterns of the application. Consideration also needs to be give to how best to manage data consistency, how to achieve efficient and correct query routing, and how to maintain balanced shards (ie. have an even distribution of data and load across shards).

Horizontal sharding has the greatest scalability potential. You can just keep adding more and more shards as the data grows. There is also greater scope for automating the creation of new shards, as data reaches predefined thresholds.

By contrast, the scope for vertical sharding is limited to the data types of the system, and the shards tend to be static. Vertical sharding is most beneficial in systems where data can be grouped in a way that queries rarely (or never) require joins between groups. Since queries to discrete data groups will get routed to specific infrastructure, you have the opportunity to optimize individual shards for the unique access patterns of their data. Some shards may be optimized for read-heavy queries, others for write-heavy queries, for example.

Horizontal and vertical sharding may be combined into a hybrid approach, attempting to leverage the benefits of both strategies. For example, lesser-accessed data may be separated out (vertically sharded), leaving the most-accessed data for horizontal sharding.

In horizontal sharding, the choice of shard key is critical. The objective is to choose a key that will result in a relatively even distribution of both data and load across all shards. The objective is to have "well-balanced" shards.

Range-based shard keys are often the simplest to implement, but be careful about the potential for data skew (where some shards store much more data than others) and hotspots (where some shards are accessed much more frequently than others). For example, is you shard by an auto-incrementing ID, and access patterns are biased towards the most recent data, then both read and write operations will be concentrated on the most recent shards.

Data access should be relatively uniform across all shards, so that load is evenly distributed across the infrastructure. Shard keys that are based on hashes of the data values tend to result in more evenly distributed data and load than range-based keys.

Shard keys should be chosen with the most common queries in mind. For example, if most queries filter data based on a specific column, such as a user ID, that column will be a strong candidate for the shard key. It will mean that the shard key will be known to the application, and so queries can be routed directly to the correct shard without requiring any intermediary lookups.

Composite keys – made up of the values of multiple columns – add the most complexity but they can also enable more granular control over data distribution, and queries can be optimized for multiple access patterns. But these keys often require custom routing logic, adding latency to query execution.

In vertical sharding, data partitions should align with queries, such that cross-shard joins and transactions are kept to a minimum. Cross-shard operations will negate some of the benefits of sharding, notably performance.

Sharding strategies should plan for data growth and increased traffic in the future. Consider how will new shards be added over time, and how the shard creation process could be automated. Shard keys that naturally support range-based distribution are often good candidates for auto-scaling.

Consider also the accuracy and efficiency of your routing logic. Shard routing should be quick and reliable. Application-level routing allows for customized routing logic, making it easier to optimize for specific use cases. If required, routing logic can be adjusted dynamically based on real-time data. Middleware and proxies can be used to centralize routing logic, extracting this accidental complexity from the application, leaving the application to specialize in the essential complexity of its domain – a neater separation of concerns. Proxies may also be shared by multiple applications that access the same data, and failover mechanisms can be built-in to the proxies, too. But this design introduces latency and the database proxy becomes a new single point of failure – more things to be considered.

Databases with built-in sharding capabilities, such as MongoDB, allow for easy sharding configuration out-of-the-box. The trade-offs with this approach include limited customization and commercial risks associated with greater vendor lock-in.

Think also about the monitoring of shards, and how fallbacks and error handling will work in the event of shard failures. How will the sharding strategy impact your backup and disaster recovery procedures?

Finally, if sharding is combined with replication and denormalization techniques, you will need to plan for how eventual data consistency will be achieved across shards. See Transactions and consistency for the broader treatment of consistency models, isolation levels, and the ACID/BASE trade-off that this eventual-consistency planning sits within.

In summary, designing a sharding strategy requires careful consideration of an application’s data types and distribution, query patterns, and performance and scalability needs. All sharding strategies involve compromise. All designs will add some level of accidental complexity to a system, and each solution will have its own particular considerations and trade-offs.

SQL style and formatting

This section sets out formatting conventions for SQL statements, so that queries are consistent and easy to scan across a codebase.

Keep queries succinct

Write SQL that is free of redundant code. Do not add quoting, parentheses, or WHERE clauses that can be derived or omitted without changing the meaning of the query. Every clause should earn its place in the query.

Indentation and the "river"

Use whitespace and indentation judiciously so that a query’s structure is visible at a glance. Right-align the root keywords (SELECT, FROM, WHERE, GROUP BY, ORDER BY, and so on) so that they form a consistent vertical "river" down the middle of the statement, with implementation details — column names, table names, conditions — left-aligned to the right of that river.

  SELECT first_name, last_name, email
    FROM customers
   WHERE status = 'active'
     AND region = 'EMEA'
ORDER BY last_name;

This layout makes the query’s clauses easy to scan down the left edge, while keeping the details of each clause together on the right.

Spacing

• Use a single space before and after the = operator, and after every comma.
• Do not add a space immediately inside parentheses.
• Do not add a space before a trailing comma or semicolon.
• Surround string literals with a single space where they sit between other tokens, but not where they are immediately followed by a closing parenthesis, a trailing comma, or a semicolon.

Line spacing

• Start a new line before AND and OR in a multi-condition WHERE clause.
• Start a new line after each semicolon that terminates a statement.
• Start a new line after each root keyword’s own clause definition (SELECT, FROM, WHERE, and so on).
• When grouping columns in a SELECT list or a GROUP BY clause, start a new line after each comma once the list is long enough to need it.
• Use blank lines to separate a query into related logical sections, the same way blank lines separate paragraphs in prose.

Formatting joins

Indent JOIN clauses to the opposite side of the river from the root keywords, and group each join with a new line, so the joined tables are visually distinct from the columns being selected.

  SELECT o.id, o.total, c.email
    FROM orders AS o
    JOIN customers AS c
      ON o.customer_id = c.id
   WHERE o.status = 'completed';

Formatting subqueries

Align a subquery to the right side of the river, and lay it out internally as any other query — with its own river of root keywords. Place the subquery’s closing parenthesis on its own line, aligned with the line that opened it. This is especially important for nested subqueries, where misaligned parentheses make the nesting difficult to follow.

  SELECT id, email
    FROM customers
   WHERE id IN (
                 SELECT customer_id
                   FROM orders
                  WHERE total > 100
               );

Reserved keywords

• Always write reserved keywords in upper case (SELECT, WHERE, INNER JOIN), so they are visually distinct from identifiers.
• Prefer the full-length form of a keyword over an abbreviation where the dialect offers both (for example, ABSOLUTE rather than ABS where both exist and mean the same thing).
• Avoid vendor-specific keywords where an ANSI SQL keyword performs the same function. This keeps queries portable across database engines.

Reserved keywords vary across dialects and versions. Rather than reproduce a comprehensive keyword list here — which would drift out of date against newer database versions — consult your database engine’s own reserved-word reference before choosing an identifier, and prefer identifiers that avoid the ANSI SQL reserved set entirely, since that set is honored by every mainstream engine.

Comments

Include comments where they add value — where the intent of a query is not obvious from the SQL alone. Prefer C-style comments (/* …​ */) for multi-line or block commentary. Where a single-line comment is more appropriate, use -- followed by a space, and terminate the comment with a newline rather than continuing it onto the same line as code.

Naming conventions

This section sets out conventions for naming database objects — tables, columns, aliases, and stored procedures — so that a schema is predictable and self-documenting to read.

Case and character rules

Use snake_case as the default casing convention for all identifiers — tables, columns, aliases, and stored procedures — unless the system you are working with already has an established, different prevailing convention, in which case follow that convention consistently. Avoid CamelCase; it is harder to scan in SQL, where keywords are conventionally upper case and identifiers are conventionally lower case, so a mixed-case identifier loses that visual distinction.

Identifier names MUST:

• Begin with a letter, never a digit or an underscore.
• Never end with a trailing underscore.
• Use only letters, numbers, and underscores — no spaces or punctuation.
• Delimit words with a single underscore, and avoid consecutive underscores.

Choosing identifiers

• Choose identifiers that are consistent and descriptive, so that a reader unfamiliar with the schema can infer an object’s purpose from its name alone.
• Keep identifiers under 30 characters where practical, for compatibility with database engines that impose shorter identifier length limits.
• Avoid abbreviations, except where the abbreviation is commonly understood within the domain (id, url, html).
• Avoid reserved keywords as identifiers — see "Reserved keywords" in SQL style and formatting.
• Avoid quoting identifiers. Where quoting cannot be avoided (for example, an identifier that collides with a reserved keyword in a legacy schema), use the SQL92 double-quote form rather than a vendor-specific quoting character.
• Avoid prefixes and Hungarian notation, such as sp_ for stored procedures or tbl for tables. The object’s context in the schema already conveys its kind; a type prefix is redundant. Valid, narrow exceptions may apply — for example, a naming scheme mandated by a specific framework or ORM.

Table names

• Name a table with a collective noun (staff, people) in preference to a plural (employees, individuals) where a natural collective term exists. Where no natural collective term exists, a plural form is an acceptable, less ideal fallback.
• Never give a table the same name as one of its own columns.
• For a joining table that models a many-to-many relationship, avoid concatenating the two related table names (cars_mechanics). Prefer a name that describes the relationship itself (services), where one exists.
• Suffix a reference or lookup table’s name with _lookup (language_lookup, colour_lookup), so it is distinguishable from an entity table by name alone. A reference/lookup table holds a small, largely static set of values that other tables reference by foreign key — a status list or a set of country codes — rather than modeling a domain entity in its own right.

Column names

• Name a column in the singular, even where its table is named in the plural or as a collective noun (a row in staff has a role, not roles, unless the column genuinely holds multiple values).
• Never name a column the same as its own table.
• Use lower case for all column names, except where a value is a proper noun.
• Avoid using id alone to name a non-primary-key identifier column where a more descriptive name is available. This standard’s own primary-key naming convention — including its treatment of the id column name — is set out in Primary keys, which this section defers to, rather than duplicating or contradicting it here.

Column-suffix conventions

Adopt a controlled vocabulary of column-name suffixes with a consistent, universal meaning across the schema. A reader who understands the vocabulary can infer a column’s shape and semantics from its name alone, without checking the schema definition. Recommended suffixes:

Column-name type prefixes

Where a table has many columns and a suffix-only vocabulary is not granular enough, a column-name schema of <type>_<subject>(_<modifier>) gives a stronger contract. The type prefix declares the column’s data shape; the subject is a noun from the business domain; the optional modifier is an adjective describing a variant of the same subject.

Global type prefixes, consistent across the whole schema:

Domain-specific prefixes MAY be added where a project’s domain calls for them — for example, loc for a location value, or addr for an address. Whatever prefixes a project adopts, every column sharing a given prefix MUST store data in the same underlying format, so the prefix’s meaning never varies by table.

The modifier suffix describes a variant of the subject — for example, _raw for an unprocessed value and _clean for its validated or normalized counterpart (dt_signup_raw vs. dt_signup_clean).

Adopting a controlled vocabulary for column names carries benefits beyond readability: it makes it easier to generate realistic fake data for testing, to write automated validation that keys off the prefix, to build safer data pipelines that treat same-prefixed columns identically, and to discover related columns across otherwise unrelated datasets.

Aliases

• An alias MUST relate clearly to the object it proxies.
• As a rule of thumb, derive a table alias from the first letter of each word in the table name (orders becomes o, order_items becomes oi).
• Where two aliases in the same query would collide, append a number to disambiguate (o1, o2).
• Always include the AS keyword when introducing an alias, for both tables and computed columns. This makes the alias visually distinct from the object it names.
• Name computed data — the result of SUM(), AVG(), and similar aggregate or expression columns — as if it were a real schema column, using the same naming conventions set out above (SUM(total) AS order_total, not SUM(total) AS sum_total).

Stored-procedure names

A stored procedure’s name MUST contain a verb, since a procedure performs an action (calculate_monthly_total, not monthly_total). Do not prefix a stored procedure’s name with sp_ or any other descriptive or Hungarian prefix — this is a legacy convention from systems where sp_ was reserved for system-defined procedures, and it adds no information in a modern schema.

Columns, keys, and schema definition

This section covers the design of table schemas: column ordering, primary keys, constraints, and the formatting of CREATE/ALTER statements.

Column ordering

Order a table’s columns consistently, so that any table in the schema reads the same way:

1. The primary key (id), first.
2. A uuid column, where the table has one.
3. A logged_at column, for tables that record log or event data.
4. Foreign-key columns, in alphabetical order.
5. All other columns, grouped logically by what they represent; alphabetical order within a group where there is no other natural grouping.
6. created_at, updated_at, and deleted_at, last, in that order.

Primary keys

Not every table needs a primary key. Do not add one by default — every column and constraint on a table must serve a purpose, and a primary key that nothing depends on is unjustified schema weight.

UUID vs auto-incrementing integer

Where a table does need a primary key, choose between a UUID and an auto-incrementing integer based on the table’s access pattern, not by default:

• UUIDs are 16 bytes, larger than an integer key, but they can be generated anywhere — including client-side, before a row is persisted — are globally unique across tables and databases, and are well suited to offline-first clients, data merging, horizontal distribution, and replication, none of which cope well with a centrally-issued sequential integer.
• Auto-incrementing integers are smaller and more convenient at small scale, and they are generally the better choice for foreign-key columns that reference the primary key, since a smaller key keeps indexes on the referencing table more compact.

Default to UUID primary keys, and mix in auto-incrementing integer keys where a specific table’s access pattern favors them — most often for foreign-key targets, per the trade-off above.

Naming the primary key column

Two conventions compete for a primary key’s column name: a bare id, or a table-qualified <table>_id (for example, user_id on the users table). Arguments exist for both — <table>_id is unambiguous in a joined query without aliasing, and searches more predictably across a codebase; a bare id is shorter, matches the default column ORMs such as ActiveRecord expect, and reads naturally as "the identifier of this row" from within the table’s own context.

This standard’s house style is the bare id, for consistency with the common ORM convention. <table>_id is a valid exception for a composite or aggregate key, where the plain id no longer identifies which part of the key a given column represents.

This is the definitive rule for the primary-key column specifically. It takes precedence over the general column-naming guidance in Naming conventions, which advises avoiding a bare id for non-primary-key identifier columns — the two are not in tension: id is reserved for the primary key alone, and every other identifier column should be more specific (author_id, parent_category_id).

Modeling data as relations

Two anti-patterns tend to appear when a schema is designed by analogy to something other than the relational model itself, rather than around the relations it actually needs to represent.

Avoid an Entity-Attribute-Value (EAV) schema — a generic (entity_id, attribute_name, attribute_value) table used to store arbitrary attributes for arbitrary entity types. EAV trades away the type safety, NOT NULL/CHECK constraints, and query performance that a relational schema exists to provide: every attribute value is stored as an untyped string or variant column, a query that needs several attributes at once requires a self-join per attribute, and the database engine can no longer enforce that a given attribute is present, correctly typed, or within a valid range. Where the set of possible attributes is genuinely unbounded and schema changes are genuinely impractical, prefer a schema-less store — see TS-44: Non-relational (NoSQL) databases — over simulating one inside a relational schema.

Avoid applying object-oriented design principles, such as inheritance hierarchies, directly to a relational schema. A table models a relation, not a class, and forcing an OOP mental model onto schema design tends to produce the same problems as EAV by another route — either an over-normalized hierarchy of tables mirroring a class hierarchy that has no query-time benefit, or an EAV-like structure used to simulate polymorphism. Design each table around the data it needs to hold and the queries it needs to answer, not around an application-layer class model.

Defining schemas

Readability

Order and group column definitions within a CREATE TABLE statement so the grouping makes sense to a reader — following the column-ordering convention above is a reasonable default. Indent column definitions by four spaces within the CREATE TABLE block.

Default values

A column’s default value MUST be the same type as the column itself — a DECIMAL column must not take an INTEGER literal as its default, for example. Within a column definition, the default value follows the data-type declaration and comes before any NOT NULL constraint.

CREATE TABLE orders (
    total DECIMAL(10, 2) DEFAULT 0.00 NOT NULL
);

Do not encode business logic into a DEFAULT or CHECK constraint. A default of now() on an order_date column looks convenient, but it buries a business rule — when an order is considered "placed" — inside the schema, invisible to and disconnected from the application-layer code that actually owns that rule; a later change to the rule (for example, an order is placed only once it is approved) requires finding and editing a default hidden in CREATE TABLE/ALTER TABLE, rather than in the application code a reader would expect to find it in. This does not rule out every use of DEFAULT now(): a logged_at column on a log or audit table defaulting to now() is a reasonable use, because "when was this row written" is a database-level fact, not a business rule the application layer owns.

Choosing keys

Whether a primary key or a unique key on some other column, a key’s underlying value should be:

• Unique, by definition.
• Consistent in data type across the schema, and unlikely to change over the life of the system — a key that changes type or format forces a migration of every table that references it.
• Validatable against a standard, well-known format where one exists (an ISO country code, for example), rather than an ad hoc format specific to this schema.

Keep keys as simple as possible, but use a compound key where the data genuinely requires one — for example, a joining table’s composite key of its two foreign-key columns (see Many-to-many relationships).

Prefer a UNIQUE constraint over an equivalent UNIQUE index to enforce a uniqueness guarantee, even though most database engines implement a UNIQUE constraint as a unique index under the hood. A constraint is easier to toggle — temporarily dropped and re-added — than an index, which must be dropped and recreated outright; on a large table, recreating an index is an expensive operation, while toggling a constraint is not.

Defining constraints

• Every table MUST have at least one key.
• Give constraints custom, descriptive names, except UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, where the database engine’s default generated name is usually intelligible enough on its own.
• Specify the primary key constraint first, immediately after CREATE TABLE.
• Place a single-column constraint directly beneath its corresponding column definition, indented to the right of the column name, so the constraint reads as an attribute of that column.
• Place a multi-column constraint near the columns it covers, or at the end of the table definition if it does not naturally belong near any one of them.
• Place table-level constraints at the end of the CREATE TABLE block, after all column definitions.
• Where both are specified on a foreign key, order ON DELETE before ON UPDATE — this also happens to be alphabetical order, which makes it easy to remember.
• Use LIKE or SIMILAR TO constraints to enforce string format integrity, where the format is simple enough to express as a pattern.
• Use CHECK() constraints to bound numeric values to a valid range — at minimum, CHECK (value > 0) for a value that must never be zero or negative.
• Keep each CHECK() constraint in its own clause, rather than combining multiple conditions into one, so a constraint violation is easy to attribute to a specific rule when debugging.

CREATE TABLE orders (
    id            INTEGER      PRIMARY KEY,
    customer_id   INTEGER      NOT NULL REFERENCES customers (id)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE,
    total         DECIMAL(10,2) DEFAULT 0.00 NOT NULL
                                CHECK (total >= 0),
    status        VARCHAR(20)  NOT NULL
                                CHECK (status IN
                                    ('pending', 'paid', 'shipped')),

    CONSTRAINT uq_orders_reference UNIQUE (customer_id, reference)
);

Choosing a foreign key’s ON DELETE action

Choose a foreign key’s ON DELETE action based on the semantics of the relationship it enforces, not by reflex or by leaving it at the engine’s default:

• NO ACTION or RESTRICT — where the referenced row models an independent entity that can exist without the referencing row. A products.category_id reference to categories should block deletion of a category that still has products in it, rather than silently deleting or orphaning those products.
• CASCADE — where the referencing row cannot meaningfully exist without its parent. An order’s line items referencing their order should be deleted along with the order; a line item with no order to belong to is not a meaningful row.
• SET NULL — for an optional, nullable reference where the parent’s removal should orphan the referencing row rather than delete it or block the parent’s deletion. An employee’s manager_id should be set to NULL when the manager leaves, rather than deleting the employee or preventing the manager’s removal.
• SET DEFAULT — a rarely-needed fallback, for the uncommon case where a referencing column should revert to a specific non-NULL default rather than being nulled out or cascaded.

Do not use a sentinel value — 0 or -1, for example — in a foreign-key or identifier column to represent "no value" where the column could instead be made nullable. The manager_id example above is the canonical case: not every employee has a manager, so manager_id should be a nullable foreign key with SET NULL on delete, not a NOT NULL column defaulting to a sentinel 0 that does not reference any real row. A sentinel value forces every query against the column to special-case it, and it is not enforced by the foreign-key constraint the way a genuine NULL is.

Adding and removing constraints on existing tables

Add a constraint to an existing table with ALTER TABLE, following the same naming guidance as a constraint defined at table-creation time:

ALTER TABLE orders
    ADD CONSTRAINT uq_orders_reference UNIQUE (customer_id, reference);

Column-level and multi-column UNIQUE constraints follow the same rule — name them explicitly when the constraint spans more than one column, or when a descriptive name aids debugging:

CREATE TABLE users (
    email VARCHAR(255) UNIQUE NOT NULL
);

ALTER TABLE users
    ADD CONSTRAINT uq_users_email_username UNIQUE (email, username);

To remove a UNIQUE constraint, drop the index that backs it (most database engines implement a UNIQUE constraint as a unique index under the hood):

DROP INDEX uq_orders_reference;

Data types

This section gives guidance on choosing column data types for portability, correctness, and clarity.

General principles

Avoid vendor-specific data types where a portable, standard alternative exists. A vendor-specific type is not portable across database engines, and is not guaranteed to exist in older or future versions of the same vendor’s software — a schema tied to a specific engine’s proprietary types is harder to migrate and harder to upgrade.

Avoid splitting a single value across two columns, such as storing a numeric value in one column and its unit of measurement in another. Instead, choose a column name and a storage convention that make the unit self-evident — for example, duration_seconds rather than a duration column paired with a separate duration_unit column. See also the column-suffix and type-prefix conventions in Naming conventions.

String types

Prefer CHAR, CLOB, and VARCHAR for string data. These three types have the widest cross-engine support and the most predictable behavior across database vendors, which makes them the safest default even where a vendor offers a more specialized string type.

Numeric types

• Use REAL or FLOAT only for genuine floating-point math, where an approximate result is acceptable.
• Prefer NUMERIC and DECIMAL for values where precision matters — currency amounts, for example — since floating-point types are subject to rounding errors that compound across calculations.
• Exact numeric types, which store values with no rounding error: BIGINT, DECIMAL, DECFLOAT, INTEGER, NUMERIC, SMALLINT.
• Approximate numeric types, which trade precision for a wider range and faster computation: DOUBLE PRECISION, FLOAT, REAL.

Date and time types

Prefer ISO 8601-compliant values for date and time data (YYYY-MM-DD HH:MM:SS.SSSSS). The DATE, TIME, and TIMESTAMP types are well supported across mainstream database engines and should be used for their corresponding ISO 8601 components.

Do not use a TIMESTAMP column to represent an inherently past or future date that has no meaningful time-of-day component — a birth date or a contract expiry date, for example. Prefer plain ISO 8601 date storage (DATE) for that case, and reserve TIMESTAMP for values that genuinely represent a specific instant in time.

Binary types

Use a dedicated binary type (BINARY, VARBINARY, BLOB, or your engine’s equivalent) for binary data, rather than encoding it into a string column. As with other types, prefer your engine’s most standard/portable binary type where more than one option exists.

Joins and queries

This section covers joining data across tables, and general query-writing patterns.

Join fundamentals

A relational database links tables through foreign-key columns. A join combines rows from two or more tables based on matching values in those common columns. The mainstream join types — inner, left, right, and cross — are supported across virtually every relational database engine; a full outer join is notably absent from some engines, including MySQL, and must be emulated with a UNION of a left and a right join where it is not natively supported. A JOIN clause appears after the FROM clause in a SELECT statement. See Formatting joins for how to lay out a join across multiple lines.

Inner joins

An inner join returns only the rows that have a match in both joined tables.

SELECT o.id, o.total, c.email
  FROM orders AS o
 INNER JOIN customers AS c ON o.customer_id = c.id;

Where the matching column has the same name in both tables, the USING clause is a more concise alternative to ON:

SELECT o.id, o.total, c.email
  FROM orders AS o
 INNER JOIN customers AS c USING (customer_id);

Left and right joins

A left join returns every row from the left-hand table, whether or not a matching row exists in the right-hand table. Where no match exists, the right-hand table’s columns are NULL in the result.

SELECT c.id, c.email, o.id AS order_id
  FROM customers AS c
  LEFT JOIN orders AS o ON o.customer_id = c.id;

Combined with a WHERE …​ IS NULL clause, a left join finds rows in the left table that have no match in the right table — for example, customers who have never placed an order:

SELECT c.id, c.email
  FROM customers AS c
  LEFT JOIN orders AS o ON o.customer_id = c.id
 WHERE o.id IS NULL;

A right join is the mirror of a left join: it returns every row from the right-hand table, with NULL for unmatched left-table columns. The same USING syntax and the same WHERE …​ IS NULL pattern for finding unmatched rows apply, with the tables' roles reversed.

Cross joins

A cross join produces the Cartesian product of the two joined tables — every row from the first table paired with every row from the second, giving n × m rows in the result. A cross join has no join condition. It is rarely useful for combining related data, but it is a convenient way to generate planning or scheduling data — for example, every combination of a set of dates and a set of resources.

SELECT d.calendar_date, r.resource_name
  FROM date_range AS d
 CROSS JOIN resources AS r;

Many-to-many relationships

Model a many-to-many relationship with a joining table that holds a foreign key to each side of the relationship. Use a composite key of the two foreign-key columns, rather than a separate surrogate id column, as the joining table’s primary key. This guarantees that a given pairing can only be recorded once, which a surrogate id alone would not enforce.

CREATE TABLE car_mechanics (
    car_id       INTEGER NOT NULL REFERENCES cars (id),
    mechanic_id  INTEGER NOT NULL REFERENCES mechanics (id),

    PRIMARY KEY (car_id, mechanic_id)
);

See also Table names for guidance on naming the joining table itself — prefer a name that describes the relationship over a concatenation of the two related table names, where a suitable name exists.

Joins vs subqueries

A JOIN and an equivalent subquery (a sub-SELECT nested inside another query) can often produce the same result. They are not, however, interchangeable in performance: a subquery can be significantly slower than the equivalent join, depending on the query planner’s ability to optimize it. Where a query’s performance matters, benchmark alternative formulations — a join, a subquery, and a common table expression, where applicable — against the actual data volumes the query will run against, rather than assuming one form is faster.

Paginated search

Paginate a result set with LIMIT offset, count (or the equivalent OFFSET/FETCH syntax on engines that use it).

SELECT id, name
  FROM products
 ORDER BY name
 LIMIT 20 OFFSET 40;

A paginated UI typically also needs the total number of matching rows, to render page counts or a "showing X of Y" indicator. There are three common ways to obtain it:

1. A separate, un-LIMIT`ed count query. Run `SELECT COUNT(*) FROM …​ with the same WHERE clause as the paginated query. Simple and portable, at the cost of a second query per page request.
2. Cache the count. Where the underlying data does not change on every request, compute the count once and cache it, invalidating the cache when the underlying data changes.
3. A vendor-specific mechanism. MySQL, for example, offers SQL_CALC_FOUND_ROWS in the paginated query combined with a subsequent SELECT FOUND_ROWS() call, avoiding a second full table scan. This sacrifices portability for a performance gain specific to one engine — weigh that trade-off per SQL style and formatting's general guidance on vendor-specific features.

Where a user pages through the same result set repeatedly, consider storing the total count in a session variable after the first page request, rather than recomputing or re-fetching it on every subsequent page.

Query formalisms

• Prefer BETWEEN over a chained pair of AND-joined comparisons (value BETWEEN 10 AND 20 rather than value >= 10 AND value ⇐ 20).
• Prefer IN (…​) over a chain of OR-joined equality comparisons (status IN ('pending', 'paid') rather than status = 'pending' OR status = 'paid').
• Use CASE to interpret or transform a value based on conditions. CASE expressions may be nested where a single level of conditions is not sufficient.
• Avoid UNION and temporary tables where the underlying schema can instead be optimized to remove the need for them — both are frequently a workaround for a schema design that does not fit the query being written, rather than a first-choice tool.

Functions

Prefer standard SQL functions over vendor-specific ones, for the same portability reasons as vendor-specific data types and keywords (see SQL style and formatting and Data types). Use a vendor-specific function where it offers a significant, concrete advantage — most often performance — that outweighs the portability cost of depending on it.

Transactions and consistency

A database transaction groups one or more statements into a single unit of work. This section sets out the guarantees a transaction should provide, the isolation levels available to trade correctness against concurrency, and practical patterns for using transactions to protect data integrity.

ACID

ACID — atomicity, consistency, isolation, durability — is the set of properties a transaction is expected to guarantee, so that data remains valid despite errors, concurrent access, and system failures. The ACID paradigm has shaped the design of relational database systems since their earliest implementations, and remains the baseline expectation for a transactional relational database today.

Atomicity

A transaction is an all-or-nothing unit of work. If any statement within the transaction fails, the whole transaction fails and the database is left unchanged, as though none of its statements had run. A transaction in progress cannot be observed by another client — its intermediate state is never visible outside the transaction itself.

Atomicity is not automatic in every kind of database. Document-oriented databases such as MongoDB, RethinkDB, and CouchBase are typically atomic only at the level of a single document or row, not across multiple documents written together. Where an operation must update several related records as one unit, and the underlying store’s atomicity does not extend that far, the application is left to invent its own consistency guarantees. In practice this looks like: invalid intermediate state becoming visible to other readers; ad hoc retry logic; one-off "fixer scripts" written after the fact to repair rows left in a bad state; engineers spending their time as "data janitors" cleaning up after failures that a real transaction would have prevented; and application code that mutates over time to defensively handle an ever-growing set of bad-state combinations nobody fully enumerated. A relational database’s built-in multi-statement atomicity avoids this whole class of problem, which is one of the strongest arguments for reaching for a relational store, or at minimum a document store’s native multi-document transaction support, for state where this class of integrity actually matters.

Consistency

A transaction can only move the database from one consistent state to another — it preserves whatever invariants the schema defines. Data written by a committed transaction must satisfy all defined rules: constraints, cascades, and triggers. Referential integrity is a common example of such an invariant — a foreign key must reference a row that exists, and the database enforces that on every write, not just at the point the application believes it is inserting valid data.

A UNIQUE constraint is a particularly effective, low-ceremony way to enforce a consistency invariant. Consider a user-registration flow that must prevent two accounts from sharing the same email address: rather than relying entirely on an application-level check for an existing row before inserting a new one — which is vulnerable to a race between two concurrent registration requests, see Concurrency protection with SERIALIZABLE — a UNIQUE constraint on the email column guarantees the invariant at the database level, regardless of what the application code does or fails to do.

Isolation

Isolation is the property that concurrent execution of transactions leaves the database in the same state as if those transactions had executed one after another, sequentially. Isolation is the primary goal of concurrency control in a database engine. Depending on the isolation level in effect, the effects of another, still-incomplete transaction may or may not be visible to a concurrently running transaction.

SQL defines four standard isolation levels, each permitting a progressively narrower set of concurrency anomalies:

A weaker isolation level generally allows greater concurrency — more transactions can run in parallel without blocking each other — at the cost of tolerating more of the anomalies in the table above. Choose the weakest isolation level that your application’s correctness requirements can tolerate, and reach for SERIALIZABLE where a specific operation’s correctness genuinely depends on it (see Concurrency protection with SERIALIZABLE).

Do not implement your own pessimistic locking scheme — application-level locks, advisory locks used as a substitute for proper isolation, or polling-based mutual exclusion — as a substitute for using the database’s own isolation levels. Custom locking is typically slower than the database engine’s built-in concurrency control, harder to reason about correctly, labor-intensive to implement and maintain, and more likely to contain bugs than the battle-tested locking or multi-version concurrency control (MVCC) built into the database engine.

Durability

Once a transaction commits, its effects persist despite a subsequent system failure — a crash, a power loss, or an operating-system fault must not lose a committed transaction. Durability is usually achieved by recording completed transactions to non-volatile storage (disk) before acknowledging the commit to the client, rather than relying solely on data held in volatile memory.

ACID vs BASE

BASE — basically available, soft state, eventually consistent — describes the opposite end of the consistency spectrum from ACID. Where ACID prioritizes strict consistency, potentially at the cost of availability during a partition, BASE prioritizes availability and accepts that the system will only become consistent eventually, after any conflicting writes have propagated and settled.

This trade-off is the same one described by the CAP theorem: a distributed data store leans toward strong consistency (the "C" in ACID) or toward availability (the "A" in BASE) when the network partitions. Relational, SQL databases have traditionally leaned toward the ACID end of this spectrum; many NoSQL databases lean toward BASE, trading strict consistency for availability and horizontal scalability. Neither is universally correct — the right choice depends on which failure mode your application can tolerate. See also Designing a sharding strategy, which touches the same trade-off in the narrower context of eventual consistency across shards.

Transaction implementation techniques

A database engine typically implements atomicity and durability through a combination of write-ahead logging and, in some implementations, shadow paging. Write-ahead logging records a transaction’s intended changes to a durable log before applying them to the database’s main data structures, so that a crash mid-transaction can be recovered from — either by replaying a committed transaction’s logged changes, or by rolling back an uncommitted one.

To provide isolation, a database engine must acquire locks on the data it is about to update — and, depending on the isolation level in effect, possibly also on data it merely reads, so that a concurrent transaction cannot modify data this transaction depends on before it commits.

Locking vs multiversion concurrency control

There are two broad strategies for implementing isolation:

• Two-phase locking acquires all the locks a transaction needs before it can release any of them, providing full isolation at the cost of reduced concurrency — a transaction waiting on a lock blocks until the lock is released.
• Multiversion concurrency control (MVCC) instead gives a reader an unmodified, previously committed version of the data, rather than making it wait on a writer’s lock. This means readers do not block writers, and writers do not block readers. Snapshot isolation is one common MVCC implementation, giving each transaction a consistent snapshot of the database as it existed when the transaction began — a form of isolation that is strong in practice, though not identical to full SERIALIZABLE isolation in every edge case.

Most mainstream relational database engines use MVCC as their default concurrency-control mechanism precisely because of the advantage described in Isolation above: it outperforms and out-scales a hand-rolled locking scheme in the overwhelming majority of cases.

Distributed transactions

A transaction that spans more than one node — where no single database instance holds all the data being modified — introduces complications beyond those of a single-node transaction. The two-phase commit protocol (distinct from two-phase locking, described above) is a common technique for providing atomicity across a distributed transaction: a coordinator first asks every participating node to prepare to commit, and only instructs the participants to formalize the commit once every participant has confirmed it is ready. If any participant fails to prepare, the coordinator instructs every participant to abort instead, so the transaction remains all-or-nothing across every node involved.

Concurrency protection with SERIALIZABLE

SERIALIZABLE isolation is a practical tool for closing a specific, common class of race condition: two concurrent requests that each check whether some condition holds, and then act on the assumption that it still holds by the time they write. A user-registration endpoint is a canonical example — two concurrent "create user" requests for the same email address might each run:

SELECT id FROM users WHERE email = 'alice@example.com';
-- Both requests see no matching row, so both proceed to:
INSERT INTO users (email) VALUES ('alice@example.com');

Under a weaker isolation level, both requests can see no existing row and both can proceed to insert, producing two rows for the same email. Running the check-then-insert sequence inside a SERIALIZABLE transaction gives the database license to detect this conflict and abort one of the two transactions with a serialization failure, exactly as if the two requests had been forced to run one after the other rather than concurrently.

Retrying an abort

A transaction aborted for a serialization failure is not an application error — it is the isolation level doing its job. The correct response is to retry the aborted transaction, either manually within the same request handling loop, or automatically through an ORM facility that supports it — for example, Sequel’s retry_on: [Sequel::SerializationFailure] option. Design any code path that runs inside a SERIALIZABLE transaction to be safely retryable, since a retry re-executes the whole transaction body from the start.

This request-level retry loop is closely related to the idempotency concerns covered for HTTP APIs in TS-21: HTTP APIs — specifically, that standard’s treatment of idempotent request handling assumes a simpler one-request-to-one- transaction model than the retry-within-a-request pattern described here, which is worth bearing in mind when the two standards are applied to the same endpoint.

Data protection in layers

Even when using SERIALIZABLE isolation to protect a check-then-insert sequence, add a UNIQUE constraint on the column that must be unique as well. The constraint is defense-in-depth: it protects the invariant even if a transaction is invoked without the intended isolation level — for example, because of a configuration mistake, a connection pool default, or buggy code that opens a transaction at the wrong isolation level. Treat the isolation level and the constraint as two independent layers protecting the same invariant, not as alternatives to each other.

References

• mysqltutorial.org. MySQL JOIN Made Easy For Beginners. — The source for the join fundamentals, inner, left, right, and cross join guidance in Join fundamentals, Inner joins, Left and right joins, and Cross joins.
• Wikipedia. ACID. — The primary source for the ACID properties, BASE, the CAP theorem framing, transaction implementation techniques, locking vs. MVCC, and distributed transactions in ACID, ACID vs BASE, Transaction implementation techniques, and Distributed transactions.
• Leppka, B (2017). Building Robust Systems with ACID and Constraints. — The source for the document-level atomicity critique in Atomicity, the four named isolation levels and their phenomena in Isolation, and the duplicate-email consistency example in Consistency.
• Leppka, B (2021). Using Atomic Transactions to Power an Idempotent API. — The source for Concurrency protection with SERIALIZABLE, including the retry and defense-in-depth guidance in its "Retrying an abort" and "Data protection in layers" subsections.
• Holywell, S. SQL Style Guide. — The source for the EAV-avoidance and OOP-schema-avoidance guidance in Modeling data as relations.
• Elnur (2020, archived). Old, Good Database Design. — The source for the UNIQUE-constraint-vs-index guidance in Choosing keys, the ON DELETE action-selection and sentinel-value guidance in Choosing a foreign key’s ON DELETE action, and the business-logic-in-constraints caution in Default values. The live URL now redirects to the site’s homepage with no article content; retrieved via the Wayback Machine.