What is Database Schema?

Two meanings of “schema”

Most of the time, database schema means the entire structure of the data: tables, columns, types, relationships, constraints, indexes and views. In some databases SCHEMA is also a namespace. PostgreSQL groups tables under schemas such as public.orders or billing.invoices and lets you grant permissions per schema, while in MySQL SCHEMA is simply a synonym for DATABASE. A third meaning is worth keeping apart: in SEO, “schema” usually refers to structured data written with the Schema.org vocabulary, which has nothing to do with database structure.

Business rules live in the schema

A good schema does more than hold data; it states what valid data looks like. Every line of the definition below is a business rule:

CREATE TABLE invoices (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),
  number       text NOT NULL UNIQUE,
  currency     char(3) NOT NULL DEFAULT 'EUR',
  amount       numeric(12,2) NOT NULL CHECK (amount >= 0),
  status       text NOT NULL CHECK (status IN ('draft', 'issued', 'paid', 'void')),
  issued_at    timestamptz
);

• The primary key identifies each invoice; the foreign key prevents invoicing a customer who does not exist.
• UNIQUE stops an invoice number from being used twice, and the CHECK constraints reject negative amounts and undefined statuses.
• Using numeric for money is deliberate: floating-point types cannot represent decimal fractions exactly and produce rounding errors in cents.
• timestamptz stores time with time zone handling, so servers and users in different zones interpret the same moment the same way.

The application will naturally check these rules too, but the database is the last line of defence: another service, a hand-run script or a buggy release hits the same rules.

Normalisation and deliberate duplication

The core idea of normalisation is that every fact is stored once. Copying a customer's city onto every order row means that when the city changes, some rows get updated and others go stale. So customer details live in their own table and orders point to it by key. Not every repetition is a mistake, though. The shipping address on an order should stay exactly as it was at purchase time even if the customer moves later; that is a historical record, not a copy. Denormalising for read performance, for example keeping a product's review count in its own column, is legitimate too, provided you design explicitly how that copy stays correct.

There is no such thing as schemaless

Relational databases enforce the schema when data is written (schema-on-write). Many NoSQL systems accept records without a predefined structure and get called “schemaless”. The schema does not vanish, though; it moves into application code. Code that assumes which fields a document contains is a schema. The real difference is who enforces it. A collection holding documents saved in three different shapes is a classic discovery during reporting, which is why several document databases offer optional schema validation.

Keeping a schema healthy

• Change the schema through database migrations versioned in the repository, never by hand, so every environment ends up with the same structure.
• Pick naming conventions, such as singular or plural table names and snake_case columns, and apply them everywhere.
• If you use an ORM, check regularly in CI that the model definitions and the real schema have not drifted apart.
• Treat indexes on filtered and joined columns as part of schema design; adding them later on large tables is more work.