Physical Design: Fields, Data Types, Denormalization & Partitioning

From logical to physical

Logical design ended with a set of normalized relations — clean, anomaly-free, organization-wide. Physical design asks a different question: *given a real DBMS, real data volumes, and real access patterns, how do we store this so it performs?* The logical model says what data exist; the physical model decides how they sit on disk and how fast they come back.

The major inputs to physical design are the normalized relations plus volume estimates (rows per table), attribute definitions with maximum field lengths, descriptions of how/where/when data are used (entry, retrieval, update frequencies), and the non-functional requirements: response time, security, backup, recovery, retention — all filtered through the chosen DBMS's capabilities.

Designing a field

A field is the smallest unit of named application data — the physical realization of a logical attribute. For every attribute you choose a data type, balancing four objectives that routinely conflict:

1. Represent all possible values the attribute can take.
2. Improve data integrity by restricting to valid values.
3. Support all required manipulations (date arithmetic on dates, SUM/AVG on numbers, string ops on text).
4. Minimize storage space.

Objective 3 is the subtle one: store a birth date as VARCHAR and you *cannot* compute an age; store a dollar amount as text and SUM is impossible. Pick the type for how the data are used, not how they look.

Then attach integrity controls to the field:

- Default value — assigned automatically when none is given (cuts entry errors). - Range control — limit to a numeric bound or value set (a CHECK constraint). - Null control — prohibit empty values where a value must always exist (every primary key requires this).

Worked example: a field specification

Take the registrar's credits attribute on courses. A faithful physical field spec, expressed as Postgres DDL on a practice copy:

credits   INTEGER  NOT NULL  DEFAULT 3
          CHECK (credits BETWEEN 1 AND 6)

Every objective is visible: INTEGER represents and lets us aggregate credit loads (objectives 1 + 3), the CHECK is a range control improving integrity (objective 2), NOT NULL is a null control, and DEFAULT 3 is a default value — and INTEGER over VARCHAR(2) also wins on storage (objective 4).

Denormalization — a deliberate, last-resort trade

Fully normalized schemas create many tables, and a frequently-run query that needs columns from several of them pays a join cost every time. Denormalization transforms normalized relations into nonnormalized physical record specifications to speed retrieval — by *combining* tables (to avoid a join) or *partitioning* one. It is a conscious trade: you buy read speed with redundancy, reintroduced anomalies, harder maintenance, and more storage.

The sequence matters: normalize first, then selectively denormalize. Normalize to get the clean, anomaly-free structure and to *see* the dependencies; denormalize only where a measured performance need justifies the cost — and only after indexing, clustering, and query tuning have been tried. Three classic opportunities: a 1:1 relationship that almost always matches (merge the two records); an associative entity whose three-table join is hot (fold one side's attributes in); and small reference data on the *one* side of a 1:M with few children (copy it into the *many* side).

Partitioning — splitting one relation into several physical tables

- Horizontal partitioning distributes the rows of a relation into separate physical tables by a column value — each partition has the *same columns*. Customers by region, orders by year. A query that targets one partition never scans the others. - Vertical partitioning distributes the columns into separate tables, repeating the primary key in each so the rows can be rejoined. Accounting fields in one table, engineering fields in another, so each group reads only what it needs.

Both add query complexity whenever you must recombine partitions, and that recombination cost is the price of the access-pattern speedup.