What is a Database Schema?

A database schema defines the logical and structural layout of a database. It describes how data is organized into tables, the fields (or columns) within those tables, the relationships between them, and the rules that govern the data. Schemas are foundational to relational databases, which are built around structured data and governed by the relational model first introduced by Edgar F. Codd in 1970 while working at IBM.

In relational databases like PostgreSQL, MySQL, SQL Server, Oracle, and StarRocks, the schema acts as a contract between the data and its consumers. It specifies what kind of data can exist, how it can relate to other data, and how it can be queried and enforced.

Why are Database Schemas Important?

Database schemas are essential because they enforce order, consistency, and structure. In production environments, where dozens of applications and teams interact with the same data, a schema prevents chaos. Let’s break down their value:

1. Data Integrity and Accuracy

• Schemas enforce rules: primary keys, foreign keys, unique constraints, and data types.

• For example, in PostgreSQL or MySQL, defining email VARCHAR(255) NOT NULL UNIQUE ensures each record has a unique, valid email.

2. Performance

• Schemas enable indexing, which speeds up lookups and filtering.

• Query optimizers (like the Cost-Based Optimizer in StarRocks, PostgreSQL, or Oracle) rely on schema metadata to generate efficient execution plans.

3. Maintenance and Collaboration

• A clear schema allows developers, data engineers, and analysts to collaborate effectively.

• Schema versioning tools like Liquibase, Flyway, or schema registries in data lakes help track changes safely.

4. Future-Proofing

• A well-designed schema can evolve with business requirements by supporting extensibility without rework.

Components of a Database Schema

Tables

Tables are the core data containers. Each row represents an entity instance (e.g., a specific customer), and each column represents an attribute (e.g., email address).

Example: A customers table may contain:

In StarRocks, tables can be defined as row-based or columnar, depending on workload (OLTP vs. OLAP).

Fields (Columns)

Each field is defined by:

• Name

• Data type (INT, VARCHAR, DATE, BOOLEAN, etc.)

• Constraints (NOT NULL, UNIQUE, etc.)

Fields serve as the database’s first line of defense against bad data.

In PostgreSQL, a field like created_at TIMESTAMP DEFAULT now() ensures time consistency.

Relationships

Relationships define how tables are connected:

• One-to-One: A user has one profile.

• One-to-Many: One customer can place many orders.

• Many-to-Many: Students enroll in multiple courses, often modeled with a join table like enrollments.

These are defined using foreign key constraints and can be enforced or virtualized depending on the engine (StarRocks supports JOINs but does not enforce foreign keys at the storage layer, unlike PostgreSQL).

Schema vs. Instance

Schema:

• The definition of structure (tables, fields, relationships).

• Defined in DDL (Data Definition Language) SQL like CREATE TABLE.

Instance:

• The current snapshot of data stored in the database at a moment in time.

Use Case: In an online store, the schema defines tables like orders, products, and users. The instance is the actual orders and users at 3 PM on April 15, 2025.

Types of Database Schemas

1. Physical Schema

Describes how data is stored on disk — file formats, partitioning, storage engines, compression, indexes.

• In PostgreSQL, this includes tablespaces and physical indexes (e.g., B-tree).

• In StarRocks, physical layout includes columnar storage, vectorized execution, and primary key indexing.

• In data lakes like Apache Iceberg or Delta Lake, the physical schema controls how Parquet or ORC files are laid out and partitioned.

Example: In Apache Iceberg, data is stored in partitioned Parquet files. The schema includes metadata about data types, nullability, and field IDs for schema evolution.

2. Logical Schema

Defines how users see the data — table definitions, field types, relationships, constraints. It abstracts away physical storage.

Example:

• Table: transactions with transaction_id, user_id, amount, timestamp

• Foreign key: user_id references users(user_id)

Use Case: In banking, the logical schema defines relationships among accounts, transactions, and users for anti-fraud systems.

3. View Schema

A view is a virtual table based on a SELECT query. It doesn’t store data but presents it as if it were a table.

• Used for access control, abstraction, or simplified querying.

• Often materialized in OLAP systems like Snowflake, BigQuery, or StarRocks as materialized views for performance.

Real Example:

• A healthcare system may expose a view that shows anonymized patient data (patient_id, diagnosis) to researchers but hides PII like names or birthdates.

Designing a Database Schema

Designing a schema is one of the most important steps when building a database system. A well-designed schema can save months of engineering time down the road — reducing bugs, improving performance, and making your data easier to work with.

Let’s walk through key design principles and common trade-offs in practice.

1. Start with the Entities and Relationships

Before writing any SQL, you should understand the domain. Ask:

• What are the core entities in this system?

• How do they relate to each other?

In an e-commerce platform, for example:

• You’ll likely have Customers, Products, Orders, and OrderItems.

• Each Order belongs to one Customer, and each Order contains many OrderItems.

This kind of basic mapping — sometimes done with Entity-Relationship (ER) diagrams — helps ensure you’re capturing the real-world model accurately.

2. Normalization: Eliminate Redundancy

Normalization means breaking data into smaller related tables so that:

• Each table represents a single concept (e.g., a customer, not a customer + order)

• Each fact is stored in only one place

This makes updates safer and keeps the data clean. For example:

• Instead of storing the customer’s email in every order, you store it once in the Customers table and reference it via CustomerID in Orders.

This is especially important in systems that update or write data frequently — like banking platforms, inventory systems, or CRM software.

You’ll typically go through a few stages of normalization:

• 1NF ensures each field has atomic values

• 2NF separates repeating groups

• 3NF removes indirect dependencies (columns depending on other non-key columns)

3. When to Denormalize

