Database Schema

In Turkish: Veritabanı Şeması
Pronunciation: DAY-tuh-bays SKEE-muh

Updated September 30, 20262 min read

In short

A database schema is the blueprint of a database that defines its tables, columns, data types, relationships, and the rules that stored data must follow.

What is a database schema?

A database schema is the formal description of how data in a database is organized. In a relational database, it lists the tables, the columns in each table, the data type of every column, and constraints such as primary keys, foreign keys, and NOT NULL rules. The schema describes the structure only, not the actual rows of data stored inside it.

You define a schema with SQL statements such as CREATE TABLE and change it later with ALTER TABLE, usually through versioned database migrations so every environment stays in sync. The database then enforces the schema on every write: if you try to insert text into an integer column or leave a required field empty, the write is rejected. This is called schema-on-write, and it keeps bad data out early.

A good analogy is the architectural blueprint of a building: it shows the rooms and how they connect, but not the furniture or the people inside. Many NoSQL databases are described as schemaless, but in practice the application still expects a certain shape, so the schema simply moves from the database into application code. This approach is sometimes called schema-on-read.

The word schema can also cause confusion because some databases, such as PostgreSQL, use it for a namespace: a named container that groups tables inside one database, like public or sales. In that sense, sales.orders means the orders table in the sales schema. The context usually tells you whether someone means the overall structure or this kind of namespace.

Key takeaways

A schema defines the structure of a database, not the data itself.
It covers tables, columns, data types, keys, and constraints.
Schema changes are usually applied through versioned migrations.
Relational databases enforce the schema whenever data is written.
In PostgreSQL, a schema is also a namespace that groups tables.

Example

Defining the schema of two related tablessql

-- Each column has a name, a data type, and optional rules
CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  total       NUMERIC(10, 2) NOT NULL,
  created_at  TIMESTAMP DEFAULT now()
);

Readers ask

What is the difference between a schema and a table?

A table is one structure that holds rows of data, while the schema is the full design of the database, including all its tables and the relationships between them. In PostgreSQL, a schema can also be a named group of tables.

Do NoSQL databases have a schema?

Many NoSQL databases do not enforce a fixed schema, so each record can have different fields. The data still has an implied shape that the application relies on, and many document databases let you add optional validation rules.

How do you change a database schema safely?

Use database migrations: small, versioned scripts that apply each change in order and can be reviewed and tested like code. For live systems, prefer backward-compatible steps, such as adding a new column before removing an old one.