Database Schemas

What a Database Schema Defines

What is a database schema? A database schema is the blueprint that describes how data is structured. It defines which tables exist, which columns each table has, what type of value each column holds and which rules the data must follow.

A schema also defines how tables relate to each other. An orders table may declare that its customer column must match an existing row in the customers table. This link is called a foreign key, and it lets the DBMS reject an order for a customer who does not exist.

Rules are declared as constraints. Common examples include NOT NULL, which requires a value; UNIQUE, which prevents two rows from sharing one value; and DEFAULT, which fills in a value when none is given. Some systems also support CHECK constraints, which test a value against a condition, such as a quantity that must not be negative.

Schema-on-Write, Flexible Designs and Naming

Relational databases usually enforce the schema on write. The structure is defined before data arrives, and every new row must fit it. This strictness catches errors early and keeps data consistent, which is why it suits accounts, invoices and bookings.

Document and other NoSQL databases are often described as schema-less, but the term is misleading. Their records do not have to follow one fixed layout, so the structure is applied later, when the data is read, usually by the application. The data still has a shape. The database simply checks less.

The word "schema" also has a specific meaning in MySQL, where a schema is the same thing as a database. In PostgreSQL, a schema is a named group of objects inside a database. Good schema design is covered in normalization, which organizes tables to reduce duplicated data.

Changing a Schema Over Time

Applications change, so schemas change too. Adding a column, renaming a table or changing a data type are all schema changes. On a live site with existing data, a change can affect many rows and the code that reads them. Teams therefore record each change as a migration, a versioned script that moves the schema from one state to the next.

Migrations let the same changes be applied in the same order to a development copy, a test environment and the live site. They also make changes easier to review before they run. Good design reduces how often changes are needed, but it cannot remove the need for them.