One model, four descriptions

Nothing is designed yet. These are sentences a store manager could have said, written precisely enough to build from.

1. A customer may be billed on many invoices.
2. Each invoice is billed to exactly one customer.
3. A customer must have a last name and a phone number.
4. No two customers may share a phone number.
5. An invoice must have a date and a total.
6. An invoice total may not be negative.
7. A customer with existing invoices may not be deleted.

Rules 1 and 2 together fix the relationship as one to many. Rules 3 through 7 become constraints once there is a database to put them in. Everything that follows is those seven sentences, restated at four depths.

Each group of users gets its own window onto the database. Nobody sees the whole thing.

A sales clerk needs to reach a customer. Accounting needs to reconcile totals and has no business seeing phone numbers. Both needs are met by defining views, the subschemas that sit above the shared design.

CREATE VIEW clerk_customer_lookup AS
SELECT customer_id, first_name, last_name, phone
FROM   customer;

CREATE VIEW accounting_invoice_summary AS
SELECT i.invoice_id, i.invoice_date, i.invoice_total, c.last_name
FROM   invoice i JOIN customer c ON i.customer_id = c.customer_id;

The clerk's application queries clerk_customer_lookup as though it were a table, and never learns that an invoice table exists at all.

What this buys you: logical independence

Add an email column to the customer table tomorrow and neither view breaks, because neither one asked for SELECT *. Changing the conceptual model does not force a change in the external models above it.

The entire database in one picture, described in the vocabulary of the business rather than of any particular DBMS.

This is the designer’s working drawing and the thing the whole organisation agrees on. It names entities, attributes and one relationship. It says nothing about which DBMS will hold the data, what the columns will be called, or how big a phone number field should be.

Rules 1 and 2 are visible here as the connectivity of the relationship. The remaining five rules have nowhere to live yet.

The same design, now expressed in the terms of a particular relational DBMS: tables, data types, keys, indexes. Still logical, still independent of the hardware underneath.

CREATE TABLE customer (
  customer_id  INT PRIMARY KEY,
  first_name   VARCHAR(30),
  last_name    VARCHAR(30) NOT NULL,
  phone        VARCHAR(15) NOT NULL UNIQUE);

CREATE TABLE invoice (
  invoice_id    INT PRIMARY KEY,
  customer_id   INT NOT NULL,
  invoice_date  DATE NOT NULL,
  invoice_total DECIMAL(9,2) NOT NULL CHECK (invoice_total >= 0),
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    ON DELETE RESTRICT);

CREATE INDEX idx_invoice_customer ON invoice(customer_id);

Where each business rule went

Every rule from the top of the page has a home in the schema.

Rule	Becomes
1, 2	The FOREIGN KEY on invoice.customer_id
3	NOT NULL on last_name and phone
4	UNIQUE on phone
5	NOT NULL on invoice_date and invoice_total
6	CHECK (invoice_total >= 0)
7	ON DELETE RESTRICT

The index is the giveaway that this is the internal model and not the conceptual one. An index has nothing to do with what the data means. Adding or dropping idx_invoice_customer changes no query’s result. It changes only how fast it comes back.

How the storage is actually laid out. This is the only level that depends on both the software and the hardware, and in an RDBMS you generally do not write it. The DBMS does.

The vocabulary differs by vendor. Working upward from the disk in Oracle’s terms:

Data block

The smallest unit the DBMS reads or writes, typically 8 KB. Holds a header plus as many rows as fit.

Extent

A run of contiguous blocks, allocated to a table in one go.

Segment

All the extents belonging to one table or index.

Tablespace

A logical container of segments, mapped onto one or more operating system data files.

SQL Server calls these pages, extents and filegroups; PostgreSQL calls them pages and relation files. The layering is the same everywhere.

Three consequences worth knowing

Row storage explains a choice you already made. A fifteen character phone number stored as CHAR(15) occupies fifteen bytes in every row. As VARCHAR(15) it occupies only what the value needs, so more rows fit in each block and a scan reads fewer blocks. That is why the schema above uses VARCHAR.

The index exists physically as a B-tree, a shallow tree of blocks whose leaves hold key values and row addresses. Finding one customer’s invoices costs three or four block reads instead of a scan of the whole table. That is the entire reason idx_invoice_customer was created one level up.

Recently used blocks stay in memory. The DBMS keeps a buffer cache, so a repeated query often touches no disk at all.

What this buys you: physical independence

Move the tablespace to a faster drive, change the block size, or partition the invoice table by year, and the SELECT statements in the application do not change by a single character. The database administrator tunes down here; the conceptual model above stays exactly as drawn.