What Is a Database Schema?
A database schema is the blueprint that defines how data is organised in a database. It specifies the tables, the columns within each table, the data type of each column (text, number, date, and so on), and constraints that enforce data quality rules such as 'this value cannot be empty' or 'this value must be unique'. The schema is designed before data is stored and determines what kinds of queries you can run efficiently.
A database schema defines the structure of your data: which tables exist, which columns they have, and what rules apply to the data stored in them.
What a Schema Actually Contains
A database schema is a formal description of structure, not content. It answers questions like: how many tables does this database have, what are the columns in each table, and what type of data can each column store? The schema does not contain the actual rows of data; it defines the container those rows will fill.
Consider a simple webshop. The schema might define three tables: customers, products, and orders. The customers table has columns for id (a unique integer), name (text, maximum 100 characters), email (text, unique across all rows), and created_at (a timestamp). The orders table has id, customer_id (which links to the customers table), total_amount (a decimal number), and status (text limited to specific values like ‘pending’, ‘shipped’, ‘delivered’).
Defining the schema this way does two things: it prevents invalid data from being stored (the database rejects an order with no customer ID), and it tells the database engine enough about the structure to create efficient indexes and query plans.
Tables, Columns, and Data Types
Every table in a relational database stores one type of entity. The columns define the attributes of that entity, and each column has a data type that constrains what values it can hold.
Common data types include:
- INTEGER or BIGINT for whole numbers (quantities, counts, auto-incrementing IDs)
- NUMERIC or DECIMAL for precise numbers with decimals (prices, percentages)
- VARCHAR(n) or TEXT for strings of characters (names, descriptions)
- BOOLEAN for true/false values
- TIMESTAMP or DATE for points in time
- UUID for universally unique identifiers, often used as primary keys
Choosing the right data type matters for correctness and performance. Storing a price as TEXT instead of DECIMAL means you cannot sort it numerically or perform calculations on it without conversion. Storing a country code as TEXT when there are only ~250 possible values might be better served by an ENUM or a lookup table.
For a deeper introduction to how these components fit together, see what is a relational database.
Constraints: Enforcing Data Quality at the Database Level
Constraints are rules attached to columns or tables that the database enforces automatically, regardless of the application code. The most important ones:
NOT NULL means the column must always have a value. An orders row without a customer_id is meaningless, so that column should be NOT NULL.
UNIQUE means no two rows can have the same value in that column. Email addresses are a classic example: you generally want to prevent duplicate accounts.
PRIMARY KEY combines NOT NULL and UNIQUE and marks the column as the table’s unique row identifier.
FOREIGN KEY links a column in one table to the primary key of another. If you define orders.customer_id as a foreign key referencing customers.id, the database will refuse to insert an order with a non-existent customer ID, and it can also prevent you from deleting a customer who still has orders.
CHECK allows you to define a custom condition, such as price > 0 or status IN ('pending', 'shipped', 'delivered').
These constraints mean you spend less code in your application validating data, because the database catches violations before they are persisted.
Normalisation: Storing Each Fact Once
Without discipline, database schemas grow messy. The most common mistake is repeating data across rows. In a naive design, each row in an orders table might contain the customer’s full name, email, and shipping address. When the customer updates their address, you would need to update every existing order row, a tedious operation prone to inconsistency.
Normalisation prevents this by storing each fact in exactly one place. The customer’s address lives in the customers table. The orders table stores only the customer_id that points to it. To retrieve an order with the customer’s current address, you run a JOIN query that combines the two tables at query time.
Normalisation reduces storage, prevents inconsistency, and makes updates simpler. Its main trade-off is that queries for combined data require JOINs, which are slightly more complex to write but are highly optimised in modern relational databases like PostgreSQL.
Schema as the Foundation of Every Application
The schema you design at the start of a project shapes almost every decision that follows. Changing a schema later, adding a column, splitting a table, or changing a data type, is possible but increasingly costly as data accumulates and application code builds up around the existing structure.
Tools like Supabase manage schema changes through migrations: versioned SQL files that describe each change to the schema in sequence. This gives you a full history of how the structure evolved and lets you reproduce the exact schema in a new environment by running the migrations in order.
Our tip: Before writing any application code, sketch your schema as an ER diagram, even just on paper. List your entities, their key attributes, and the relationships between them. Catching a missing relationship or a normalisation problem at the design stage takes five minutes. Catching it after you have six months of production data is a significantly larger project.
Frequently asked questions
What is the difference between a primary key and a foreign key?
A primary key is a column (or combination of columns) that uniquely identifies each row in a table. Every table should have one, and the value must be unique and never null. A UUID or an auto-incrementing integer are common choices. A foreign key is a column in one table that references the primary key of another table, creating a link between the two. In a webshop, the `orders` table has a foreign key `customer_id` that references the `id` column in the `customers` table. The database can enforce this link, preventing you from creating an order for a customer ID that does not exist.
What is database normalisation and why does it matter?
Normalisation is the process of organising a database schema to reduce redundancy and prevent inconsistency. The core idea is that each piece of information should live in exactly one place. In a poorly designed webshop, every row in the orders table might repeat the customer's name and address. If the customer moves, you would need to update dozens of rows, and missing one creates inconsistent data. A normalised schema stores the customer's address once in the customers table and references it by ID from orders. First Normal Form (1NF) eliminates repeating groups, Second Normal Form (2NF) removes partial dependencies, and Third Normal Form (3NF) removes transitive dependencies. Most applications need to reach at least 3NF.
What is an ER diagram?
An Entity-Relationship (ER) diagram is a visual representation of a database schema. Each entity (table) is shown as a rectangle containing its column names. Lines between entities represent relationships, and the notation at each end of the line (a single line, a fork, or a circle) indicates the cardinality: one-to-one, one-to-many, or many-to-many. An ER diagram is a communication tool used to discuss and validate a schema design before building it, making it easier to spot missing relationships or unnecessary tables early. Most database tools, including Supabase, can generate an ER diagram automatically from an existing schema.