Schema Design and Comparison

Section 4: Database Platforms for Migration. Lesson 21.

What is a schema?

A database schema is the blueprint of a database. Just as a building blueprint defines the rooms, doors and connections, a schema defines how data is structured, how elements relate to one another, and therefore how migration processes must operate.

A schema covers:

• Tables and their columns
• Data types for each column
• Primary keys and foreign keys
• Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT)
• Indexes
• Views, stored procedures, functions and triggers

Data cannot simply be copied from one database to another without first understanding these structures. Without understanding the schema, you cannot set up a database correctly or make it work, so reviewing schema design is one of the most important activities in a migration.

Note that "schema" has two meanings. It is the overall design of a database, and in Oracle, SQL Server and PostgreSQL it is also a named container (a namespace) for objects, such as sales.customers.

Example: a customer schema

CREATE TABLE customers (
  customer_id  INT           PRIMARY KEY,
  name         VARCHAR(100)  NOT NULL,
  gender       VARCHAR(20),
  email        VARCHAR(255)  UNIQUE
);

CREATE TABLE orders (
  order_id     INT            PRIMARY KEY,
  customer_id  INT            NOT NULL REFERENCES customers(customer_id),
  order_date   DATE           NOT NULL,
  sales        DECIMAL(12,2)  NOT NULL,
  profit       DECIMAL(12,2)             -- negative = loss
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

This follows the lecture's Customer design (customer ID, name, gender, sales, profit or loss), but moves sales and profit into an orders table, since they belong to each order rather than to the customer.

Relationships

A relationship links rows in one table to rows in another through a key. A customer places orders, so each order carries the customer's customer_id as a foreign key. Searching for "the orders for Alex" works in two steps: find Alex's customer_id, then fetch every order with that ID.

SELECT o.order_id, o.order_date, o.sales
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.name = 'Alex';

• One-to-many: one customer, many orders (the most common)
• One-to-one: one user, one profile
• Many-to-many: students and courses, stored through a junction table such as enrolments

Relationships are why migration order matters. Load customers before orders, or temporarily disable foreign keys and validate afterwards, otherwise orders point at customers that don't exist yet.

Exploring real schemas

drawSQL (drawsql.app) is a web tool for drawing and browsing entity-relationship diagrams, with a gallery of real-world schema templates. The lecture opens the Laravel Spark (Classic) template. Spark is a Laravel package by Taylor Otwell for scaffolding SaaS applications. At the time of the lecture the template showed 12 tables, 134 columns and 12 relationships, covering users, teams, subscriptions, invoices, announcements and notifications.

Its users table, for example, holds id, name, email, email_verified_at, password and more. Level of detail like this is what makes a database well structured. Other ways to visualise a schema include dbdiagram.io, MySQL Workbench, SSMS database diagrams, DBeaver and pgAdmin.

Schema comparison

Schema comparison is the process of analysing and comparing the structures of the source and target databases before migrating any data. It reviews tables, columns, data types, relationships, constraints and indexes to find the differences and define the migration mappings.

Typical gaps it finds:

• Different table names (cust versus customers)
• Different column names (cust_nm versus full_name)
• Different data types or lengths (VARCHAR2(50) versus NVARCHAR(100))
• Missing or different constraints (nullable in source, NOT NULL in target)
• Different indexes and keys
• Different relationships and structure

The comparison then decides the transformation. Data may be transformed, split across several tables, merged into one, or reorganised for new business requirements.

Example source-to-target mapping

Comparing with SQL

-- Columns in the source that are missing, or typed differently, in the target
-- (both schemas reachable from one database, e.g. after staging a copy)
SELECT s.table_name, s.column_name, s.data_type AS source_type, t.data_type AS target_type
FROM information_schema.columns s
LEFT JOIN information_schema.columns t
  ON  t.table_schema = 'target'
  AND t.table_name   = s.table_name
  AND t.column_name  = s.column_name
WHERE s.table_schema = 'source'
  AND (t.column_name IS NULL OR t.data_type <> s.data_type)
ORDER BY s.table_name, s.column_name;

Tools that compare schemas automatically: SQL Server Data Tools and Redgate SQL Compare (SQL Server), MySQL Workbench's schema synchronisation, the dbForge Schema Compare family, Liquibase diff, migra (PostgreSQL), and the assessment reports from AWS SCT and Ora2Pg for cross-engine moves.

Why it matters

• Ensures compatibility between source and target
• Preserves data integrity and relationships
• Supports accurate transformations and mappings
• Improves data quality and reduces migration risk

Try it: Legacy CRM Schema Diff

An example comparison between a legacy Oracle CRM (CUST, ORD, ORD_HIST) and its PostgreSQL redesign (customers, addresses, orders), with 14 column mappings. Filter by kind of difference (match, renamed, retyped, split, moved, merged, new, dropped), then select a row to see why it differs and the SQL transformation it needs, from splitting CUST_NM into first and last names to merging ORD_HIST into orders.

Key takeaways

• A schema is the database's blueprint: tables, columns, types, keys, constraints, indexes, views and procedures.
• Relationships link tables through keys, such as customer_id on orders, so load order and integrity matter.
• Schema comparison finds the gaps between source and target and produces the mapping.
• Mappings drive transformations: split, merge, rename, retype, reorganise.

Next: 22. Data Types and Compatibility Issues.