There are cases where joining multiple normalized tables becomes a performance bottleneck — especially in analytical systems where queries run over billions of rows.

Denormalization means reintroducing some redundancy to make read queries faster.

This is common in OLAP databases like StarRocks, ClickHouse, or Snowflake, where:

• You might pre-join dimension tables into a single wide table (fact table)

• Or create materialized views for frequent aggregations (e.g., daily sales by region)

Real example: In the analytics backend at Demandbase, they moved from ClickHouse to StarRocks and eliminated manual denormalization because StarRocks could handle multi-table joins efficiently in real time — even with millions of rows arriving daily.

Denormalization is also common in recommendation systems, dashboards, and reporting layers where query speed is more important than update frequency.

4. Plan for Schema Evolution

Your schema won’t stay static. New product features, compliance rules, or integrations will require you to:

• Add or rename columns

• Add new tables or relationships

• Deprecate old fields

Modern systems like Apache Iceberg, Delta Lake, and StarRocks support schema evolution — allowing changes without needing to rewrite data.

To prepare for evolution:

• Avoid overly rigid designs (e.g., don’t bake dozens of product options into one table)

• Use nullable fields where optional data may be added later

• Document column meanings clearly to avoid misuse as the schema grows

5. Indexing and Constraints

After tables are designed, you need to think about:

• Primary keys (to uniquely identify rows)

• Foreign keys (to connect related tables — even if not enforced in OLAP)

• Indexes (to speed up filtering and sorting)

For transactional systems, indexes must be chosen carefully — too many can slow down writes. In analytical systems like StarRocks, zone maps and column pruning make queries efficient even without user-defined indexes.

Practical Applications of Database Schemas

Let’s ground this in real-world systems where schema design plays a critical role.

SaaS / Infrastructure: Airtable’s LSDA Project

Use Case: As part of its LSDA (Live Shard Data Archive) project, Airtable needed to validate over 1PB of historical data—trillions of records—against its live transactional state. The goal was to ensure data integrity across archived Apache Iceberg tables on S3 and real-time streams from Kafka.

Schema Design Approach:

• Used normalized schemas for both historical and live datasets (e.g., record_id, table_id).

• Queried Iceberg and Kafka data directly in StarRocks via external catalogs.

• Avoided data duplication by joining across live and archived data on the fly.

Outcomes:

• StarRocks enabled millisecond-scale JOINs at petabyte scale—no denormalization or ETL required.

• Delivered high-throughput validation via SQL over federated data sources.

• Leveraged primary key indexing and vectorized execution for performance.

This architecture let Airtable validate massive datasets in place, with full schema control and minimal operational overhead.

Finance: TRM Labs' Migration from BigQuery to Lakehouse

Use case: TRM Labs, a provider of blockchain intelligence tools for financial crime investigation, migrated from a BigQuery-based architecture to a self-managed lakehouse stack using Apache Iceberg and StarRocks to meet growing demands around performance, cost, and deployment flexibility.

Their analytics platform processes petabytes of blockchain data across 30+ chains, serving over 500 real-time queries per second. BigQuery’s scalability was no longer cost-effective, and Postgres could not keep up with the ingestion rate. The team needed a high-performance, open solution that supported flexible schema design and real-time response.

Schema Design Approach:

• Stored raw blockchain and entity data in partitioned Apache Iceberg tables on object storage.

• Modeled normalized fact and dimension tables (e.g., transactions, wallets, labels) for analytical workflows.

• Served query workloads directly from StarRocks via external Iceberg catalogs, avoiding data duplication.

Outcomes:

• StarRocks handled low-latency lookups and JOINs across Iceberg tables — without needing to precompute views.

• Enabled real-time insights on high-cardinality, multi-chain data models with SQL-based schema evolution and high concurrency.

• Replaced proprietary and costly BigQuery workloads with an open, scalable lakehouse design.

The combination of Iceberg for scalable, versioned storage and StarRocks for fast, schema-aware OLAP allowed TRM to consolidate their architecture while delivering petabyte-scale analytics with sub-second performance.

E-commerce: Shopee’s Real-Time Analytics with StarRocks

Use case: Shopee, one of Southeast Asia’s largest e-commerce platforms, adopted StarRocks across several internal analytics systems — including Data Service, Data Go, and Data Studio — to overcome performance bottlenecks with Presto-on-Hive for JOIN-heavy and high-concurrency queries.

Rather than migrating data, Shopee leveraged StarRocks’ ability to query external Hive tables directly and used materialized views to accelerate key workloads.

Schema Design Approach:

• Registered external Hive tables in StarRocks via built-in catalogs.

• Built materialized views on top of external tables to precompute complex aggregations.

• Retained normalized schemas with no need for denormalization, thanks to StarRocks’ efficient JOIN engine and cost-based optimizer.

Outcomes:

• Data Service APIs ran 10× to 2000× faster after replacing Presto queries with MV-backed StarRocks queries — without changing the API layer or ingestion pipelines.

• Data Go saw 3–10× faster JOIN queries and 60% lower CPU usage, even with multiple daily snapshot tables.

• Data Studio ad-hoc queries ran 2–3× faster on the same hardware footprint, benefiting from StarRocks’ vectorized execution and parallel processing.

By keeping data in place and relying on StarRocks’ SQL capabilities, Shopee achieved major performance and cost gains without redesigning its data lake architecture.

Digital Advertising: Pinterest's Anti-Spam Analytics

Use case: Pinterest operates a large-scale advertising platform where marketers use real-time dashboards like Partner Insights to track and optimize ad performance. Each advertiser can define customized metrics across multiple dimensions — such as audience, platform, and time — making schema flexibility and performance essential.