Databases, content and information architecture

Two traditions meet here, and they meet awkwardly. One comes from database engineering, where correctness is defined by a formal model and tested against it. The other comes from content work, where the test is whether a person finds the thing they came for. A search box is where the two collide. The retrieval model decides what comes back, and the labels decide whether a sensible question is asked at all.

The claim that organises the material is that the shape of stored data decides which questions are cheap to ask, and a label decides whether the question gets asked.

Deciding what a record is decides what the product can do

Codd argued in 1970 that users of large data banks should be protected from having to know how data is organised inside the machine (Codd, 1970). His model described data as relations, defined a normal form for database relations, and introduced the idea of a universal data sublanguage. The stated aim was that changes in internal representation, and even some changes in external representation, should not disturb application programs.

That is an argument about maintenance as much as about storage. Take a worked case: a university records system that keeps a student's enrolled modules as a comma-separated string in the student row. It answers one question well, which is what this student is taking. It cannot answer who teaches a given module without reading every row and parsing text. A separate enrolment record, with a key to the student and a key to the module, answers both.

Entity relationship design is the discipline of naming those records before any storage code exists. What counts as a thing determines what can later be counted, joined and constrained.

Normalisation, and the honest case for denormalising

Normalisation removes redundancy that lets one fact be recorded twice and then disagree with itself. A relation is in first normal form when each attribute holds a single value. Second normal form removes dependencies on part of a composite key. Third normal form and Boyce-Codd normal form remove dependencies between attributes that are not keys.

The worked case that carries this section is an order line. If a customer's delivery address is copied into every order line, then an address correction either misses old orders or rewrites history. Keep the address once, in its own record, and the anomaly disappears. Elmasri and Navathe develop this at book length, with the functional dependencies behind each normal form (Elmasri and Navathe, 2016).

Denormalising is sometimes right, and the defensible version of that claim is narrow. It is right when a read path has been measured as too slow, one write path controls the duplicated copies, and the team accepts the reconciliation work. It is not right because joins feel slow to a designer who has not measured them. Whether denormalisation is a legitimate design decision or a form of debt is one of the live arguments in the field, and measurement usually decides it.

SQL is a question, and the plan is what it costs

SQL states a question. Project the columns you need, filter rows, join related tables, group the result and aggregate it. Each clause corresponds to an operation on sets, so the same requirement can often be written in several ways.

The query plan is the engine's account of what it will actually do. It is the artefact a designer should read. Indexes are the main lever on the plan. An index on orders(customer_id) replaces a scan of the orders table with a lookup into a sorted structure. It also consumes space and slows every write that has to maintain it.

Worked case: a support tool that lists a customer's twenty most recent orders. Without an index on the customer column, the plan shows a full scan for each customer, and the page is slow once the table is large. With the index, the plan shows an index range scan followed by a sort of about twenty rows. The evidence is the plan and the timing, not a preference for indexes.

Transactions, isolation, and the price of correctness

A transaction groups actions that must move the database from one consistent state to another. Isolation is the promise about what one running transaction may see of another. The SQL standard names four isolation levels and describes them through three phenomena: the dirty read, the non-repeatable read and the phantom.

Berenson and colleagues showed that this account is weaker than it looks (Berenson et al., 1995). The phenomena are ambiguous, and even their broadest reading does not exclude anomalous behaviour. Lock-based implementations also behave differently from the level they claim. The authors then define snapshot isolation, a multi-version scheme that avoids all three standard anomalies and is still not serializable. A store can be free of every anomaly you were taught to look for and still permit an outcome no serial order of the transactions could produce.

Worked case: two people buy the last seat at the same moment. Both read one seat remaining, and both write a purchase. The isolation level, not the application logic, decides whether that is possible.

Where the relational store runs out

A key-value store offers lookup by one key and little else. That sounds impoverished until you examine the access pattern. The authors of Dynamo describe Amazon services where a relational database would limit scale and availability, among them shopping carts, session management and sales rank (DeCandia et al., 2007). Dynamo therefore exposes a primary-key-only interface. To stay available it gives up consistency under some failure scenarios, and it resolves conflicts with object versioning and application-assisted reconciliation.

A document store sits between the two models. It stores a whole document as the unit of retrieval, which suits any screen that reads and writes one thing at a time. The cost appears when the same fact belongs to many documents, because embedding duplicates it and referencing it reintroduces the joins the model was meant to avoid. Kleppmann surveys these trade-offs, and the storage engines beneath them, for readers who have to choose rather than recite (Kleppmann, 2017).

Worked case: a shopping basket. One key addresses the basket, it is read and written whole, and it changes many times in a session. It suits a key-value store. A quarterly report on which products are abandoned together does not, and it usually runs somewhere else.

