| Schema Type | Structure | Pros | Cons |
|---|
| Star Schema | 1 fact table + N denormalised dimension tables | Fast queries, simple joins | Data redundancy in dimensions |
| Snowflake Schema | Normalised dimensions split into multiple tables | Less storage, easier updates | More joins, slower queries |
Star schema is the default for OLAP warehouses (Snowflake, BigQuery, Redshift). Snowflake schema is used when storage efficiency matters.
Schema Design — Best Practices
- Choose correct data types. Use VARCHAR(n) for limited strings, TEXT for unlimited, INTEGER/BIGINT for IDs, DECIMAL for money.
- Normalise first, denormalise for performance. 3NF is a good starting point. Denormalise where query patterns demand it.
- Use foreign keys for relationships. Referential integrity is enforced by the DBMS, not application code.
- Index lookup columns. Foreign keys, WHERE clause columns, and ORDER BY columns.
- Avoid reserved words in names. Don't name a column "order" or "user" — quote them.
- Document every table. Use COMMENT ON TABLE in PostgreSQL or extended properties in SQL Server.
- Plan for migrations. Use tools like Flyway, Liquibase, or Alembic to version-control schema changes.
Schema Example — E-commerce
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_created ON orders(created_at);
Common Schema Design Pitfalls
Watch out for these common mistakes when designing schemas:
- Using reserved words as column names: Naming columns "order", "user", or "group" requires quoting everywhere and breaks some tools. Use "order_id", "user_id", "group_name".
- Over-normalising to the point of unreadability: Joining 8 tables for every query kills performance. Strategic denormalisation for read-heavy workloads is fine.
- No foreign key constraints: Many schemas skip FKs "for performance", but lose referential integrity. Always use FKs unless you have a very specific reason not to.
- Storing JSON as VARCHAR without constraints: Loose JSON columns become unsearchable garbage dumps. Use JSONB (PostgreSQL) with proper schema validation.
- No migration history: Manual schema changes lead to drift between dev, staging, and prod. Always version-control schema changes with tools like Flyway, Liquibase, or Alembic.
Quick Reference — Cheatsheet
- Schema is the blueprint; database is the container; table is the data.
- Star schema for analytics; 3NF for OLTP.
- Always use foreign keys — let the DBMS enforce referential integrity.
- Version-control schema changes with Flyway, Liquibase, or Alembic.
- Document every table — future you will thank you.
Frequently Asked Questions
What is the purpose of a schema in a database?
A schema defines the database structure — tables, columns, data types, relationships, constraints, and indexes. It is the blueprint that governs data integrity.
What is the difference between a schema and a database?
A database is the top-level container. A schema is a logical namespace within a database that groups related tables. In MySQL, the terms are interchangeable.
What is a logical schema vs physical schema?
Logical schema is the business view (tables, columns, relationships). Physical schema is the storage view (file layout, indexes, partitioning).
What is a star schema vs snowflake schema?
Star has a central fact table with denormalised dimensions. Snowflake normalises dimensions. Star is faster for queries; snowflake saves storage.
How do I create a schema in PostgreSQL?
CREATE SCHEMA my_schema; then CREATE TABLE my_schema.users (...);
What are the best practices for schema design?
Use correct data types, normalise first, use foreign keys, index lookup columns, avoid reserved words, document tables, version-control changes.
Fazlur Rahman
