Atomicity Is a Judgment Call, Not a Fact

Codd's normal forms never made assumptions about types, yet the type vocabulary available when they were formulated reflected the rather primitive sets found in common programming languages. PostgreSQL today offers something far richer, and that changes which column values can be treated as atomic.

The extreme case is textual data — char, varchar and text. At the simplest level these are byte arrays addressable by offset; under a multibyte encoding such as UTF-8 they become arrays of characters. PostgreSQL supplies enough operators to make working with them practical anyway.

Standard SQL features give you equal and unequal comparisons plus other scalar operations, with indices to accelerate them. PostgreSQL goes further:

That means the database starts to understand its data more.

  • Pattern matching (regular expressions, for example), accelerated by GIN indices over trigrams.
  • Fuzzy matching via trigrams or Levenshtein distance, answering questions in a more subtle manner than true/false.
  • Domains defined over an ordinary textual type, adding internal structure to it.
  • Full-text search, which turns a character array into information with a language, discernible items (email addresses, phone numbers, prices, words), and further structure: base forms and synonyms.

Other base types behave similarly. date, timestamp and interval are scalars underneath, but carry enough operators that their intricate, irregular internal structure can be processed naturally and accessed easily.

Then come the types PostgreSQL openly calls complex, which violate 1NF outright: geometric types, range types defined over scalars, and — historically — arrays, complex types, hstore, XML and JSON. Each has a rich set of operators, operator classes and index methods, so they can be processed on the database level without shuttling data back and forth to the application.

Opaque vs. Understood Structures

What ultimately matters is not whether fields are truly atomic, but whether they are simultaneously opaque — inaccessible from inside the database. An opaque structure is one whose content the kernel neither understands nor has specific means to process. Two archetypes:

  • A JPG image held in a bytea column. For the kernel it is just a string of bytes.
  • A CSV file held in a text column. The database can sort by text collation, reach words with string functions, even apply full-text search — but it has no idea the field holds a set of records with syntax and meaning.

XML and JSON are emphatically not opaque to PostgreSQL. Storing them as text wastes the server's capabilities for simplifying application code and for performance.

Extension or Replacement?

Two use cases have to be distinguished.

  • Extension. A complex type augments a relational model and is treated as an object through a well-defined interface — geometric and range types, and to some degree arrays, hstore, XML and JSON, the latter when regarded as documents with specific, distinguished properties. These fit the normalization rules well and do not significantly alter the relational model.
  • Replacement. A complex type stands in for a relational model, especially when arrays, hstore or JSON are used to build structures that could have been modeled as tables and columns. Relational principles break and 1NF cannot be applied. Whatever the rationale — and there are valid reasons — the decision must be conscious, because there is a price:
    • benefiting from the built-in optimiser becomes much harder, if not impossible
    • data manipulation is more tedious
    • the access language differs sharply from SQL (XPath for XML, jsonpath for JSON)
    • schema enforcement is much harder

A hybrid model combining both approaches is possible and well supported by PostgreSQL. Even where a formal method like normalization cannot be applied directly, its objective still holds: reduce data duplication and unintentional dependencies, and thereby reduce the opportunity for anomalies.

Where the Book Makes Life Harder

Consider modeling structures at the level of one or a few columns. When representing real-life objects, it helps to remember:

People do not have primary keys

Things that appear to have an intuitively obvious structure may not need that structure on the modeling side: it may be irrelevant to the application, or not generic enough to cover all actual and projected use cases. This is not speculation about future requirements — it concerns requirements the designer knows now but that may be masked by assumptions and preconceptions.

Typical manifestations:

  • introducing more structure than necessary (decomposing a whole further than needed)
  • imposing overly strict or unnecessary constraints, particularly on data format
  • limiting valid values with inflexible dictionaries or enumerations

These bite hardest when the stored values are more international than the designer expected.

Names, Addresses, Categories

  • Personal names and salutations. Decomposing into given name and family name is tempting; worse is a middle-name or initial field plus a dictionary of allowed salutations, plus length bounds and character restrictions (such as forbidding whitespace) on all of them. What about two given names and two surnames, as in Spain or Portugal? What about reversed order (last, first, as in Hungary)? What about a person for whom the concept of a surname makes no sense at all, as in Iceland?
  • Addresses. The same story, more complicated, since addresses depend even more on cultural and national factors and conventions. A model expected to hold mostly data from one location rarely implies exclusivity there; within a single location, assumptions about which components are requisite, which are allowed and in what order vary greatly. This is an area full of unstructured data, and forcing it into a rigid framework can backfire.
  • Dictionaries. Categories for people or places can be strongly personal, specific to a narrow cultural circle, or tied to nationality or language — titles, salutations, the type of place someone lives or works in, or the proverbial gender assignment limited to {M,F}.

Legitimate Reasons for Precision

All of these structures would in theory help find and model data, yet in practice they can make data less useful, more error-prone, and demanding of ever more elaborate validation and sanitation rules — ending in a less stable schema than anyone wants. Precise modeling is warranted in specific situations:

  • legal or compliance requirements dictating how data must be stored
  • very specific applications, where an actual business requirement expressed by data users and uses calls for decomposition (providing medical services, or processing full legal names)
  • compatibility, often backward, where exchanging data with third parties imposes the structure — localities with a local cadaster or land registry, or an airline where gender information really is mandatory and limited to {M,F}

None of these makes the data easier or more natural to use, however.

Balancing Normalization Against Practicalities

Benefits of a well-normalized schema must be weighed against project practicalities and the designer's ability to support the work; otherwise the result is unmaintainable, underperforming code and a bad experience for everyone involved. Answering how many users have a second name Bettina, or computing the distribution of family names by building floor, may be interesting but is often futile — unless the model is meant to support a national census.

Before applying normalization to a model:

  • ask why — what is the purpose of storing this data and how will it be processed? The answer often belongs not to the modeler but to the author of the actual use case.
  • accept that the modeler's personal experience and preconceived notions might be worthless in a broader context, particularly when modeling natural concepts other people have opinions about. This is not to undermine years of practice but to underline the value of a fresh look for every modeling task.
  • focus on the core business need: name the object and its properties honestly, describe their true purposes, and resist collecting as much detail as possible. Less is often more, yielding more maintainable data of better quality.
  • rather under-model than overdo relational structures. Store as little as necessary to achieve the business purpose, especially since PostgreSQL can hold semi-structured, non-relational data that can later be made relational if the need arises.

A hybrid data model encompassing both the relational and non-relational approaches is not only possible in PostgreSQL but well supported.