---
term: 'Database Schema'
seoTitle: 'What is a Database Schema? Complete Guide'
headline: 'What is a Database Schema?'
slug: database-schema
category: database
shortDefinition: 'A database schema is a blueprint that defines how data is organized — the classes, columns, types, and relationships, but not the data.'
relatedTerms:
- data-modeling
- visual-database-management
- class-level-permissions-clp
- database-queries
contrastsWith:
- data-modeling
faq:
- question: 'What is a database schema in simple terms?'
answer: 'The blueprint of a database: which tables or classes exist, what columns they have, the type of each column, the keys and constraints, and how records relate. It is metadata — the structure, not the stored data. Change the schema and you change what shape data may take; the data itself lives inside that shape.'
- question: 'What is the difference between a schema, a database, and an instance?'
answer: 'Three zoom levels. The database is the whole system — engine plus stored data. The schema is its formal structure, which changes rarely and deliberately. An instance is the actual data at one moment, changing with every write. One schema, one database, endlessly many instances over time.'
- question: 'What is the difference between a logical and a physical schema?'
answer: 'The logical schema is the engine-independent design: entities, attributes, relationships, constraints — what an ER diagram draws. The physical schema is how that design lands in a specific engine: storage layout, indexes, partitions. A third layer, the view or external schema, defines what each consumer sees. Same design, three altitudes.'
- question: 'What does a schema actually contain?'
answer: 'The schema objects: tables or classes, columns with data types, primary and foreign keys, constraints like NOT NULL, UNIQUE, and CHECK, indexes, and views. In some engines "schema" has a second meaning too — a named namespace that groups these objects and carries access permissions, which is what CREATE SCHEMA creates.'
- question: 'Do NoSQL databases have schemas?'
answer: '"Schemaless" is a misnomer — the schema always exists; the question is who enforces it and when. Relational engines are schema-on-write: structure is validated before data lands. Document stores default to schema-on-read: structure lives in application expectations and is checked when data is used. Most document platforms now support validation too, making strictness a dial rather than a dichotomy.'
- question: 'What is a schema migration?'
answer: 'A versioned, scripted change to the schema — adding a column, tightening a constraint — applied incrementally and in order across environments. Migrations are how schemas evolve without chaos: each change is reviewable, repeatable, and reversible, and the schema version travels with the codebase that expects it.'
- question: 'What are star and snowflake schemas?'
answer: 'Analytics-specific schema shapes. A star schema puts one central fact table (events, sales) amid denormalized dimension tables — few joins, fast aggregation. A snowflake normalizes those dimensions into sub-tables — less redundancy, more joins. They optimize reporting workloads and are cousins, not competitors, of the transactional schemas application backends use.'
- question: 'How is a schema defined on a Backend-as-a-Service?'
answer: 'Two complementary ways: visually — classes and typed columns created in a dashboard — and by inference, where saving the first object creates the class and typed columns automatically, with default fields like objectId, createdAt, updatedAt, and an ACL added by the platform. Production hardening then freezes it: client-driven schema changes off, further evolution through the dashboard on purpose.'
codeLanguages: [javascript, dart, swift, kotlin]
externalAuthorities:
- name: 'Database schema (Wikipedia)'
url: 'https://en.wikipedia.org/wiki/Database_schema'
- name: 'PostgreSQL — Data Definition documentation'
url: 'https://www.postgresql.org/docs/current/ddl.html'
- name: 'Introduction to database schemas — Prisma Data Guide'
url: 'https://www.prisma.io/dataguide/intro/intro-to-schemas'
- name: 'Back4app database hub documentation'
url: 'https://www.back4app.com/docs'
cta:
title: 'A schema you can see'
text: 'On Back4app the schema is a living surface: create classes and typed columns in the dashboard or let the first save infer them, browse and evolve everything visually, and lock it down with class-level permissions when you ship.'
linkText: 'Start Free'
linkUrl: 'https://www.back4app.com/signup'
author: 'Back4app Engineering'
publishedDate: '2026-07-26'
translationKey: database-schema
---
**A database schema is a blueprint that defines how data is organized — the classes, columns, types, and relationships, but not the data.** The one-line trinity worth memorizing: the *schema* is the blueprint, an *instance* is the data at one moment, and the *database* is the whole building. Blueprints change rarely and deliberately; the rooms refill constantly.
## Key takeaways
| Question | Answer |
| --- | --- |
| What it is | Structure as metadata: tables/classes, typed columns, keys, constraints |
| What it isn't | The data — that's the instance, changing with every write |
| The three altitudes | Logical (design) · physical (storage) · view (what each consumer sees) |
| "Schemaless"? | A misnomer — schema-on-read just moves enforcement to query time |
| How it evolves | Migrations: versioned, scripted, reviewable changes |
## The blueprint, written down
A schema in its native tongue — two tables, keys, a constraint, an index, and a view, which is most of the vocabulary:
```sql
CREATE TABLE users (
id bigserial PRIMARY KEY,
email text NOT NULL UNIQUE, -- constraint: no duplicates
role text NOT NULL DEFAULT 'member'
);
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id), -- relationship
total numeric(10,2) CHECK (total >= 0), -- rule the data must obey
placed timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_by_user ON orders (user_id, placed); -- physical layer
CREATE VIEW recent_orders AS -- view layer
SELECT * FROM orders WHERE placed > now() - interval '30 days';
```
The same blueprint on a schema-flexible platform grows from what you save — typed columns inferred on first write, visible immediately in a dashboard:
**JavaScript:**
```javascript
// JavaScript / Node.js — Back4app JS SDK
// The schema grows typed columns from what you save
const event = new Parse.Object('Event');
event.set('name', 'Launch day'); // String
event.set('seats', 120); // Number
event.set('startsAt', new Date('2026-09-01T18:00:00Z')); // Date
event.set('venue', new Parse.GeoPoint(38.72, -9.14)); // GeoPoint
await event.save(); // columns exist, typed, visible in the dashboard
```
**Flutter:**
```dart
// Flutter / Dart — Back4app Flutter SDK
// The schema grows typed columns from what you save
final event = ParseObject('Event')
..set('name', 'Launch day') // String
..set('seats', 120) // Number
..set('startsAt', DateTime.parse('2026-09-01T18:00:00Z')) // Date
..set('venue', ParseGeoPoint(latitude: 38.72, longitude: -9.14));
await event.save(); // columns exist, typed, visible in the dashboard
```
**Swift:**
```swift
// iOS / Swift — Back4app Swift SDK
// The schema is a typed struct — columns mirror the model
struct Event: ParseObject {
var objectId: String?; var createdAt: Date?
var updatedAt: Date?; var ACL: ParseACL?; var originalData: Data?
var name: String? // String column
var seats: Int? // Number column
var startsAt: Date? // Date column
var venue: ParseGeoPoint? // GeoPoint column
}
var event = Event(); event.name = "Launch day"; event.seats = 120
event.save { _ in } // columns exist, typed, visible in the dashboard
```
**Kotlin:**
```kotlin
// Android / Kotlin — Back4app Android SDK
// The schema grows typed columns from what you save
val event = ParseObject("Event").apply {
put("name", "Launch day") // String
put("seats", 120) // Number
put("startsAt", Date()) // Date
put("venue", ParseGeoPoint(38.72, -9.14)) // GeoPoint
}
event.saveInBackground() // columns exist, typed, visible in the dashboard
```
## Logical vs. physical vs. view
```mermaid
flowchart LR
accTitle: The three schema layers
accDescr: The logical schema holds the engine-independent design of entities and relationships; the physical schema maps it onto storage with indexes and partitions; view schemas expose tailored slices to each consumer.
L["Logical schema
entities · relationships · constraints
(the ER diagram)"]
P["Physical schema
storage · indexes · partitions
(one engine's reality)"]
V["View schemas
tailored slices per consumer"]
L --> P --> V
```
| Distinction | This | vs. that |
| --- | --- | --- |
| Schema vs. instance | The blueprint, changes rarely | The data snapshot, changes constantly |
| Logical vs. physical | Engine-independent design | Engine-specific storage decisions |
| Blueprint vs. namespace | "The schema" of your app | `CREATE SCHEMA` — a named container of objects with permissions |
| Schema-on-write vs. on-read | Validated before data lands (relational) | Enforced at use time (document default) |
| Transactional vs. analytical | Normalized app schemas | Star/snowflake shapes built for aggregation |
The third row defuses a genuine ambiguity most explainers skip: in some engines the word also names a *namespace* — a permissioned container of tables — so "the schema" can mean your app's blueprint or a folder inside the database, and context decides.
## Schema-on-write vs. schema-on-read
"Schemaless" databases have schemas — they just bill for them differently. **Schema-on-write** validates structure before data lands: wrong type, missing field, broken reference — rejected at the door. **Schema-on-read** accepts writes flexibly and enforces expectations when data is used — faster iteration, and every reader becomes a validator. The modern position is a dial, not a war: document platforms add validation, relational engines add JSON columns, and managed backends split the difference — types inferred and enforced per column, while new columns appear without a migration ceremony. The strictness question is really an ownership question: *who* finds the malformed record, the database at write time or your code at 2 a.m.?
## How schemas evolve
The blueprint outlives its first draft, and **migrations** are how it changes without chaos: each schema change is a versioned script — add the column, backfill it, tighten the constraint — applied in order, in every environment, reviewed like the code that depends on it. Two disciplines carry most of the value: make changes *backward-compatible* in the window where old and new code overlap (add-then-migrate-then-remove, never rename-in-place), and keep the schema version in the repository so code and structure travel together. On dashboard-managed platforms, the same discipline applies with different tooling — evolve visually, but deliberately, with client-driven schema changes disabled in production.
## Common use cases
- **Designing a new backend.** The schema is [data modeling's](/glossary/data-modeling/) output: entities and edges become classes, columns, and keys.
- **Enforcing integrity.** Constraints as executable rules — non-negative totals, unique emails — caught by the engine, not by bug reports.
- **Team contract.** The schema is the shared vocabulary between backend, frontend, and analytics; an ER diagram is documentation that can't drift.
- **Performance groundwork.** Indexes and physical layout — the schema's lower floor — decide what queries stay fast at scale.
- **Security surface.** Schema-level permissions gate who may do what per class — structure and access control in one place.
## How strict should your schema be? A decision matrix
| Favor strict (schema-on-write) when… | Favor flexible (infer + validate) when… |
| --- | --- |
| Data errors are expensive (money, inventory) | You're iterating on the product weekly |
| Many writers, one contract | One team owns both code and data |
| Analytics depend on stable columns | Fields genuinely vary per record |
| Constraints encode business rules | Rules live in server-side validation anyway |
| Migrations are routine for the team | Migration ceremony would slow discovery |
The pragmatic default for app backends: flexible while you learn, hardening as you ship — infer the schema during development, then freeze it (no client schema changes, add-field off) the day real users arrive.
## Limitations and trade-offs
- **The schema freezes assumptions.** Every column type and cardinality decision is cheap today and a migration tomorrow — design for the next size up.
- **Strictness taxes iteration.** Every experiment pays the migration toll; that's the price of the guarantees, not a flaw.
- **Flexibility taxes readers.** Schema-on-read moves validation into every consumer; without discipline, "flexible" becomes "five shapes of the same record."
- **The physical layer is invisible until it isn't.** Indexes and layout don't change correctness — only whether queries survive growth.
- **Namespaced permissions aren't design.** `CREATE SCHEMA` organizes and gates objects; it doesn't make the blueprint good.
## The schema on Back4app
Back4app is an open-source Backend-as-a-Service (BaaS) platform that combines a managed database, auto-generated REST and GraphQL APIs, authentication, file storage, and Cloud Code serverless functions. The schema is a first-class, visible surface: define classes and typed columns in the dashboard, or let the first save infer them — the code tabs above create real typed columns, with `objectId`, `createdAt`, `updatedAt`, and an ACL added to every class by default. Everything the schema declares is instantly reflected in the [auto-generated APIs](/glossary/auto-generated-database-apis/), guarded by [class-level permissions](/glossary/class-level-permissions-clp/), and browsable in the [visual dashboard](/glossary/visual-database-management/) — the blueprint, the enforcement, and the documentation as one artifact.