An inverted index turns a scan into a lookup

Keyword search does not read documents one at a time. It consults an inverted index: a dictionary of terms, and for each term a postings list recording the documents that contain it (Manning, Raghavan and Schutze, 2008).

Matching a term is only the start. Rare terms discriminate between documents and common terms do not, so the ranking has to account for how informative each term is. The tf-idf weight multiplies how often a term occurs in a document by a function of how many documents contain it. A term that occurs in nearly every document contributes almost nothing. Documents and queries are then represented as vectors and ranked by cosine similarity, which compares their direction rather than their length, so a long page that mentions a term in passing does not outrank a short page about it.

Worked case: a recipe site where a search for chicken salad returns a long article on poultry farming first. Weighting the rare terms and normalising for length moves the recipe above the article without editing it.

Measuring relevance, and the question that will not settle

Recall is the fraction of the relevant items that were retrieved. Precision is the fraction of retrieved items that are relevant. The two trade off, because returning everything gives perfect recall and useless precision. The F measure combines them as a weighted harmonic mean. For ranked results, a common summary is mean average precision across a set of queries (Manning, Raghavan and Schutze, 2008).

The contested question in this area is not which formula to use. It is whether relevance can be measured in a way that settles an argument at all. Relevance is a judgement a person makes about a document at a moment, not a property the document carries. Judgements vary between assessors. The standard method of building a test collection, called pooling, asks assessors to judge only the documents that the systems under test already returned. A new approach that finds something none of them found cannot be credited for it.

Worked case: a retail search team judges fifty queries and reports precision at the top ten. The honest report states who judged, how the pool was assembled, and that recall was not measurable for the queries where the full set of relevant items was never enumerated.

Content models, metadata and controlled vocabularies

Content modelling decides what a piece of content is, which fields it carries, and which fields it shares with other types. Structured content keeps meaning separate from presentation, so one field, such as an event date, is reused in a listing, a detail page and an automated reminder. Metadata is the data about the content. A controlled vocabulary restricts the values a field may take, a taxonomy arranges those values into a hierarchy, and a thesaurus records relationships between terms. That restriction is what makes faceted filtering work, because a filter needs a finite set of values to offer.

Hearst compared the two ways a search interface can group its results (Hearst, 2006). Clustering groups items by computed similarity. It is automatic and works on any collection, but its labels are unpredictable and its groups are inconsistent. In her worked example, a clustering engine's groups for a recipe query mixed levels, listing Salad, Crockpot and Italian side by side, and group members did not match their labels. Hierarchical faceted categories must be designed in advance, which costs human effort, but the usability studies she reviews found that participants preferred them.

Worked case: a recipe collection faceted by dish type, main ingredient, cooking method and cuisine. Each facet is a small vocabulary, each recipe is assigned terms from each, and a combination of filters narrows the set without the user typing a query.

Labelling, wayfinding, and a site you did not build

Rosenfeld, Morville and Arango treat information architecture as the structural design of shared information environments. They give most of their attention to organisation, labelling and navigation, with search systems alongside them (Rosenfeld, Morville and Arango, 2015). URL structure and breadcrumbs belong to the same argument. They make a page's location legible, which is what lets someone predict where a neighbouring page lives.

Two methods carry the evidence. Card sorting asks people to group items, after which the analyst looks for the structure they share. Dougalis and Katsanos report a closed card sort for a retail site in which 191 participants sorted 45 cards into 7 predefined categories (Dougalis and Katsanos, 2025). Their resampling analysis found that 8 to 12 participants gave reliable closed card sort data, and that more were costly without being more informative. This is one case study of one site, so treat the figure as a starting point rather than a rule.

Tree testing asks people to find items in a text-only hierarchy, which tests the labels without the distraction of visual design. Kuric, Demcak and Krajcovic compared three tree testing variants with three high-fidelity prototypes across 180 participants and 1800 task completions (Kuric, Demcak and Krajcovic, 2025). They found significant differences between the variants and in how closely each predicted navigation in a real interface. The variant that displayed earlier navigation choices, and allowed them to be reversed, was the most accurate. Evidence of this kind is recent and thin, and a single round of testing should not be treated as decisive.

The contested question here is whose structure the taxonomy should record. An organisation tends to sort content by its own departments, and the people using the site sort by the task they came to perform. The evidence leans towards the user's model, and the two methods above are how a team finds out which is which. What makes an architecture durable is that the next person can extend it. The labels still mean what they meant, and a new page has an obvious place to go.

Once the structure is settled, the questions that remain are answered with words rather than counts, and the next step is to make sense of what people said and did while using it.