A crash course on the 13 diagram types in Chapter 5 of Object-Oriented Analysis and Design with Applications, each drawn for the same web shop so you can see how the views fit together.
UML is a standard visual language for describing software: what it is made of and how it behaves. Booch, Rumbaugh and Jacobson merged their competing notations into UML in the late 1990s. The book uses UML 2.0, which defines 13 diagram types.
A model is a simplification of a system built for a purpose. A diagram is one view into that model. No diagram shows everything, and no project needs all 13. You pick the view that answers the question in front of you.
Every diagram in this course describes ShopFast, an online store with a React front end, a Node API, PostgreSQL, and Stripe for payments. Seeing one system from 13 angles is the fastest way to understand what each angle is for.
{ordered}, {total > 0}.Booch frames UML around views of an architecture. The logical view (classes, packages) shows the design. The process view (activity, sequence, state) shows runtime behavior. The implementation view (components) shows how code is organized, and the deployment view (nodes) shows where it runs. Use cases tie the four together as the scenarios every view must support.
A package diagram shows how a large model is split into groups and which groups depend on which. It answers one question: how is this system organized, and are the dependencies sane?
Ordering::Cart means the Cart element inside the Ordering package.+ public and - private, as with class members.@shop/ui and @shop/domain. Lint rules such as import boundaries are a package diagram enforced in CI.A component diagram shows the replaceable building blocks of a system and the interfaces each one offers and needs. A package groups design elements; a component is a unit of implementation you could swap out without touching its neighbours.
IPayment and IOrderRepo contracts. Replace PaymentAdapter to move from Stripe to PayPal and nothing else changes.Use component diagrams to plan service boundaries, plugin points or micro-frontends, and to agree on contracts between teams before code exists. Show components as black boxes (interfaces only) for consumers, or as white boxes (inner parts visible) for the team that owns them.
A deployment diagram shows the physical runtime: which machines or environments exist, what software runs on each, and how they talk. It is the only UML diagram about infrastructure.
bundle.js, api.zip, a Docker image.Specification vs instance level. A specification-level diagram says “a Lambda node and a Postgres node.” An instance-level diagram names real machines, such as prod-db-1 : PostgreSQL, underlined like objects. Use instance level in runbooks and incident write-ups.
A use case diagram shows who uses the system and what goals they reach with it. It captures requirements from the outside, with no internal design at all.
Booch stresses that the diagram alone is thin. Each use case needs a written specification: actors, preconditions, a numbered main success scenario, alternate flows, and postconditions. For Place order:
Alternate flow 4a: the card is declined. The system shows the reason and returns to step 3.
An activity diagram shows a flow of work: steps, decisions, parallel branches, and who does each step. Think of a flowchart with real semantics for concurrency. It suits business processes and the scenario inside a use case.
[in stock].Order [paid].The semantics come from Petri nets. Picture a token travelling along the arrows: a fork copies the token, and a join waits until every copy has arrived.
The class diagram is the heart of object-oriented design. It shows classes, their attributes and operations, and the relationships between them. If you learn one diagram deeply, learn this one.
Three compartments: name, attributes, operations. Hide a compartment when it doesn’t help the reader.
visibility name : Type [mult] = default, for example - total : Money = 0visibility name(param : Type) : Return, for example + addItem(p : Product) : void+ public, - private, # protected, ~ package.| Relationship | Drawn as | Meaning | In Figure 5-7 |
|---|---|---|---|
| Association | Solid line, optional arrow for navigability | Objects know about each other | Customer places Order |
| Aggregation | Hollow diamond at the whole | Has-a; parts can outlive the whole | (Category has Products) |
| Composition | Filled diamond at the whole | Owns; parts die with the whole | Order contains OrderLines |
| Generalization | Hollow triangle at the parent | Is-a (inheritance) | PhysicalProduct is a Product |
| Realization | Dashed line, hollow triangle | Implements an interface | StripeGateway implements PaymentGateway |
| Dependency | Dashed open arrow | Uses temporarily | Order uses PaymentGateway in pay() |
Multiplicity sits at each end of an association: 1, 0..1, * (the same as 0..*), 1..*. Read Figure 5-7 out loud: a customer places zero or more orders; an order is composed of one or more lines; each line refers to exactly one product.
Each UML relationship has a direct code shape.
interface PaymentGateway { // «interface»
charge(amount: Money): Promise<PaymentResult>;
}
class StripeGateway implements PaymentGateway { /* … */ } // realization
abstract class Product { // italic name = abstract
constructor(private sku: string, private price: Money) {}
}
class PhysicalProduct extends Product { weightKg = 0; } // generalization
class Order {
private lines: OrderLine[] = []; // composition, 1..*
private status: OrderStatus = "PENDING";
async pay(gateway: PaymentGateway) { // dependency: parameter only
return gateway.charge(this.total());
}
}Booch distinguishes conceptual class diagrams (domain ideas, no types) from specification diagrams (real types and operations). Start conceptual with stakeholders and add detail as the design firms up.
A sequence diagram shows one scenario as messages passed between participants, with time flowing down the page. After class diagrams, it is the diagram you will draw most.
name : Type (or : Type when anonymous) with a dashed line running down.alt, opt, loop, par, ref.alt frame holds the two possible outcomes.| Operator | Meaning | Code equivalent |
|---|---|---|
alt | Alternatives, each with a guard | if / else |
opt | Runs only when the guard is true | if without else |
loop | Repeats while the guard holds | for, while |
par | Regions run in parallel | Promise.all |
ref | Points to another sequence diagram | a function call |
break | Runs, then leaves the enclosing fragment | early return |
critical | An atomic region | a lock or transaction |
The handler that Figure 5-8 describes. Every arrow is a line you can mock or assert in a test.
async function payForOrder(id: string) { // OrderService
const order = await orderRepo.findById(id); // 3, 4
const result = await stripe.charge(order.total()); // 5, 6
if (result.ok) { // alt [payment succeeded]
await orderRepo.save(order.markPaid()); // 7
return { status: 200 }; // 8
} else { // [card declined]
return { status: 402 }; // 9
}
}ref.An interaction overview diagram is an activity diagram whose nodes are whole interactions, usually sequence diagrams. It shows the big-picture control flow while each box holds the detailed conversation.
ref that points to a sequence diagram drawn elsewhere.sd.ref box is a separate sequence diagram; the one on the right is drawn inline.Use it when a scenario has so many steps and branches that a single sequence diagram becomes unreadable. The difference from a plain activity diagram: every node is a conversation between objects, not a single action.
A composite structure diagram looks inside one class or component and shows the parts it is built from, how they are wired, and the ports it uses to reach the outside. A class diagram describes types in general; this diagram shows how parts collaborate in one specific context.
role : Type [mult]. Solid border means owned; dashed means a reference to something owned elsewhere.http port and payments leave through the payments port.You’ll mostly meet this diagram in architecture documents and systems engineering. A React analogy helps: a page component built from child components, with props as ports and the data passed between children as connectors.
A state machine diagram shows the lifecycle of one object: the states it can be in, the events that move it between them, and what happens on each move. Booch gives it a lot of space because objects with interesting state are where most bugs hide.
event [guard] / action. All three parts are optional.entry / on arriving, exit / on leaving, do / while inside.(H)refund first: nothing happens, because Pending has no such transition.A state machine makes illegal states visible. Ask of every state which events are allowed and what happens to the others. Every missing answer is a bug report waiting to be filed: can a cancelled order be paid?
The same machine as a reducer. Unhandled events return the state unchanged.
type State = "Pending" | "Paid" | "Picking" | "Packed" | "Shipped"
| "Delivered" | "Cancelled" | "Refunded";
function next(state: State, event: string): State {
switch (state) {
case "Pending":
if (event === "payment_succeeded") { sendReceipt(); return "Paid"; }
if (event === "cancel" || event === "timeout") return "Cancelled";
return state;
case "Paid":
if (event === "fulfil") return "Picking"; // enters the composite
if (event === "refund" && withinDays(14)) return "Refunded";
return state;
// … Picking → Packed → Shipped → Delivered
default:
return state;
}
}loading && error.A timing diagram shows how the states of one or more objects change along a precise time axis, and the timing constraints between those changes. Time runs left to right, unlike sequence diagrams.
{< 10 s}.Use timing diagrams for real-time and embedded systems, and on the web for debounce and throttle logic, animations, timeouts and retries: anywhere “how long” matters as much as “in what order.”
An object diagram is a snapshot of real instances and their links at one moment. Classes state the rules; objects show one situation that obeys them.
name : Class, or : Class when anonymous.status = PAID. Objects show no operations.Use object diagrams to sanity-check a class diagram, to explain a tricky structure with a concrete case, or to describe test fixtures. If you can’t draw a sensible object diagram, the class diagram is probably wrong. It is also, in effect, a picture of a normalized client cache or a JSON fixture.
A communication diagram carries the same information as a sequence diagram but lays it out by who is linked to whom, with numbers giving the order. UML 1.x called it a collaboration diagram.
1: pay().1.1, 1.2, 1.2.1.2 *[for each line]: subtotal()| Choose | When the reader needs to see |
|---|---|
| Sequence diagram | Exact order, loops, alternatives, timing of replies |
| Communication diagram | The network of objects and which ones are coupled |
Rule of thumb: sequence for when, communication for who knows whom. Many objects and few messages favour communication; few objects and many messages favour sequence.
| Question you’re asking | Diagram | How often |
|---|---|---|
| What types exist and how are they related? | Class | Constantly |
| What happens, step by step, in this scenario? | Sequence | Constantly |
| What states can this object be in? | State machine | Often |
| What is the workflow, with branches and parallel work? | Activity | Often |
| Who uses the system and for what? | Use case | Often, early |
| How is the codebase organized? Any cycles? | Package | Sometimes |
| What are the swappable parts and their contracts? | Component | Sometimes |
| Where does everything run? | Deployment | Sometimes |
| What does a real example of this structure look like? | Object | Sometimes |
| Which objects are coupled to which? | Communication | Rarely |
| How do many scenarios chain together? | Interaction overview | Rarely |
| What is inside this complex class? | Composite structure | Rarely |
| How long do state changes take? | Timing | Rarely |
A crash course on Chapter 6 of Object-Oriented Analysis and Design with Applications: the lifecycle that frames a project, and the analysis-and-design loop that runs inside it.
The chapter opens with a contrast. Amateurs hunt for a magic method or tool that will make development trivial, follow cookbook steps, and either skip documentation or produce it for show. Professionals know there is no silver bullet, that rigid recipes lead to poor designs, and that documents are worth writing only when they serve the substance of the system.
Booch’s position sits between chaos and cookbook: object-oriented analysis and design can’t be reduced to a recipe, yet it is well defined enough to be predictable and repeatable for a mature team. The chapter describes it from two angles:
| Macro process | Micro process | |
|---|---|---|
| What it is | The overall software development lifecycle | The analysis and design process |
| Scope | The whole team, from idea to first release | Getting from requirements to an implementable design |
| Shaped by | Your lifecycle style: waterfall, iterative, agile, plan-driven | Your analysis and design technique: structured, object-oriented |
| Covered in | 6.2 | 6.3, the real focus of the chapter |
The separation is the key idea. Because the two concerns are independent, the object-oriented techniques of the micro process work just as well inside an agile lifecycle as inside a plan-driven one.
A successful project delivers what the customer expected (or more), on time and on budget, and produces a system that copes well with change. Across the successful object-oriented systems the authors studied, two traits kept appearing, and they were missing from the failures:
IEEE 1471 defines architecture as a system’s fundamental organization: its components, how they relate to each other and to the environment, and the principles that guide its design and evolution. Most definitions agree that architecture covers both structure and behavior, deals only with significant decisions, may follow an architectural style, is shaped by stakeholders and environment, and records the rationale behind decisions.
A sound architecture gives a system conceptual integrity, which Brooks called the most important consideration in system design. End users rarely see it, but a clean internal structure is what makes a system understandable, extensible, maintainable and testable. A clear architecture also reveals common abstractions and mechanisms; exploiting them makes systems smaller, simpler and more reliable. Without one, the book warns, you get the software equivalent of sludge.
Good architectures share three attributes:
In a few mature domains the problem is so well understood that development can be almost fully codified. Most real systems aren’t like that: they balance a unique mix of functional and performance requirements and need the team’s full creativity. That calls for an iterative, incremental process.
Iterative and incremental development means delivering functionality in a series of releases (iterations), each more complete than the last (increments). Releases may be internal or external. What goes into each iteration is chosen to retire the most critical risks first, and the lessons from one iteration feed the next. This approach underpins most modern methods, including XP and Scrum.
| Advantage of iterating | Why |
|---|---|
| Requirements changes are accommodated | Each iteration focuses on a specific set of requirements. |
| No “big bang” integration | Every iteration integrates what it builds, so integration is continual. |
| Risks are addressed early | Early iterations tackle key risks and surface new ones while they’re cheap to handle. |
| Tactical product changes are possible | You can adjust the product or release early, for example to answer a competitor. |
| Reuse is easier | Key architectural components exist early, so reusable pieces are easier to spot. |
| Defects are found earlier | Testing happens every iteration, not only at the end. |
| People are used more effectively | Team members play several roles instead of passing work down a pipeline of handoffs. |
| The team learns along the way | Problems in one iteration are fixed in the next. |
| The process improves | Each iteration ends with an assessment of what worked and what didn’t. |
Parnas and Clements argued that no process will ever let us design software in a perfectly rational way, but that we can “fake it”: following an agreed ideal process beats working ad hoc, and makes progress measurable. Booch adds that the process must be well-managed (controllable and measurable) without being so rigid that it stifles creativity.
Rather than a binary choice between RUP, XP, Scrum, Crystal and the rest, every process sits somewhere on a continuum with agile at one end and plan-driven at the other.
| Agile processes tend to be… | Plan-driven processes tend to be… |
|---|---|
| Lightweight, low ceremony | Heavier, more ceremony and documented artifacts |
| Reliant on the team’s tacit knowledge | Reliant on well-documented processes |
| Tactical: don’t build for an unknown future | Strategic: a strong architectural framework that absorbs future change |
| Built on close customer collaboration | Built on an up-front customer contract |
| Self-organizing teams | Managed and controlled, with explicit milestones |
| Emergent: the process evolves as it runs | Defined up front, then continually improved |
For agile, the process is a means to an end: deliver what the customer needs now, as fast as possible. For plan-driven, a predictable, repeatable process is itself a goal. Neither is “all process” or “all creativity”: agile still has process, and an iterative plan-driven process with time for prototyping leaves plenty of room for invention.
Let risk decide: is it riskier to use a given practice or to go without it? The style can also change over the lifecycle, with more agility early and more rigor later. Treat the process like a recipe you adjust to your ingredients and guests, and keep refining it after every iteration: keep what worked, drop what didn’t, repeat.
The macro process is the controlling framework for the whole team. It runs from the first idea to the first version of the system that implements it; later versions, for evolution or maintenance, are new runs of the macro process. As its example the chapter uses the RUP lifecycle: plan-driven, but architecture-focused and iterative, which supports both of the traits from 6.1.
Its primary product is a stream of executable releases, each a refinement of the last. Secondary products are behavioral prototypes, to explore designs and the system’s “dark corners”, and documentation that records design decisions and rationale.
The book describes the macro process along two dimensions: content (what is done: roles, tasks, work products, grouped into disciplines) and time (when it is done: milestones, phases and iterations).
The order doesn’t make it a waterfall. A waterfall makes one pass through the disciplines for the whole system. An iterative process makes many passes, and how much work each discipline gets in a pass depends on where you are in the lifecycle.
Iterations alone could go round in circles forever. A series of milestones gives them a strategic frame: each milestone is reached by one or more iterations and ends with an assessment. Pass it and you move to the next phase.
The book is emphatic that milestones are quality or maturity gateways, not dates. If the planned date arrives and the project hasn’t reached the required maturity, the date slips; the criteria don’t.
Agile methods have phases too, and they line up closely with RUP’s:
| Common purpose | RUP | XP | Scrum |
|---|---|---|---|
| Envisioning, feasibility, scoping | Inception | Exploration | Planning |
| Release and iteration planning | Elaboration | Planning | Staging |
| Implementing and testing | Construction | Iterations to release | Development (sprints) |
| Productizing and deploying | Transition | Productionizing | Release |
| After release | New lifecycle | Maintenance | — |
The row alignment is a simplification for comparison; the book lists the phases of each method and the four shared purposes, not a one-to-one mapping.
Inside each phase, iterations may touch every discipline, but the balance shifts. Inception leans on requirements, Elaboration on analysis and design (especially architecture), Construction on implementation and test, and Transition on deployment. Project management, configuration and change management, and environment run throughout.
End every iteration with a postmortem: assess the system, the environment and the team, and use it to adjust the plan and the process. Recommended iteration lengths differ by method:
| Method | Recommended iteration length |
|---|---|
| XP | One to two weeks |
| Scrum | 30 days per sprint |
| RUP | Two to six weeks |
Analysis is most visible early, in Inception and Elaboration, when you analyse the architecturally significant requirements, and tapers off later. Design may start in Inception (for example, if you’ll build on existing components), peaks in the early architectural iterations, and later shifts to peripheral and supporting elements.
Release planning defines a controlled series of releases that grow in functionality until they cover the full production system. Its input is the scope plus constraints such as cost, time and quality; its output is a development plan of releases, team activities and risk assessments.
Prototyping. A behavioral prototype explores one isolated element, such as an algorithm, a UI model or a database schema, to resolve risk quickly without endangering the production releases. It is deliberately incomplete and meant to be thrown away. Every significant new system should start with a proof of concept, and it’s far better to discover wrong assumptions then than later. Prototypes shouldn’t grow into production code without a compelling reason, and meeting a short-term deadline is explicitly not one.
Post-transition evolution and maintenance. Lehman and Belady observed that a program used in the real world must keep changing or become less useful (continuing change), and that its structure grows more complex unless you actively work against it (increasing complexity). Each new release re-runs a lifecycle sized to the change: a small fix may be mostly Transition, while a major release needs Inception and Elaboration too. A change request list collects defects and enhancements for prioritizing, and urgent operational problems may need patches outside the normal release rhythm.
The micro process runs inside the macro process. The macro process sets its scope, hands it requirements (plus any specifications from earlier micro iterations), and takes back design specifications, most importantly the architecture, to implement, test and deploy.
The micro process deliberately blurs the old analysis and design phases into a continuum of levels of abstraction.
| Analysis | Design | |
|---|---|---|
| Purpose | Model the system’s behavior: understand the problem a little better | Invent the elements that provide the behavior analysis requires |
| Focus | Behavior, not form: what the system does, not how | A specification that can be implemented efficiently |
| Main skill | Discovery, alongside domain experts | Invention, from the solution domain |
| Done when | It represents the requirements accurately, is consistent, and is a good basis for design | It is detailed enough to implement and test |
Architecture is about the relationships between parts: their responsibilities, interfaces and collaborations. It is strategic. Component analysis and design looks inside each part to see how it meets the requirements the architecture places on it, which is more tactical. Combining the two concerns with the two disciplines gives four focuses.
The number of levels can’t be fixed in advance; it depends mostly on the system’s size. If a component turns out too large to analyse, step back and do another round of architectural analysis to split it. What to take down a level, and when, is opportunistic and risk-based: you can dive deep on one poorly understood element, then come back up.
At any level of abstraction and for any scope, the micro process runs the same four activities. They are listed in order but in practice overlap; with experience you need fewer passes.
The model gives the team a shared vocabulary, a central repository that newcomers can browse, and a global view in which architects spot commonalities. UML makes inconsistencies easier to see.
One model or two? A separate analysis model pays off for systems that will live for decades, have several variants, or target several platforms, where it acts as a platform-independent view (the idea behind OMG’s Model Driven Architecture). Otherwise, keeping two models in sync is costly, and the analysis model can simply evolve into the design model.
Following IEEE 1471, an architect describes the system through views, each guided by a viewpoint that names the models, the stakeholders and the concerns it addresses. Kruchten’s 4+1 set is a simple starting point:
| View | Describes |
|---|---|
| Requirements (use case) view, the “+1” | The architecturally significant requirements. Significant functional ones drive the scenarios analysed early; nonfunctional ones (qualities such as performance, security and scalability, plus economic, technology and regulatory constraints) are usually the most architecturally significant and drive the mechanisms. |
| Logical view | Significant analysis and design elements, their organization into components, packages and layers, a few key realizations, and the main mechanisms and patterns. |
| Implementation view | Key implementation elements such as executables and directories. It matters for parallel development, configuration management, integration and testing. |
| Process view | Independent threads of control and which logical elements take part in them. |
| Deployment view | Nodes such as computers, routers and containers, and which elements are allocated to them. |
The name records a debate over whether requirements count as an architectural view; they’re included because they shape the architecture and should be traced to the other views. Add views you need (data, user experience) and drop ones you don’t. On some projects all of this lives in a software architecture document (SAD), organized by view, reviewed with the team and kept up to date.
This activity finds the elements that describe the solution at the current level. It is really an evolution: elements at one level become the input for identifying elements at the next. Keep descriptions brief here; later activities refine them. (“Element” is the book’s general term for whatever you’re working with: an analysis class, a component, a design class.)
| Focus | Elements identified | Why |
|---|---|---|
| Architectural analysis | Key abstractions | Form the domain vocabulary early, so concepts aren’t defined in conflicting ways later. |
| Architectural partitions | Separate areas of concern and give the system its high-level logical organization. In a layered architecture, the partitions are the layers. | |
| Analysis mechanisms | Placeholders for common services and policies, foundational (persistence, security, error handling, transactions) or domain-specific, described without implementation detail so analysts solve them consistently. | |
| Architectural design | Architecturally significant design elements | Encapsulate business behavior or data access, specified so they can be implemented efficiently. |
| Architectural partitions | Refined from analysis; used to cluster design elements. | |
| Design mechanisms | Analysis mechanisms refined to specific technologies. | |
| Component analysis | Analysis classes | The first object-oriented decomposition of the solution, each with cohesive responsibilities. |
| Component design | Design classes | As in architectural design but at a lower level, detailed enough to implement. |
Based on the book’s Table 6–2.
Analysis leans on discovery; design leans on invention. Some analysis elements become classes, others turn out to be attributes or synonyms, and some are simply wrong, which is fine as long as decisions stay open. Elements are fluid in analysis and solidify in design. Good candidates to become design elements are analysis classes with non-trivial responsibilities, groups whose information should be managed together, groups that collaborate in the same realizations, and groups with the same responsibilities. Patterns and design principles also shape the refinement, and it’s always worth studying similar systems and reusable assets.
If you keep separate models per level, maintain traceability between them so you can assess the impact of changes.
Done when you have an ample set of consistently named and described abstractions, and the model isn’t changing wildly on each pass. Finding architecturally significant elements late in the lifecycle points to a flaw in requirements, analysis or design.
This activity describes how the elements work together to deliver the required behavior by distributing responsibilities sensibly. The product is a set of realizations, the link between requirements and solution, documented with sequence and communication diagrams, plus state machines for elements whose state drives their collaborations. In analysis, a phrase or a sentence per responsibility is enough; anything longer suggests the responsibility should be split.
Behavior analysis, typically use case analysis:
Pattern scavenging: look across realizations for repeated interactions, responsibilities and operation signatures, remove gratuitous differences, and capture common collaborations as documented mechanisms, which protects the architectural vision.
Checkpoints: responsibilities are balanced (no element “does it all”), consistent within each element, not duplicated across elements, sufficient for the flows each element takes part in, and simple and clear. You don’t need a realization for every scenario, just the primary ones and a meaningful set of secondary ones.
Relationships give the solution its shape; at the architectural level they define the system’s overall structure. Draw the interesting ones, where a relationship reflects a fundamental decision or a detail needed for implementation, not every possible view. A good starting set is one diagram per realization.
Measures: consistency (if two elements collaborate in a realization, they have a relationship), cohesion, coupling and completeness, so the next level doesn’t need new significant relationships. Relationships that are awkward to specify mean the elements aren’t right yet.
The first three activities look at elements from the outside; this one looks at each element on its own, defining behavior and attributes consistently across every scenario it takes part in, in enough detail to move to the next level. It comes last on purpose: deferring detailed decisions avoids premature choices that rule out smaller, simpler architectures and leaves room to change internal representations later.
One element at three levels of abstraction. Each level adds only the detail the next one needs.
// Analysis: a responsibility, one sentence, no representation
// PaymentHandler: "takes payment for an order"
// Design: a named protocol on an interface
interface PaymentGateway {
charge(amount: Money): PaymentResult;
}
// Detailed design: full signatures, ready to implement and test
interface PaymentGateway {
charge(amount: Money, idempotencyKey: string): Promise<PaymentResult>;
refund(paymentId: string, amount?: Money): Promise<RefundResult>;
}Done when each element’s semantics are sufficient, primitive and complete for the current level: informal responsibilities and attributes in analysis, implementable operations and attributes in design. Not exhaustive detail, just enough for a competent implementer. The main measure is simplicity: complex, awkward or inefficient semantics mean the element itself is wrong or poorly represented.
| Phase | Purpose | Key work products | Milestone |
|---|---|---|---|
| Inception | Make sure the project is valuable and feasible | Vision, behavioral prototypes, risk list, key mechanisms, development environment | Scope is understood |
| Elaboration | Build and prove the architecture | Executable architectural releases (vertical slices), architecture document | Architecture is stable |
| Construction | Move from discovery to production | Executable releases covering the remaining scenarios | System is ready for end-user testing |
| Transition | Make the product acceptable to its users | Packaged product, documentation, training and marketing material | System is ready to be deployed |
| Activity | Main product | Measure of goodness |
|---|---|---|
| Identify elements | Elements with brief descriptions in the model | Ample, consistently named; model stable between passes |
| Define collaborations | Realizations and responsibilities | Completeness and simplicity; balanced responsibilities |
| Define relationships | Relationships in the model | Consistency, cohesion, coupling, completeness |
| Detail semantics | Refined behavior and attributes | Simplicity: sufficient, primitive, complete |
A crash course on Chapter 12 of Object-Oriented Analysis and Design with Applications: the Vacation Tracking System, followed from first idea to running software.
Chapter 5 taught the notation. Chapter 12 puts it to work on one realistic system: the Vacation Tracking System (VTS), a web application where employees request time off, managers approve it, and HR staff maintain the rules that decide what is allowed.
The chapter is organized by the four phases of an iterative process, the same lifecycle Booch describes in Chapter 6. Each phase asks a different question, produces different artifacts, and ends at a milestone where the team decides whether to continue.
Inception answers two questions: what are we building, and is it worth building? The team writes a vision, identifies who will use the system, captures the main goals as use cases, and lists the biggest risks. Nothing needs to be complete; it needs to be clear enough to decide whether to go ahead.
| Use case | Actor | Goal |
|---|---|---|
Manage Time | Employee | Request vacation time and view existing requests. |
Approve Request | Manager | Respond to a direct report’s vacation request. |
Award Time | Manager | Give a direct report extra leave (comp time). |
Edit Employee Record | Clerk | Edit an employee’s details, including leave allowances and the most a manager may award. |
Manage Locations | Clerk | Maintain location records and their rules. |
Manage Leave Categories | Clerk | Maintain leave categories and their rules. |
Override Leave Records | Clerk | Override a rejection that the system’s rules produced. |
Back Up System Logs | System Admin | Back up the system’s logs. |
Override Leave Records exists because automatic checks will sometimes reject a request that a human should allow. Overrides must be recorded.The diagram names the goals; the written specification carries the detail. A sketch of the main success scenario for Manage Time, in the usual numbered style:
Alternate flow 4a: a rule is violated. The system shows which rule and why, and the employee changes the request or cancels. Alternate flow: the employee withdraws a request that hasn’t been decided yet.
Elaboration turns a promising idea into a proven architecture. The rule of thumb from the chapter: describe and discuss the architecturally significant use cases first. Their details don’t all need to be finished, but they must be addressed before any single use case is refined further.
In theory, analysis should be independent of how the system will be built. In practice, knowing the target architecture shapes the analysis, and this is especially true for web applications, because the decision to deliver through a browser is usually made at inception.
The book points to two signs of this in the VTS requirements. The Manage Time flow asks for a visual calendar to help employees pick and compare dates. That requirement exists partly because browsers of the time had no common built-in date picker; for a native Windows client it would hardly have been worth stating. More subtly, the use cases keep talking about navigating to pages and submitting information, which are web concepts through and through.
There is no agreed quantitative way to represent an arbitrary architecture, so the chapter uses the 4+1 view model introduced in Chapter 6: four views of the system, each for a different audience, held together by the scenarios in the use cases.
The chapter briefly reviews web architecture to explain later design decisions. These are the ideas that matter most:
| Web reality | Design consequence in VTS |
|---|---|
| HTTP is request/response: the browser asks, the server answers. | Every user action, such as submitting a request, is one round trip handled by a server-side controller. |
| HTTP is stateless: the server forgets you between requests. | Who is logged in and what they’re editing must live in a session or in the database, and be reloaded on each request. |
| Pages are built on the server and rendered in the browser. | The design needs to model screens and navigation, not just classes. Construction adds a user-experience model for this. |
| The browser is not trusted. | Client-side checks are for convenience. Leave rules must be enforced again on the server. |
| Many users at once, mostly short requests. | Keep request handling fast and stateless so the server can scale; push slow work, such as email, out of the main path. |
By the end of elaboration the team should have an executable baseline: a thin slice through every layer, from browser to database, running the architecturally significant scenarios. That slice proves the architecture works before most of the code is written.
Construction is where most of the product gets built, iteration by iteration, on top of the architecture proven in elaboration. For a web application this means modelling three things side by side: what the user sees and how they move between screens, the classes that hold the business logic, and the behavior of the most important objects.
A web app’s screens and navigation are a design concern in their own right, so the chapter models them explicitly. Each screen is a stereotyped class; arrows show how the user can move between screens and which forms submit data.
A common step between use cases and design is to sort the objects a scenario needs into three kinds. Boundary objects sit at the edge of the system and talk to actors or other systems. Control objects coordinate one use case’s logic. Entity objects hold the long-lived business data.
Manage Time. Actors talk only to boundaries; boundaries talk to controls; controls do the work with entities and with other boundaries.The rules are what make this useful. Because the actor reaches the system only through a boundary, swapping the web page for a mobile screen leaves the control and entities unchanged. Because entities never call boundaries, business data never depends on how it is displayed.
Refining the analysis gives design classes. The heart of VTS is a small domain: employees and their managers, requests, the leave categories and locations whose rules govern those requests, and each employee’s allowance per category.
interface LeaveRule { // «interface»
check(request: LeaveRequest): string[]; // returns violations
}
class MaxDaysRule implements LeaveRule { // realization
constructor(private maxDays: number) {}
check(r: LeaveRequest) {
return r.days() > this.maxDays
? ["Requests in this category are limited to " + this.maxDays + " days"]
: [];
}
}
// Rules come from both the leave category and the employee's location
function violations(r: LeaveRequest): string[] {
const rules = [...r.category.rules, ...r.employee.location.rules];
return rules.flatMap(rule => rule.check(r));
}Manage Locations and Manage Leave Categories both say HR manages rules. If each rule is an object implementing one interface, adding a rule type is a new class, not an edit to the request logic. This is the open/closed principle, delivered through realization.The Request class has the most interesting lifecycle in the system, so the chapter gives it a state machine. A request is created, then submitted. From submitted it can be approved, rejected or withdrawn. Once approved it is either canceled or, when the time has been taken, completed.
Notice what the diagram forbids. A withdrawn request can’t be approved, and a rejected one can’t be canceled. Each end state is final, so any later change, such as an HR override, has to be a deliberate, recorded action rather than a quiet status edit.
type RequestState = "created" | "submitted" | "approved"
| "rejected" | "withdrawn" | "canceled" | "completed";
const transitions: Record<string, Record<string, RequestState>> = {
created: { submit: "submitted" },
submitted: { approve: "approved", reject: "rejected", withdraw: "withdrawn" },
approved: { cancel: "canceled", complete: "completed" },
};
function fire(state: RequestState, event: string): RequestState {
const next = transitions[state]?.[event];
if (!next) throw new Error(event + " is not allowed when " + state);
return next;
}A sequence diagram ties the pieces together. It shows the web realities from 12.2 in action: one HTTP round trip, rules enforced on the server, and the manager notified without making the employee wait.
app.post("/requests", async (req, res) => { // :RequestController
const request = LeaveRequest.from(req.body, req.session.employee);
const problems = violations(request); // 3, 4: rules on the server
if (problems.length > 0) {
return res.status(422).render("new-request", { request, problems }); // 8
}
await requests.save(request.submit()); // 5
mailer.notifyManager(request); // 6: not awaited (async)
res.redirect("/requests/" + request.id); // 7: post/redirect/get
});Transition moves the finished system into the hands of its users. The work changes character: less designing, more installing, migrating, training and listening. The phase ends when the product is released and the users can run it without the development team holding their hand.
For VTS this means setting up the production servers, loading existing employee and leave data, training HR clerks and managers, and piloting before a full rollout. Problems found in the pilot loop back to the team for fixes.
Post-transition is the system’s working life: fixing defects, handling change requests, and planning the next release. Two parts of the VTS model come into their own here. The Back Up System Logs use case keeps the audit trail safe, and the rule objects from construction let HR adapt the policies as the business changes, often without a new release at all.
Larger changes start the cycle again. A new release gets its own short inception, elaboration, construction and transition, building on the architecture already in place.
| Phase | Main question | Key VTS artifacts | Ends at |
|---|---|---|---|
| Inception | What are we building, and is it worth it? | Vision, actors, use case model, risks | Lifecycle objectives |
| Elaboration | Can we build it? Is the architecture sound? | Significant use cases, 4+1 views, executable baseline | Lifecycle architecture |
| Construction | Build it, iteration by iteration | UX model, analysis and design classes, state machines, code and tests | Initial operational capability |
| Transition | Can users run it? | Deployed system, migrated data, trained users | Product release |
A crash course on Chapter 1 of Practical Database Design for the Web: why spreadsheets stop working, the short history that produced the relational model, the other database models you’ll hear about, what a DBMS does for you, and when a website actually needs a database.
Loosely, anything that stores data is a database. The chapter makes the idea sharper by walking a small contact list from a word processor, to a spreadsheet, to real database tables, and noticing what breaks at each step.
A Word document holds data as paragraphs. You can search for a name with Find and read what is nearby, but the only structure is the one you keep in your head. A spreadsheet is better: each column is a field, each row is a record, so you can sort by any column and scan for blanks or near-duplicates. Plenty of small businesses run on exactly this.
It falls apart when the data grows or a second person needs it at the same time. Records get hard to find, updates are missed in one place, entries are typed inconsistently, the same information is repeated over and over, and you run out of “dimensions” to put new kinds of data in.
That last problem is the heart of the example. Each client needs a list of dated notes, and you don’t know how many. They won’t fit in one cell, so they go on a second sheet, where each note repeats the client’s name and nothing actually links the two sheets. A database fixes this by storing the client once, giving it an ID, and having each note point at that ID. Step through it below.
With that in mind, the chapter offers a working definition: a database is a program that manages multi-dimensional data for multiple simultaneous users
. You describe the structure, hand it the data, and it looks after storage, fast searching, and adding new dimensions such as the notes. The payoff, in the book’s list of five:
clients and notes as separate arrays and store note.clientId. A foreign key is the database version of that normalized store.The history matters for one idea above all: separating how data is organized logically from how it is stored on disk. Early systems didn’t do that, and every model since has been judged partly on how well it does.
In the 1960s, computers could suddenly hold a lot of data. Storage schemes were built for processing efficiency, with no standard access method, so you had to know how a particular database was laid out on disk to use it. The hierarchical and network models come from this era. In 1970, E. F. Codd at IBM published A Relational Model of Data for Large Shared Data Banks, proposing tables, a mathematical foundation, and an English-like query language. That paper set off the prototypes, products and standards in the timeline.
The oldest model. Data lives in a tree of tables, and every relationship is parent/child: a parent can have many children, but a child has exactly one parent. On tape-based mainframes this was efficient because the logical layout matched the physical one.
The book’s example is its own publisher, glasshaus, storing divisions, the books in progress, the authors on each book, and author details. Every query must start at the top and walk down. To find the authors of one book, you go from the division to the book, down to the current authors to collect their IDs, then back up to the top and down a different branch to get their details. Trace it below.
| Table | Purpose |
|---|---|
| Divisions | The publisher’s subject divisions |
| Books | Books currently being written |
| Current Authors | Which authors are on a given book |
| Authors | Details for every available author |
Based on the table beside the book’s figure, reworded.
Two costs follow. Users must know the exact structure to write any query, and data gets duplicated: an author on three books has their details attached in three places. The book’s analogy is a file explorer, where you can only reach a folder by opening every folder above it. IBM’s IMS is the classic commercial example.
The network model relaxes both limits. You can start a query at any table and move up or down, and because parent/child is replaced by association, a table can be linked to more than one “parent”. The links between tables form a network, hence the name. Its vocabulary:
The book names IBM’s DL/1 and Computer Associates’ IDMS as network products. IDMS is the classic network database (it follows the CODASYL standard); DL/I is more usually described as the data language of IBM’s hierarchical IMS. Either way, both models now survive only for accessing legacy data.
Codd’s model keeps the structure of the data independent of how it is stored on disk. You describe tables and relationships; the system decides on files, pages and indexes. This is the model you will meet almost everywhere, and the rest of the book builds on it.
An Authors table, for example, might have name, email and billing fields. Fill them in for one author and you have a record; collect every author’s record and you have the table. The model’s power is in the relationships it keeps between tables, of three kinds:
In the figure, division_id links to books.book_division (one-to-many), each author links to one authorbilling row (one-to-one), and books_authors resolves the many-to-many between books and authors. (The book calls this table authors_books once in the text; the figure’s name is books_authors.) Here is the many-to-many in SQL. This is a course addition: the book doesn’t introduce SQL syntax until later.
PostgreSQL · the linking table
CREATE TABLE books_authors (
book_id INT NOT NULL REFERENCES books(book_id),
author_id INT NOT NULL REFERENCES authors(author_id),
PRIMARY KEY (book_id, author_id) -- a pair can appear only once
);
-- Who is writing book 7?
SELECT a.author_name
FROM books_authors ba
JOIN authors a ON a.author_id = ba.author_id
WHERE ba.book_id = 7;author_ids column, or with an author1, author2, author3 column set. Both make querying and integrity checks painful; the linking table is the answer. Also, don’t split tables one-to-one by default: do it only when you have a reason.pgTable with two .references() columns and a composite primary key, and the many-to-many is declared as two one-to-many relations through it, exactly as in the figure.Object databases grew up alongside object-oriented languages like C++, for organizations whose data didn’t fit tables well. Stored objects look like the language’s own objects: data plus the methods that operate on it. Because the designer defines how objects may be changed, users have little chance to corrupt them. The trade-offs: there is no standard query language like SQL, and you need someone who can design the objects and program in an OO language. The book’s example is CA’s Jasmine, used to track McLaren’s Formula One car performance.
A blend of the two, created for two reasons: storing large multimedia data, and the mismatch between how programs structure data and how a relational database stores it. Programmers kept writing code to convert between the two on every read and write. Object databases avoid that, but switching to one later is hard. Object-relational systems let you stay relational while storing information in its native object form.
JSONB columns are the object-relational idea alive and well.Built to store images, video and audio together with metadata describing them. Users query the metadata and get the media back. Size is the persistent problem, which is why, in the book’s day, these were mostly used over a fast local network.
Not strictly a model but a configuration: several smaller machines share one database’s workload and appear to users as a single database. Each machine is a node, connected over a network. This adds power without upgrading one big machine.
A homogeneous system runs the same DBMS with the same layout on every node, for example a busy site’s mirror servers, with each request routed to a node that isn’t overloaded. The hard part is replication: every node must stay consistent, so these DBMSs ship with replication and fault tolerance. A heterogeneous system usually exists to support legacy systems: nodes may run different DBMSs, data normally isn’t duplicated, and nodes are linked so each can reach data it doesn’t hold.
Strictly speaking, a file with consistently structured data is a database too, and flat files run a lot of the web. The common format is comma-separated values: one record per line, fields separated by commas. The application must read each line, split it into fields, and only then decide what to do with the record. Here are the chapter’s two clients as CSV; note the quotes around the address that contains a comma.
Name,Phone,Address
John Smith,555-0404,"1 Queen's Way, Southville"
Jane Doe,555-1234,53 East 17th AvenueXML wasn’t designed as a database, but it was often used as a cheap one. As storage it shares the flat-file drawbacks: it’s read from disk and isn’t compressed or optimized, so access is slow. Its advantages over plain text are portability and that it describes the structure of the data it holds, which also makes it easier for people to read. Most languages can parse it, query it, and add or change records, and XSLT can transform it into XHTML for display.
<Names>
<client name="John Smith">
<telephone>555-0404</telephone>
<address>1 Queen's Way, Southville</address>
</client>
<client name="Jane Doe">
<telephone>555-1234</telephone>
<address>53 East 17th Avenue</address>
</client>
</Names>A native XML database is designed to store XML documents, with DBMS features on top: document collections and XML query languages. XPath addresses a specific part of a document but can’t group or sort results. XQuery is much more powerful; when the book was written it was read-only. Most mainstream databases were “XML-enabled” (they could import and export XML), but native XML databases came from smaller vendors and open-source projects such as Apache Xindice.
JSONB column than an XML database.A database management system is the software that stores and manages databases. To the DBMS, a database is one named collection of related tables, usually among many. A database built in a DBMS is practically useless without it.
The book works with relational DBMSs (RDBMSs) from here on. Its examples are the leaders of the time: Oracle 9, Sybase ASE and Microsoft SQL Server 2000.
The book gives three reasons a website uses a database:
The book also warns that being able to put something in a database doesn’t mean you should. Each one has a cost: the database itself plus the scripts to access it. Its example is a site header and footer, which a server-side include handles more cheaply than a query on every page.
| Model | Structure | How you query | Status |
|---|---|---|---|
| Hierarchical | Tree; each child has one parent | Always from the top, walking down | Legacy (IMS) |
| Network | Record types linked by set types; many “parents” | Start anywhere, but only along predefined sets | Legacy (IDMS) |
| Relational | Tables of records and fields, related by keys | Declaratively with SQL; storage hidden | The default |
| Object | Objects with data and methods | Through the programming language; no SQL | Niche |
| Object-relational | Relational tables holding object-like values | SQL plus extensions | Built into modern RDBMSs |
| Flat file / XML | Text files with consistent structure | Read and parse the file in code | Fine for small, simple data |
A crash course on Chapter 2 of Practical Database Design for the Web: what a data model is, how the relational model structures data, how nulls and keys work, why we normalize, how integrity rules protect data, and what indexes and hashing do at the physical level.
The chapter opens by defending theory. You can build a database without it, and it will mostly work, until you push it. Well-designed databases keep their performance as they grow and are easier to maintain and extend. A catalog search that is fine with 500 products can crawl at 5,000 if the design is poor. The good news: the ideas behind the relational model are simple, even if designing large systems isn’t.
“Data model” has two accepted meanings, and the book asks you to keep them apart:
The book’s analogy: building regulations versus the floor plans for one particular building. This chapter and the next use meaning 1. In that sense, every data model has three aspects:
The big win of modern models is physical data independence. In the 1960s there was no DBMS: you wrote your own data-management code and had to know where every file lived and how it was laid out. The fix was a logical data model layered over the physical storage. Programs talk to the logical model; the DBMS maps it to files. In principle, storage can be completely reorganized without rewriting a single program, and a general query language becomes possible because nobody needs to know where data physically sits.
Three logical models appeared in the late 1960s and early 1970s: hierarchical, network and relational. The first two (Chapter 1) proved inflexible and were overtaken by the relational model in the early 1980s, which is why the rest of the chapter focuses on it.
Edgar Codd, a mathematician at IBM, disliked the ad hoc nature of the early models and built one on set theory and predicate logic. He published it in the July 1970 Communications of the ACM. IBM’s System R prototype followed in the mid-1970s, then SQL/DS in 1981, which evolved into DB2. But a small company, Relational Software, Inc., shipped first, in 1979; its product was Oracle. The 1980s brought DB2, Sybase, Informix and many more.
The core idea is a relation. Take a set of employee names, a set of positions, a set of locations and a set of salaries. Each employee takes one value from each set. Line those choices up and you get a table: each column draws from one set, each row is one employee. Build it below.
It looks like a spreadsheet, but the book stresses the resemblance is only skin deep. Two warnings: a relation (a table) is not a relationship (a link between tables), and from here on the book just says “table”. The rules that make a table different:
Formally, tables are relations, rows are tuples and columns are attributes. The book uses the everyday names, but rejects file, record and field as synonyms: those are physical structures, and a logical table may be split across several physical files.
A table usually stands for something real (employees, products, customers) or abstract (orders, invoices, contracts). Columns are the properties you store; each row is one occurrence. The chapter’s running example has three more tables, PRODUCT, CUSTOMER and SALE (shown in section 2.4). Drawing whole tables is clumsy, so the book uses a compact notation:
EMPLOYEE(Name, Position, Location, Salary)
PRODUCT(ProductNo, Description, ReorderLevel, Price, QtyInStock)
CUSTOMER(CustomerNo, First, Last, Address, CreditLimit)
SALE(SaleNo, SaleDate, CustomerNo, ProductNo, Qty, Amount, Salesrep)ORDER BY. And treating a table like a spreadsheet, with duplicate rows or several values crammed into one cell.Everything is bits on disk, but a DBMS offers data types that say how to store and return values while hiding the physical form. Expect integers and decimals, a date type (often with time, sometimes separate), and fixed or variable-length strings. Many products add time intervals, BLOBs (binary large objects, such as images or documents), CLOBs (character large objects, for megabytes of text or XML) and Booleans.
Strictly, data types aren’t part of the relational model. It speaks of domains: the set of allowed values for a column. A domain can be “any integer greater than 4000”, or even a mixed bag of values, though single-type domains are the norm. Few products supported domains directly (the book names Oracle); you get most of the effect with constraints, covered in Chapter 3.
CREATE DOMAIN, for example CREATE DOMAIN salary AS integer CHECK (VALUE > 20000), which is exactly the book’s restricted single-type domain. In most products a CHECK constraint on the column is the everyday equivalent.Sometimes there is no value to store, for two different reasons. The value may be unknown (the customer didn’t give an email address) or inapplicable (the customer has no email address at all). An empty string means “a blank string”, not “no value”, and numbers and dates have no blank. Old tricks such as -1 for a missing price break as soon as you compute a sum, an average or a date difference.
Codd’s answer is null: a marker that stands in place of a value, usable with any type. It is not itself a value, so “null value” is a slight misnomer. In the SALE table, sale 12348 has a null CustomerNo, perhaps a cash sale. In CUSTOMER, a null CreditLimit means “not allowed credit”, which a zero would get wrong: zero suggests the customer has credit, just none left.
Because null isn’t a value, comparisons and arithmetic with it are meaningless: the book likens “is null > 2000?” to asking “is fish > 2000?”. The usual answer is “unknown”. Pick an expression below to see what SQL does.
PostgreSQL · nulls in the book’s CUSTOMER table (course addition; SQL arrives in Chapter 3)
SELECT AVG(credit_limit) FROM customer; -- 1250: the three nulls are skipped
SELECT * FROM customer WHERE credit_limit = NULL; -- no rows, ever: the test is unknown
SELECT * FROM customer WHERE credit_limit IS NULL; -- Amelia, Antonio and Wayne
SELECT COALESCE(credit_limit, 0) FROM customer; -- show 0 instead of null, explicitlyPractical advice from the chapter: check how your RDBMS handles nulls, and say per column whether nulls are allowed. If a product doesn’t support nulls, simulate them with a flag column, for example AllowedCredit = Y/N beside CreditLimit. The other option is a default value, such as a dummy “Cash Sale” customer used as the default CustomerNo. The rule of thumb: use a sensible default if one exists; use null when no default makes sense.
= NULL instead of IS NULL; expecting AVG to count missing rows; using 0, -1 or '' as “missing” in a column that can legitimately hold those values. In TypeScript, the same split shows up as null versus undefined versus "": decide which one means “no value” and map it to SQL null consistently.If two rows were identical you couldn’t tell the real-world things apart, so the relational model requires every table to have a column, or combination of columns, that uniquely identifies each row: the primary key. A single column is preferable, but composite keys (such as order number plus product number) occur naturally. The model insists on a primary key even though many RDBMSs don’t.
In PRODUCT, ProductNo is clearly a unique code. Description isn’t safe: names and descriptions aren’t guaranteed to stay unique, or unchanged, and primary key values shouldn’t change. EMPLOYEE is harder:
| Name | Position | Location | Salary |
|---|---|---|---|
| Jane Smith | Manager | London | 35000 |
| Richard Jones | Analyst | New York | 35000 |
| Anna Horton | Programmer | London | 22000 |
| Alex de Tocqueville | Programmer | Paris | 21000 |
The book’s EMPLOYEE table as shown on p. 35. (On p. 28 Richard Jones earns 34000; on p. 35 the two top rows share 35000, which is why Salary can’t be the key.)
Position, Location and Salary all repeat. Name looks unique, but plenty of people share a name. Concatenating every column works on paper but makes searching slow, and two London programmers called Anna Horton on 22000 are not impossible. The better answer is an invented surrogate key, such as an employee number, often generated by the RDBMS (Chapter 4).
A table has exactly one primary key but may have several unique identifiers. Store both an employee number and a social security number, and one becomes the primary key while the other is an alternate key. The book underlines primary keys from here on; in diagrams here they are bold and underlined.
Customers place orders and books have authors, so tables need links too. Look at SALE: it contains CustomerNo, the primary key of CUSTOMER. Take a sale’s CustomerNo and you can look up the right customer. That embedded column is a foreign key: “foreign” because it is another table’s primary (or alternate) key. SALE is the child table; CUSTOMER is a parent table. SALE has a second foreign key, ProductNo, pointing at PRODUCT. Click a sale to follow both.
Three things to notice, all visible in the figure:
Foreign keys exist mainly so you can join related rows, such as sale 12347 with Amelia Waverley’s customer row (Chapter 3). Like primary keys they can be composite, and unlike primary keys a table can have several. You’ll sometimes hear them called references.
Why split data into tables and join them back? One big table is faster to read, up to a point, and that was an early criticism of the relational model. The cost is data redundancy. Here is the book’s BIG_UGLY_TABLE, with customers and sales in one table. Pick a problem to see it.
Splitting data into related tables removes the redundancy. That is normalization. (The name, the book says, came from Codd hearing that Nixon was “normalizing relations” with China.) It is a set of rules, the normal forms, from first (1NF) to fifth (5NF), each removing more redundancy by splitting tables further. Boyce/Codd (BCNF) and Domain-Key (DK/NF) sit outside that ladder and the book skips them. Every table is in at least 1NF by definition; well-designed databases usually reach 3NF or 4NF.
The worked example starts by mashing PRODUCT, CUSTOMER and SALE into one table, ALL_SALES, and normalizing it back. The key idea is the functional dependency: A → B means that knowing A tells you B. Step through it:
| Form | Rule (the book’s definitions, reworded) | Problem it removes |
|---|---|---|
| 1NF | No repeating groups: every cell holds one value | Lists inside cells |
| 2NF | Every column depends on the whole primary key | Partial dependencies on part of a composite key |
| 3NF | Every column depends on the primary key alone | Transitive dependencies (key → X → Y) |
| 4NF | The key determines single values, not collections | Multi-valued dependencies (key →→ many values) |
Two shortcuts: a table with a single-column primary key is automatically in 2NF (though a composite key doesn’t rule 2NF out), and 3NF is summed up as depending on “the key, the whole key, and nothing but the key”. The book reassures you that 4NF trips up experts too, and that most 3NF tables are already 4NF. 5NF is the theoretical limit; in practice there is little reason to go beyond 4NF, or even 3NF.
Normalization costs reads: the DBMS has to reassemble the pieces, and on the Web response time matters. So the book’s advice is to choose by workload. Mostly-read data like a catalog can be denormalized; mission-critical data such as finance should stay normalized even if slower. A common pattern keeps both: a normalized operational database feeding a denormalized data warehouse. Normalization is a bottom-up technique; Chapter 4 designs top-down with ER diagrams.
address2, address3 columns for “any number” of addresses (that is the 4NF problem; use a child table). Denormalizing before you have measured a slow query. And creating a surrogate key before the table is fully normalized: the book’s example deliberately waits until the end.Data has integrity when it is valid and internally consistent. You get there by defining integrity rules, usually during design (Chapter 4), and letting the DBMS enforce them. For legacy data or data you don’t control, the best you may manage is to identify where it breaks the rules. The concept applies to every data model, hierarchical included.
There are two kinds of rule. Built-in rules come with the data model and are mandatory. User-defined rules are the ones you, the developer, add, and they are optional, which means they may never get implemented. The book’s example: everyone “knows” no customer gets more than a 30% discount, but if that isn’t a rule in the database, nothing stops a program from giving 50%.
The book’s sample rules: two employees can’t share a social security number (entity integrity, built in); every employee must have a name; gender must be present and be ‘f’ or ‘m’; a credit limit must be 0–5000 or null (all user-defined). Try some changes against the chapter’s tables:
PostgreSQL · the rules declared in the database (course addition)
CREATE TABLE customer (
customer_no char(9) PRIMARY KEY, -- entity integrity
first varchar(40) NOT NULL,
last varchar(40) NOT NULL,
address varchar(120),
credit_limit numeric(8,2) CHECK (credit_limit BETWEEN 0 AND 5000) -- null passes
);
CREATE TABLE sale (
sale_no int PRIMARY KEY,
sale_date date NOT NULL,
customer_no char(9) REFERENCES customer (customer_no), -- null = cash sale
product_no char(8) NOT NULL REFERENCES product (product_no), -- referential integrity
qty int NOT NULL CHECK (qty > 0), -- a domain-style rule
amount numeric(10,2),
salesrep varchar(60)
);Rules belong as close to the data as possible, ideally inside the database, where a rogue program can’t bypass them. On the Web you also want instant feedback, so the book suggests sending some rules to the browser with the form (as JavaScript), as long as they come from one authoritative source.
Integrity rules guarantee data is valid, not that it is correct. “Brain Watlers” passes every rule as an employee name and is still a typo. The book’s defence is double entry or verification on top of the rules. Garbage in, garbage out.
Creating a database involves two jobs: defining its structures (tables, columns, types, keys) and filling it with data. The first produces metadata, data about the database. The DBMS files it in the catalog or data dictionary, which holds:
The dictionary can be huge (the book notes Oracle’s has hundreds of tables) and usually uses the same model as the data, so in an RDBMS it is just more tables. The DBMS uses it mainly to run itself.
PostgreSQL · reading the data dictionary (course addition)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'customer';Everything so far was logical. Performance, though, is decided at the physical storage level, which sits below the relational model; the model itself says nothing about it. The bottleneck is the disk. Memory answers in nanoseconds, disk in milliseconds, so RAM can be up to a million times faster. One slow read doesn’t matter; millions per second on a busy site do, and the book reminds you that users get twitchy after about three seconds.
Disks can’t be made much faster, and whole databases rarely fit in RAM (the book cites companies with 10 to 40 terabytes), so the goal is to touch the disk as rarely as possible. Data lives in files split into fixed-size blocks (or pages), each holding several records. A block is the smallest unit the DBMS reads or writes. There are three ways to find a record by a key value (here “key” means a search key, not necessarily the primary key):
Each node holds key values in sorted order and pointers down. In the second-level node “Chen | Fenton”, keys up to Chen are under the left pointer, keys after Chen up to Fenton under the middle one, and the rest (up to Harris) under the right. Leaf nodes point at the records on disk. Finding “Tudor” takes at most four disk reads (three nodes and the record); a small index like this sits in RAM, so usually just one. A table scan of these 27 records would average fourteen reads in the worst case of one record per block.
Because keys are sorted, B-trees are good at range retrievals (“surnames starting with C”): find the first match and scan right. Records with similar keys also tend to sit in the same block, so the next one is often already in memory. The index can be unique or non-unique; this one is non-unique (two Joneses, two Smiths).
The cost: inserts, deletes and key changes make the DBMS keep the tree in order, sometimes rebuilding part of it while nobody can use it. So index freely when data rarely changes, carefully when it changes a lot. Indexing primary keys is usually safe because they shouldn’t change (many products do it automatically), and tiny tables don’t need indexes at all: ten rows probably fit in one block.
Hashing decides where each record is placed, inside a reserved “hash space”, and spreads records out evenly. That makes single-record lookups on very large tables (millions of rows) as fast as it gets, but neighbouring keys end up in different blocks, so ranges are slow. The book’s verdict: hashing is a sledgehammer; for most databases indexes are enough. Compare all three:
PostgreSQL · the two index kinds (course addition; the book covers indexes in Chapter 8)
CREATE INDEX customer_last_idx ON customer (last); -- B-tree, the default
CREATE INDEX sale_no_hash ON sale USING hash (sale_no); -- equality lookups onlyWHERE last LIKE 'S%' or ORDER BY; and forgetting to index foreign key columns you join on.The chapter ends with one picture. Data and metadata sit at the core, wrapped in indexes and integrity rules; together they are one database. The DBMS manages it and possibly other databases, and all access by users and applications goes through the DBMS. Security, transactions, APIs and data manipulation are left out for later chapters. Click a layer.
The summary adds some perspective. The relational model beat the hierarchical and network models on flexibility and simplicity, yet both have come back in new forms: hierarchical as XML and network as objects. Mapping between tables and objects is fairly easy, but there was no single accepted object model (the ODMG’s attempt saw patchy adoption), and relational vendors were adding object and XML features anyway. The one big controversy inside the relational world has been nulls: one camp supports them, the other prefers default values.
JSONB columns hold tree-shaped data, with the same trade-offs the book describes. And the nulls debate never ended; it just moved into language design (strictNullChecks, Option types).| Formal term | Everyday term | Not the same as |
|---|---|---|
| Relation | Table | File (physical); relationship (a link) |
| Tuple | Row | Record (physical) |
| Attribute | Column | Field (physical) |
| Domain | Allowed values of a column | Data type (close, but narrower) |
| Method | One record | A range of keys | Best for |
|---|---|---|---|
| Sequential (table scan) | Half the file on average | Whole file | Tiny tables |
| Indexed (B-tree) | A few reads, often 1 | Good: keys sorted | Almost everything |
| Direct (hashing) | Usually 1 read | Poor: records scattered | Frequent single lookups on huge tables |
A crash course on Chapter 3 of Practical Database Design for the Web: where SQL came from, how to create and change tables, how to insert, update and delete rows safely, and how far one SELECT statement can take you. That means joins, sorting, grouping, aliases and set operations, finishing with a method for building queries one tested step at a time.
SQL (Structured Query Language) is the standard language for creating and using relational databases. The “query” part undersells it: SQL also creates and drops tables, changes data and manages permissions. It is not a general-purpose language like Java or PHP. Your application is written in one of those and sends SQL to the database.
The key property is that SQL is declarative. You describe the result you want, and the RDBMS works out how to get it. Contrast a loop in application code, where you spell out every step. SQL has well-known theoretical flaws, but it became good enough, and so entrenched, that even object and XML query languages of the time (OQL, SiXDML) were deliberately made to look like it.
An IBM research group created the language in 1974 as SEQUEL for its System R prototype; it was renamed SQL for legal reasons, which is why many people still say “sequel”. It shipped in IBM’s SQL/DS in 1981, and Oracle and Ingres adopted it too (Ingres after starting with its own language, QUEL). Incompatible dialects followed quickly, so ANSI began standardizing in 1982. The first ANSI standard came in 1986, essentially IBM’s dialect, and ISO accepted it in 1987.
| Standard | Keywords | Notes |
|---|---|---|
| SQL-86 | 81 | First ANSI standard |
| SQL-89 | 100 | |
| SQL-92 (“SQL2”) | 276 | About 500 pages. Levels: Entry, Intermediate, Full. Most products claim only Entry. The book uses SQL-92. |
| SQL:1999 (“SQL3”) | 473 | Over 2,100 pages in 13 parts; Part 2, SQL/Foundation, is the minimum for compliance |
| Oracle8i’s dialect | 478 | Shown for comparison |
Keyword counts from the book’s table. The book’s examples were tested in PostgreSQL.
SQL:1999 added a procedural language for stored procedures, functions and triggers (close to Oracle’s PL/SQL), structured and user-defined types, object storage, and, at last, a Boolean type. Every product speaks its own dialect: some version of the standard plus proprietary extensions, so always check your product’s documentation.
| Group | Purpose | Main statements |
|---|---|---|
| DDL: Data Definition Language | Create and change structure (the relational model’s structure part) | CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX |
| DML: Data Manipulation Language | Read and change data (the operations part) | SELECT, INSERT, UPDATE, DELETE |
| DCL: Data Control Language | Access, ownership and transactions (Chapters 7–8) | GRANT, REVOKE, COMMIT, ROLLBACK |
One web-specific point from the introduction: real web apps rarely send raw SQL strings. The book recommends wrapping SQL in stored procedures kept in the database, which saves bandwidth and avoids SQL being tampered with in transit (Chapter 8).
BOOLEAN is now ordinary in PostgreSQL and MySQL. On the security point, today the main defence against tampering is parameterized queries. Send the SQL with placeholders and the values separately, as every Node driver and ORM does, so user input can never become SQL. TLS protects the connection itself.How you create a database isn’t defined by the standard: it is “implementation-defined”. Access uses a menu, Oracle has CREATE DATABASE, PostgreSQL has the createdb command. Creating tables inside it is standard, with CREATE TABLE:
CREATE TABLE <name>
( <column-name> <data-type> [DEFAULT <value>] [<column-constraints>],
...
[<table-constraints>]
);Square brackets mark optional parts. Keywords are not case-sensitive (capitals are a readability convention), and a semicolon ends each statement. The chapter recreates the four tables from Chapter 2, starting with CUSTOMER:
CREATE TABLE statement. The annotations are added for this course.CREATE TABLE CUSTOMER
( CustomerNo CHAR(9),
First CHAR(20) NOT NULL,
Last CHAR(20) NOT NULL,
Address VARCHAR(100) NOT NULL,
CreditLimit SMALLINT CHECK (CreditLimit >= 0),
PRIMARY KEY (CustomerNo)
);Integers come in two sizes: SMALLINT (usually up to 32,767) and INTEGER (up to about 2.1 billion). NUMERIC holds exact decimals, FLOAT floating point, DATE dates, and character data is fixed-length CHAR or variable-length VARCHAR. A size in parentheses means different things per type:
| Type | What the size means |
|---|---|
CHAR(9) | Exactly nine characters; shorter values are padded with blanks |
VARCHAR(50) | Up to 50 characters |
NUMERIC(6,2) | Six digits in total, two of them after the point: −9999.99 to 9999.99 |
Based on the book’s table on p. 59. The book warns that some products read NUMERIC(6,2) as six digits before the point, against the standard.
Vendors offer supersets of these types (Oracle prefers its own NUMBER). The book’s rule of thumb for CHAR versus VARCHAR: fixed-length is faster but can waste space, so use CHAR for values of near-constant length (phone numbers) or heavily searched columns, and VARCHAR for longer, less-used text. Money should always be NUMERIC or INTEGER, never FLOAT, because floating point can produce slightly wrong totals.
CHAR no longer holds in PostgreSQL. Its docs say char(n) has no performance advantage and is usually the slowest of the three because of padding, so use varchar or text. The money rule still stands: in JavaScript, keep money as integer cents or a decimal library, never a float.A constraint is an integrity rule the RDBMS enforces. A column constraint is attached to one column and can refer only to that column. A table constraint can refer to any column, so a rule comparing two columns must be a table constraint.
PRIMARY KEY (cols)PRIMARY KEY (A, B, C). Adding NOT NULL as well is redundant, and the book notes it may even slow things down.FOREIGN KEY (cols) REFERENCES TNOT NULLCHECK (condition)UNIQUECONSTRAINT name …CONSTRAINT CreditCheck CHECK (CreditLimit >= 0), so you can drop it later. Otherwise the system invents a cryptic name.Nulls complicate true/false logic, so the book advises avoiding nulls in columns you will test in conditions. It admits CreditLimit is one of those and fixes it in section 3.3.
PRODUCT (Price NUMERIC(5,2) with CHECK ((Price >= 0) AND (Price <= 999.99))), SALE (a DATE column and two foreign keys), and EMPLOYEE. EMPLOYEE’s only candidate key in Chapter 2 was all its columns together, so the chapter adds a surrogate key, EmployeeID, and checks salaries with CHECK (Salary BETWEEN 10000 AND 50000).
CREATE TABLE statements (the book shows only the tables and SQL). ManagerID (accent) is added later with ALTER TABLE.CREATE TABLE SALE
( SaleNo SMALLINT,
SaleDate DATE NOT NULL,
CustomerNo CHAR(9), -- null = cash sale
ProductNo CHAR(8) NOT NULL,
Qty INTEGER NOT NULL CHECK (Qty > 0),
Amount NUMERIC(6,2) NOT NULL CHECK (Amount >= 0),
Salesrep VARCHAR(50),
PRIMARY KEY (SaleNo),
FOREIGN KEY (CustomerNo) REFERENCES CUSTOMER,
FOREIGN KEY (ProductNo) REFERENCES PRODUCT
);The book admits two shortcuts here. A sale can hold only one product (the proper header-lines structure for many products per sale comes in Chapter 4), and Salesrep should really be a foreign key to another table.
FLOAT for prices. Thinking CHECK (CreditLimit >= 0) forbids nulls (it doesn’t; add NOT NULL). Writing a two-column rule as a column constraint. Leaving constraints unnamed and then having to find the generated name before you can drop one.DROP TABLE CUSTOMER; deletes the table and all its data, usually without asking. Normally only the table’s creator may do it (security is in Chapter 8; backups, which you will want if it goes wrong, are in Chapter 9).
Structures change over time, and dropping and recreating a table to add one column is wasteful and means downtime. ALTER TABLE comes in three forms:
ADD [COLUMN] <column-definition> or ADD <table-constraint>. New columns start as nulls, or as the default if one is given, so a NOT NULL column can only be added with a default. Standard SQL-92 can’t add a column constraint to an existing column.ALTER [COLUMN] c SET DEFAULT v or DROP DEFAULT. Only defaults in the standard; many products also let you change constraints or types here.DROP [COLUMN] c (its data is lost) or DROP CONSTRAINT name (you need the name). You can’t drop a column that a constraint such as a foreign key still references.The chapter’s example: employees now manage other employees, so EMPLOYEE gets a ManagerID that points back into EMPLOYEE itself, a recursive (unary) relationship covered in Chapter 4. The book assumes everyone has a manager, and the person at the top manages themselves, so the default is 0 until real IDs are filled in with UPDATE.
ALTER TABLE EMPLOYEE ADD COLUMN ManagerID INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (ManagerID) REFERENCES EMPLOYEE;
ALTER TABLE EMPLOYEE ALTER COLUMN ManagerID SET DEFAULT 0;The book prints the first and third statements with the type at the end (… DEFAULT 0 NOT NULL INTEGER), and the third without SET. The versions above follow the standard order: name, type, default, constraints. In practice the foreign key also needs the real manager IDs in place first, since no employee has ID 0.
Then the CreditLimit fix. The business decides that “no credit” means 0, not null. Because SQL-92 can’t add NOT NULL to an existing column, the book needs a six-step detour through a temporary column. Step through it:
CreditLimit NOT NULL under SQL-92, applied to the five sample customers. New or changed columns are in the accent color.UPDATE CUSTOMER SET CreditLimit = 0 WHERE CreditLimit IS NULL; then ALTER TABLE CUSTOMER ALTER COLUMN CreditLimit SET NOT NULL;, and SET DEFAULT 0 for new rows. That is the shortcut the book wishes for. In a team, these changes live in migration files (Prisma, Drizzle, Knex) that are version-controlled and run in order.CREATE [UNIQUE] INDEX name ON table (columns); and DROP INDEX name;. These are technically outside the standard but universal. An index on CUSTOMER (Last, First) speeds up name searches; it can’t be unique, because names repeat. CREATE UNIQUE INDEX … ON EMPLOYEE (EmployeeID) works, but is usually redundant: many products index primary keys automatically. Indexes return in Chapter 8.
DROP TABLE or DROP COLUMN against production without a backup. Adding a NOT NULL column with no default to a table that already has rows. Trying to drop a column that a foreign key still references.The four everyday operations are Create, Read, Update and Delete, known as CRUD. Reading gets its own sections; the other three are simple.
INSERT INTO EMPLOYEE (EmployeeID, Name, Position, Location, Salary)
VALUES (617258, 'Jane Smith', 'Manager', 'London', 35000);
-- Key generated by the RDBMS: leave the column out
INSERT INTO EMPLOYEE (Name, Position, Location, Salary)
VALUES ('Heather Ralston', 'Manager', 'Melbourne', 33000);
-- An explicit null, and an ISO date in single quotes
INSERT INTO SALE (SaleNo, SaleDate, CustomerNo, ProductNo, Qty, Amount, Salesrep)
VALUES (12348, '2002-08-13', NULL, 'DHU69863', 50, 118.50, 'Sara Thompson');The column list is technically optional, but without it values go in table order, which breaks silently when columns are reordered or added. Always list the columns. You can leave a column out when the RDBMS generates it, or when you want its null or default value (after the 3.3 fix, an omitted CreditLimit becomes 0). Strings and dates use single quotes. Double quotes can mean something else entirely, and two single quotes inside a string stand for one apostrophe: 'Fred''s fish'. Date formats vary by product, so the book uses ISO dates (year-month-day).
UPDATE EMPLOYEE SET Salary = 37000 WHERE EmployeeID = 617258; -- by key: one row
UPDATE CUSTOMER SET CreditLimit = 0, Address = 'Iowa State Penitentiary'
WHERE CustomerNo = '4649-4673';
UPDATE PRODUCT SET Price = Price * 1.1; -- every row, from its own valueWithout a WHERE, UPDATE changes every row: UPDATE EMPLOYEE SET Salary = 37000; gives everyone the same salary. Filter on the primary key when you mean one row; WHERE Name = 'Jane Smith' could hit several people. SET c = NULL is the one place null goes with =. In comparisons you write IS NULL.
DELETE FROM PRODUCT WHERE ProductNo = 'AAD62726';
DELETE FROM EMPLOYEE WHERE Location = 'New York' AND Salary >= 40000;
DELETE FROM SALE WHERE CustomerNo IS NULL;
DELETE FROM PRODUCT; -- fails here: sales still reference productsThe last statement tries to delete every product. It should fail, because rows in SALE refer to products through a foreign key, and removing them would break referential integrity. The RDBMS refuses, as long as you declared the keys. Many products also offer a non-standard TRUNCATE, which empties a table much faster than DELETE because it doesn’t log each row. That makes it handy for resetting test data from a script.
WHERE filters rows: its true/false condition is evaluated for each row, and only rows where it is true go on to the operation. Combine sub-conditions with AND, OR and NOT, and use parentheses to make the grouping explicit. <> is standard “not equal” (some products also accept !=). Forgetting the WHERE on an UPDATE or DELETE is a classic beginner mistake. A wrong WHERE is just as bad. Test the condition as a SELECT first, on a backup with representative data, never on the live system.
DELETE example (p. 72). The two null tests are a course addition. Pick a condition to see each row’s result.Two corrections to the book’s worked examples. In its DELETE, the code prints ProductNo <> 'DHU69863', but the explanation and the row counts only work with =, which is what the tester uses. In the preceding UPDATE PRODUCT SET Price = Price * 2 WHERE ((QtyInStock > 5000) OR (Price <= 10)) AND (ReorderLevel < 1000), the book says four rows change. Because of the AND, only rows matching both parts change: two (AAD62726 and DHU69863).
WHERE CustomerNo = NULL, which matches nothing because any comparison with null is unknown, not true. Use IS NULL. Mixing AND and OR without parentheses. Putting a value in double quotes. Omitting the column list in INSERT.Reading data is called querying. When Codd defined the relational model he gave it two query languages. Relational algebra is a set of operators on tables, giving step-by-step instructions. Relational calculus, based on logic, describes what the result looks like and leaves the how to someone else. SQL’s SELECT is mostly algebra with some calculus. Some queries are awkward to express because of early design decisions, but everyday ones are easy.
SELECT <columns>
FROM <tables>
[WHERE <conditions>];
SELECT Name, Salary FROM EMPLOYEE WHERE Location = 'Paris'; -- Alex de Tocqueville, 21000Read a SELECT in this order: FROM (which tables), then WHERE (which rows), then the SELECT list (which columns). Because WHERE is processed before the output is shaped, it can test columns you don’t return. That suits the web, where you send only what the page needs.
SELECT * returns every column. The book calls it bad practice. It wastes server time and bandwidth on large tables, and it can be a security hole: a column added later for internal use suddenly shows up in every SELECT *. Name your columns.
The chapter then answers its list of plain-English questions:
-- How many grunge nuts do we have in stock?
SELECT ProductNo, Description, QtyInStock FROM PRODUCT WHERE ProductNo = 'FGE91822';
-- Any customers with a credit limit over $6,000? (none)
SELECT CustomerNo, First, Last FROM CUSTOMER WHERE CreditLimit > 6000;
-- Sales on 12 or 13 August 2002: three equivalent filters
WHERE SaleDate = '2002-08-12' OR SaleDate = '2002-08-13'
WHERE SaleDate BETWEEN '2002-08-12' AND '2002-08-13' -- inclusive range
WHERE SaleDate IN ('2002-08-12', '2002-08-13') -- value in a listBeing able to write a query several ways is one of SQL’s strengths. The book points out the downside: it makes the optimizer’s job of finding the fastest plan harder. Totalling the revenue waits for aggregation (3.7). Then comes “all sales to Amelia Waverley”. Her name is in CUSTOMER and her sales are in SALE, so the obvious move is to list both tables in FROM. Doing that without a join condition gives seven rows, five of which aren’t hers, which leads straight into joins.
select option on hot paths.Listing several tables in FROM with no further instructions produces the Cartesian product: every row of one table combined with every row of the other. It sounds odd, but it is the raw material that join conditions and filters then cut down.
A join combines related rows from two tables, normally over a foreign key/primary key link. The chapter covers three kinds.
FROM CUSTOMER NATURAL JOIN SALE matches rows on every column the two tables share by name and type, here CustomerNo. It is fragile. Two tables can share a column name like ItemNo without it meaning the same thing, and the join changes silently when columns are added. Many products don’t support it. The book puts it in the same “potentially dangerous” category as SELECT *, and suggests giving columns descriptive names so that same-named columns really mean the same attribute.
Say exactly how rows match. There are two syntaxes:
-- Pre-SQL-92: join condition in WHERE, alongside the filters
SELECT SALE.SaleNo, SALE.SaleDate, SALE.Amount, SALE.Salesrep
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNo = SALE.CustomerNo
AND CUSTOMER.First = 'Amelia' AND CUSTOMER.Last = 'Waverley';
-- SQL-92: JOIN … ON in FROM; WHERE only filters
SELECT SALE.SaleNo, SALE.SaleDate, SALE.Amount, SALE.Salesrep
FROM CUSTOMER JOIN SALE ON CUSTOMER.CustomerNo = SALE.CustomerNo
WHERE CUSTOMER.First = 'Amelia' AND CUSTOMER.Last = 'Waverley';
-- Three tables: sales on 14 August with product and customer names
SELECT SALE.SaleNo, SALE.Qty, SALE.Amount, PRODUCT.ProductNo, PRODUCT.Description,
CUSTOMER.First, CUSTOMER.Last
FROM (CUSTOMER JOIN SALE ON SALE.CustomerNo = CUSTOMER.CustomerNo)
JOIN PRODUCT ON SALE.ProductNo = PRODUCT.ProductNo
WHERE SALE.SaleDate = '2002-08-14';CUSTOMER.CustomerNo is a qualified column name: table plus column. It is required when both tables have the column, and the book adopts it everywhere as documentation for whoever maintains the code next. The SQL-92 form keeps join conditions apart from filters and is easier to read. In the book’s day it was less widely supported (Oracle only added it in 9i). In a multi-table SQL-92 join, each JOIN joins onto the result so far, and with outer joins that order can matter.
In the book’s side-by-side table on p. 80, the “SQL-92 style” and “Pre-SQL-92 style” column headings are swapped. Go by the syntax: the comma-and-WHERE form is the older one.
“Show all products, and the value of each sale they’ve been involved in, if any.” An ordinary JOIN returns only the three products that have sold, because the others fail the join condition. All the joins so far are inner joins. An outer join also keeps rows that have no match and fills the other table’s columns with nulls in the result. Those nulls exist only in the temporary result, so the NOT NULL constraints on SALE aren’t violated.
SELECT PRODUCT.ProductNo, PRODUCT.Description, SALE.SaleNo, SALE.Amount
FROM PRODUCT LEFT OUTER JOIN SALE ON PRODUCT.ProductNo = SALE.ProductNo;Left keeps unmatched rows from the table left of JOIN, right from the table on the right, full from both. OUTER is optional (RIGHT JOIN means RIGHT OUTER JOIN). Support varied in the book’s day: some products had no outer joins, some lacked full outer joins, and Oracle8i used its own WHERE PRODUCT.ProductNo = SALE.ProductNo (+). There, the (+) marks the side whose rows are not preserved.
JOIN syntax since 9i, so write LEFT JOIN. MySQL still has no FULL OUTER JOIN; people emulate it with a UNION of a left and a right join.Joins cost time on large tables, and slow pages lose customers. Normalization is what makes joins necessary, and redundant data has nasty side effects, so the book looks for a middle path. Data that rarely changes once written, such as completed sales or a student’s finished courses, can have its joins pre-computed. Run the join once and store the result in a separate, denormalized table, then refresh it regularly (triggers, Chapter 8). That makes order-history pages cheap. The book calls this a materialized view, and it is only acceptable when users don’t need up-to-the-second data.
-- SALE_HISTORY: one wide row per sale (columns from SALE, PRODUCT, CUSTOMER), PK SaleNo
INSERT INTO SALE_HISTORY (SaleNo, SaleDate, Qty, Amount, ProductNo, Description, Price,
CustomerNo, First, Last, Address)
SELECT SALE.SaleNo, SALE.SaleDate, SALE.Qty, SALE.Amount,
PRODUCT.ProductNo, PRODUCT.Description, PRODUCT.Price,
CUSTOMER.CustomerNo, CUSTOMER.First, CUSTOMER.Last, CUSTOMER.Address
FROM SALE, PRODUCT, CUSTOMER
WHERE SALE.ProductNo = PRODUCT.ProductNo
AND SALE.CustomerNo = CUSTOMER.CustomerNo; -- no customer filter: everyone’s historyINSERT … SELECT inserts a query’s result instead of a VALUES list. Some products can create the table straight from a SELECT, which was non-standard then. Some use SELECT INTO for this, which means something different in standard SQL.
CREATE MATERIALIZED VIEW sale_history AS SELECT … and REFRESH MATERIALIZED VIEW sale_history;. CREATE TABLE … AS SELECT is standard and widely supported. Note that the book’s inner join drops cash sales, which have no customer. That is fine for a customer’s order history.SELECT <columns>
FROM <tables>
[WHERE <conditions>]
[GROUP BY <columns> [HAVING <conditions>]]
[ORDER BY <columns>];The relational model gives rows no order, so without ORDER BY they come back in whatever order is convenient, often insertion order. ORDER BY EMPLOYEE.Position sorts ascending (ASC is the default). ORDER BY EMPLOYEE.Salary DESC sorts descending. ORDER BY EMPLOYEE.Position DESC, EMPLOYEE.Salary sorts by position, then by salary within each position. SELECT DISTINCT SALE.SaleDate FROM SALE returns each of the three dates once instead of seven rows, which also means less output to send.
SQL has five: COUNT, SUM, MIN, MAX, AVG. Each takes a column or expression and returns one value for the set of rows that passed WHERE.
SELECT COUNT(*), SUM(Salary), MIN(Salary), MAX(Salary), AVG(Salary) FROM EMPLOYEE;
SELECT SUM(PRODUCT.QtyInStock * PRODUCT.Price) FROM PRODUCT; -- inventory value
SELECT COUNT(DISTINCT Position) FROM EMPLOYEE; -- 3: Programmer twiceCOUNT(*) counts rows, without looking at values. All the other aggregates ignore nulls. You can’t mix aggregates with ordinary columns in the SELECT list (SELECT COUNT(*), CustomerNo FROM CUSTOMER is an error) unless you group.
A precision note. The book suggests COUNT(DISTINCT col) both for counting distinct values and for not counting nulls. For the second case, plain COUNT(col) is enough: it counts non-null values, duplicates included.
GROUP BY splits the rows into groups that share a value, and the aggregate runs once per group, giving one output row each. SELECT Position, COUNT(*) FROM EMPLOYEE GROUP BY Position gives three rows. Grouping by Position, Location gives four, one per distinct combination. Any ordinary column in the SELECT list must also be in GROUP BY. Aggregates can appear only in SELECT and HAVING.
To list reps with over $2,000 of sales, WHERE SUM(Amount) > 2000 fails: WHERE sees single rows, not groups. HAVING is the group-level filter, applied after the groups are built. Step through the evaluation:
SELECT SALE.Salesrep FROM SALE GROUP BY SALE.Salesrep HAVING SUM(SALE.Amount) > 2000, clause by clause. Click a clause or use Next.The book describes grouping as sorting by the group column, then splitting. Modern optimizers may hash instead, but the result is the same. Like joins, a frequently used GROUP BY on a large, fairly static table can be pre-computed into its own table.
WHERE. A non-aggregated column in SELECT that isn’t in GROUP BY. Older MySQL allowed this and returned an arbitrary value. Relying on row order without ORDER BY.Two hyphens start a comment (-- like this) that runs to the end of the line. Comments matter most in code kept in the database as stored procedures. The SELECT list can hold expressions and constants as well as columns. Each one becomes a new output column:
SELECT PRODUCT.Description, PRODUCT.QtyInStock - PRODUCT.ReorderLevel FROM PRODUCT;
SELECT SALE.Salesrep, 'has sold to', CUSTOMER.First, CUSTOMER.Last
FROM SALE JOIN CUSTOMER ON SALE.CustomerNo = CUSTOMER.CustomerNo;FROM SALE AS S JOIN CUSTOMER AS C ON S.CustomerNo = C.CustomerNo saves typing (AS is optional). An alias is really a table variable that ranges over the table’s rows, not a copy of the table. That matters for a self-join, where one table plays two roles. To list employees with their managers, EMPLOYEE must appear as both employee and manager. Without aliases the RDBMS rejects the duplicate table name.
SELECT EMP.Name, MGR.Name
FROM EMPLOYEE EMP JOIN EMPLOYEE MGR ON EMP.ManagerID = MGR.EmployeeID;ManagerID to the matching EmployeeID. Rows and result as in the book.Columns can be renamed too, which helps when output names would otherwise be blank or ambiguous. SELECT AVG(PRODUCT.Price) AS AveragePrice, AVG(PRODUCT.QtyInStock) AS AverageQty FROM PRODUCT labels two columns that would otherwise both just say AVG. Likewise (QtyInStock - ReorderLevel) AS QtyRemaining.
UNION, INTERSECT and EXCEPT (difference) combine the rows of two queries like set union, intersection and difference. Most products had UNION and INTERSECT; fewer had EXCEPT. Both queries must be compatible: the same number of columns with matching types (CHAR and VARCHAR count as the same, and sizes are usually ignored).
The book’s example: count each customer’s sales, sorted by name. An outer join from SALE leaves cash sales as a row of nulls, which application code then has to check for. Instead, union two queries, one for real customers and one that fills the cash-sale row with constants:
SELECT CUSTOMER.CustomerNo, CUSTOMER.First AS FirstName,
CUSTOMER.Last AS LastName, COUNT(*) AS NumSales
FROM SALE JOIN CUSTOMER ON SALE.CustomerNo = CUSTOMER.CustomerNo
GROUP BY CUSTOMER.CustomerNo, CUSTOMER.First, CUSTOMER.Last
UNION
SELECT ' ', ' ' AS FirstName, 'Cash sale' AS LastName, COUNT(*) AS NumSales
FROM SALE WHERE SALE.CustomerNo IS NULL
ORDER BY LastName, FirstName; -- one ORDER BY, after the last queryThe columns are renamed consistently in both halves so that the final ORDER BY can refer to them. Result: Adams 1, Cash sale 1, Gonzales 1, Johnston 1, Jones 1, Waverley 2.
COALESCE(CUSTOMER.Last, 'Cash sale') on the outer-join version to replace the null with a label in one query. Also, UNION removes duplicate rows; use UNION ALL when you know there are none, to skip that work.Build a query in the same order you read one, and test every step:
SELECT.The steps feed back into each other, so expect to loop. Know the answer you expect before you run the query, use good test data, and test complex conditions one part at a time before combining them. The book credits this incremental approach to an Oracle Magazine article (July/August 2000).
Management’s question: total value of sales to non-credit customers, day by day, during August 2002.
FROM SALE JOIN CUSTOMER ON …. Test it: there are seven sales, but only six rows come back. The cash sale has a null CustomerNo and fails the join. Switch to SALE LEFT JOIN CUSTOMER: seven rows.SaleDate BETWEEN '2002-08-01' AND '2002-08-31'. All seven pass, since every sale is in August, so add some other dates if you want a real test.CreditLimit null in the result, so the test is (CreditLimit = 0) OR (CreditLimit IS NULL): five rows.AND, keeping the parentheses around the OR.GROUP BY SaleDate, and “total” means SUM(SALE.Amount).SELECT SALE.SaleDate, SUM(SALE.Amount) AS DailyTotal
FROM SALE LEFT JOIN CUSTOMER ON SALE.CustomerNo = CUSTOMER.CustomerNo
WHERE ((CUSTOMER.CreditLimit = 0) OR (CUSTOMER.CreditLimit IS NULL))
AND (SALE.SaleDate BETWEEN '2002-08-01' AND '2002-08-31')
GROUP BY SALE.SaleDate;| SaleDate | DailyTotal | From sales |
|---|---|---|
| 2002-08-12 | 167.65 | 12346 |
| 2002-08-13 | 5120.25 | 12347, 12348 (cash) |
| 2002-08-14 | 2327.34 | 12349, 12350 |
Totals as in the book; the “From sales” column is added. The book’s data here treats Richard Johnston as still having a credit limit of 1000, even though an earlier UPDATE example set it to 0. With that update applied, sale 12345 (23.95) would join the 12 August total.
The last step is to generalize it. Wrap the query in a stored procedure that takes the date range as parameters. The web app then makes one short call instead of sending a long SQL string (Chapter 8).
WHERE SaleDate BETWEEN $1 AND $2 with the dates passed as values, never pasted into the string. Write a test with known data and a known expected total, as the book recommends.| Task | Statement | Watch out for |
|---|---|---|
| Create / remove a table | CREATE TABLE / DROP TABLE | Drop deletes all data without asking |
| Change a table | ALTER TABLE … ADD | ALTER | DROP | NOT NULL columns need a default |
| Add rows | INSERT INTO t (cols) VALUES (…) or … SELECT | Always list the columns |
| Change rows | UPDATE t SET c = v WHERE … | No WHERE = every row |
| Remove rows | DELETE FROM t WHERE … | Blocked by foreign keys still referencing them |
| Read rows | SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY | Name columns; avoid SELECT * |
| Combine tables | JOIN … ON, LEFT | RIGHT | FULL OUTER JOIN | Missing condition = Cartesian product |
| Combine queries | UNION, INTERSECT, EXCEPT | Queries must be compatible |
A crash course on Chapter 4 of Practical Database Design for the Web: why you model before you build, how to read a crow’s-foot ER diagram, the five relationship structures that keep coming back, the three kinds of integrity rule a database can enforce for you, when to bend normalization for speed, and how CASE tools turn diagrams into databases.
A data model is an abstract picture of the part of the real world your application cares about. Modeling means keeping the information that matters, dropping what doesn’t, and making the relationships between things explicit. Once you have the model, you generate the tables in your RDBMS from it.
The chapter’s argument for doing this on purpose: a web application leans entirely on its data, and the graphical table editors that ship with databases tempt you to design “by hand”, one table at a time. That works for a handful of tables. Beyond that you need an overview of the whole system, and a good design is what lets a small database keep growing as requirements change.
You are building a model anyway, even if only in your head. Writing it down lets you share it with other developers, with the customer, and with yourself after the weekend. Like an architect’s drawing, one misplaced item can sink the project later, and it is far cheaper to fix on paper than months after the site goes live.
Modeling is iterative. Customers rarely know exactly what they need; showing them the model helps them understand their own problem and explain it better, and modeling turns up the questions you didn’t know to ask. Click each phase below.
Two more payoffs. A model is independent of the database product, so analysis results (storage size, target response times) can inform which platform you pick, and you can prototype on a desktop database and move to a production server later. And when you inherit a legacy system, extracting its structure into a model explains the original design far better than pages of DDL.
The book uses Entity Relationship (ER) modeling, the most common approach, which has three building blocks. Its first example is an online bookstore that stores books and their publishers. The diagrams use crow’s-foot notation and were drawn in Microsoft Visio; other tools draw the symbols a little differently, but the ideas are the same.
PK, or FK plus a number that just tells foreign keys apart), the name, and the data type. Primary-key attributes sit in their own section at the top. Bold attributes may not be null.Read the line from each side: a book must have exactly one publisher (two bars at the Publishers end), and a publisher may have zero or many books (circle and fork at the Books end). The foreign key PublisherID lives on the many side.
Notice something missing. The bookstore wanted to know how many books each publisher has in the catalog, but there is no attribute for it. Storing the count would be redundant: you can get it from the books themselves with COUNT (Chapter 3). Section 4.7 revisits this trade-off.
Most models are assembled from a few recurring patterns of related tables, named by the cardinality of their relationships. Learn to spot them and you have a toolkit for most real-world data. From most to least common, the chapter covers one-to-many, many-to-many, one-to-many recursive, many-to-many recursive and one-to-one. Most examples come from a small hypothetical sports-clothing e-commerce store, whose full diagram ends this section.
One row in a parent table relates to many rows in a child table, but each child row has only one parent. You’ll find it in nearly every database. The child stores the parent’s key in a foreign-key column. The book’s first example is Categories and Products: each product’s CategoryID says which category it sits in, and the Tracksuits category holds three products. Strictly the many end can be zero, one or many, but nobody says “one-to-zero-one-or-many”.
A student takes many classes and a class holds many students. People grasp this easily, but a relational database can’t store it directly. The only way to do it in two tables is a repeating group of foreign-key columns (Class1, Class2…), which caps how many you can have and isn’t a true many-to-many at all.
The fix is a third table, an associative entity (also called a joining table). It holds one row per pairing and turns the many-to-many into two one-to-many relationships. In the store, OrderItems links Orders and Products. Its key is the pair of foreign keys, and it carries facts about the pairing itself: the quantity ordered and the price actually paid at the time.
Often the associative table has nothing but the two keys. The book’s example links User and Group through UserGroup, a name made by joining the two table names, a common convention for pure link tables. Here it is as SQL (a course addition; the book shows only the diagram):
PostgreSQL · a pure link table
CREATE TABLE user_group (
user_guid UUID NOT NULL REFERENCES "user"(user_guid),
group_id INT NOT NULL REFERENCES "group"(group_id),
PRIMARY KEY (user_guid, group_id) -- a user joins a group once
);One-to-many or many-to-many? A many-to-many structure can hold one-to-many data, but not the reverse. To decide, say the relationship out loud in both directions and put both sentences to the customer as questions: Can a user belong to many groups? Can a group contain many users? Two yeses mean many-to-many.
product1, product2…) or a comma-separated list instead of a link table. Forgetting that the link table is a good home for facts about the pairing: the price paid belongs on OrderItems, not Products, because the product price will change.A recursive (or reflexive) relationship relates a table to itself. The classic use is a tree. Stores nest categories inside categories; on Amazon this book might sit under Computers, then Databases, then Database Design. Give Categories a ParentCategory column that is a foreign key to its own CategoryID. Rows with no parent (null) are the top level; every other row points at its parent, so the tree can be as deep as you like.
The catch: the database can’t protect the shape of the tree. Setting a node’s parent to one of its own descendants (Sweatshirts under College Teams) satisfies the foreign key but cuts that branch off into a loop. So check for this before inserting or updating rows, either in application code or, better, with a trigger (Chapter 8) that runs inside the database on every change.
The same pattern models a management hierarchy: an Employees table whose Manager column points at another employee. In the book’s data, Simon Smit is the CEO with no manager; James Bacon (footwear) and Robert Jones (clothing) report to him; Richard Smith, a shoes warehouse storeman, reports to James Bacon. The recursive foreign key must allow null: the top of the tree has no parent, and any made-up non-null value would point at a row that doesn’t exist, breaking referential integrity (section 4.6).
Walking up from a node to the root is easy: follow the parent pointers. Walking down to build the whole tree (say, a menu) is harder. The book sketches a recursive function: fetch the top-level rows, and for each row print it, query its children, and call the function again one level deeper, passing the depth for indentation. To show one branch, start from a single category instead.
WHERE ParentCategory IS NULL.The book warns that this approach can fire tens or hundreds of queries per page view, which hurts under load. Its alternatives: cache the tree in a tree-friendly format (it suggests XML), or fetch the rows flat in one query and build the tree in code. The second is still the everyday answer:
TypeScript · one query, tree built in memory
type Cat = { categoryId: number; name: string; parentCategory: number | null };
type Node = Cat & { children: Node[] };
function buildTree(rows: Cat[]): Node[] {
const byId = new Map(rows.map(r => [r.categoryId, { ...r, children: [] as Node[] }]));
const roots: Node[] = [];
for (const n of byId.values()) {
const parent = n.parentCategory == null ? undefined : byId.get(n.parentCategory);
(parent ? parent.children : roots).push(n); // null parent = top level
}
return roots;
}WITH RECURSIVE t AS (SELECT …, 0 AS depth FROM categories WHERE parent_category IS NULL UNION ALL SELECT c.…, t.depth + 1 FROM categories c JOIN t ON c.parent_category = t.category_id) SELECT * FROM t;Here a table relates to itself through an associative table. It fits anything that is an aggregate of things of the same kind, such as a computer built from other products (a parts explosion or bill of materials). The store uses it for bundles: ProductBundles holds pairs of product IDs, the bundle and a product inside it. The trick to reading it is to imagine the Products table drawn twice. Toggle the views below.
The Tracksuit Combo Deal (product 5) is a product like any other, so it has a price and can go in an order, but its ProductBundles rows say it contains products 3 and 4. Because a bundle is just a product, you can even bundle bundles, which is overkill for a clothing store but handy for, say, a computer hardware company. As with trees, block circular references: a bundle must never contain itself, directly or further down.
Not common, and it comes in two forms with different design consequences.
Actual one-to-one. Two real things that pair exactly: each department has one manager and each manager runs one department, recorded by Departments.DepartmentManager pointing at an employee. In the data it looks just like one-to-many, except the child side never has more than one row. The book’s advice is to be wary: one-to-one gives no efficiency gain over one-to-many and is less flexible, so check with the client, and consider modeling it as one-to-many even if the data is usually one-to-one.
Sub-type/super-type. The more common form: entities share some attributes but each kind also has its own. Which side gets the foreign key depends on how many sub-types there are.
EmployeeType column to Employees, copy it into each sub-type table with a CHECK that fixes its value ('C' or 'S'), and make the foreign key the pair (EmployeeID, EmployeeType); or use a trigger.Put together, the store’s diagram uses every structure in this section. Hover each relationship to see which one it is.
Integrity constraints are the rules that keep the data valid by restricting what applications may do. Put them in the database rather than the application layer and they hold whichever program connects, which matters most when you don’t control every application or developer that will use your data. There are three kinds:
Other columns may repeat, but the primary key must be unique, because every relationship to the row goes through it. For a single-column key: values are unique and never null. For a composite (concatenated) key: the combination is unique and no part is null. The ban on null follows from Chapter 2: null isn’t a value and null doesn’t equal null, so you couldn’t compare null keys to prove uniqueness.
In a composite key no single column has to be unique. In the book’s small catalog the key is Author + Title + Publisher:
| Author | Title | Publisher | Price |
|---|---|---|---|
| J. Smith | Advanced Spoonbending | XYZ Press | 23.95 |
| J. Smith | Elementary Spoonbending | XYZ Press | 23.95 |
| R. Jones | Top Golf Holes | ABC Books | 18.5 |
| S. Robinson | Top Golf Holes | Greenkeeper Publishing | 22.95 |
From the book’s table on p. 117.
Keys made of data that means something to users are intelligent or natural keys. They are a bet that the data will always be unique. Suppose XYZ Press releases a hardback Advanced Spoonbending at $53.95: same author, title and publisher, so the key breaks, and you must hunt for another non-null column to add, with no guarantee it won’t break too. Even ISBNs fail: old books and some publishers have none, and if the shop starts selling second-hand copies alongside new ones, the ISBN repeats.
The usual argument for natural keys is that they make ad-hoc reporting queries easier to read. The book points out that web applications almost never offer end users ad-hoc queries, so that argument is weak on the web.
A surrogate key is an artificial unique value. It gives you a single-column key where you would otherwise need a composite one, which is more efficient. You can generate it in the database or in your application. Two kinds:
autonumber, SQL Server identity, MySQL auto_increment; Oracle uses a sequence object; PostgreSQL offers sequences or the serial type. Chapter 6 uses these.uniqueidentifier; Access has an autonumber set to Replication ID; Oracle exposes SYS_OP_GUID(); PostgreSQL needed an extension at the time.Use GUIDs when keys must be unique beyond one database, or to stop people guessing keys. If a profile page is view_user.asp?userid=463, anyone can script a loop over every ID; a GUID makes even one guess practically impossible. A side note in the book adds that some developers use timestamps as keys, which fails if two rows arrive within the timestamp’s precision.
Not every table needs one. The UserGroup link table already has a perfect key, the pair of foreign keys (themselves surrogates), and a user can be in a group only once. An extra ID column would add an index, and every index slows inserts (Chapter 8). Natural keys can also save joins: if Customers references a Countries lookup by CountryName, the name is already in the customer row.
SQL · natural key: no join needed
SELECT CustomerName, CountryName FROM Customers;SQL · surrogate key: join to get the name
SELECT Customers.CustomerName, Countries.CountryName
FROM Customers
INNER JOIN Countries ON Customers.CountryID = Countries.CountryID;The book’s verdict: use intelligent keys sensibly where they avoid overhead, but as a general rule surrogate keys suit web databases well. It also mentions a naming habit: end surrogate key columns in ID or GUID according to their type.
GENERATED ALWAYS AS IDENTITY, version 10) and a built-in gen_random_uuid() (version 13). Random UUIDs scatter inserts across an index; time-ordered UUIDv7 keeps them unguessable-ish while inserting in order.The domain of an attribute is the full set of values it may hold. You discover domains by asking the customer questions like: do we need “unknown” as a gender? How long can a product description be? To how many decimal places is a stock price stored?
The worked example is an online stock-portfolio system: Customers hold many EquitySecurities and a security is held by many customers, so SecurityHolding resolves the many-to-many. The business rules: store each customer’s age; track whether a customer is a credit risk; let customers record optional details for each holding; and every security has a unique ticker code of exactly four characters.
Most RDBMSs don’t support domains as such. A domain is the sum of several column settings: data type, length, constraints, optionality and default value.
The most important setting. Choosing one balances three goals: efficiency (the smallest type that leaves room for surprises), a format the application can use directly without converting, and flexibility for likely future needs. Names vary by product, so check your DBMS’s documentation; some modeling tools let you design with generic types and pick the platform’s type when generating the database.
| Type | What it is | Watch out for |
|---|---|---|
INTEGER | Whole numbers; usually 32-bit (about ±2.1 billion), with smaller and larger variants | Match your language’s integer size: the book’s example is VBScript’s 16-bit Integer overflowing above about 32,000 |
CHAR(n) | Fixed length; short values are padded to n (plain CHAR is one character) | Wastes space when lengths vary a lot |
VARCHAR(n) | Variable length up to n; stores only the actual data | Growing a value may need new space on disk; older platforms capped n at 255 |
DECIMAL(p,s) | Exact number; precision p digits, s after the point (157.4568 is p = 7, s = 4) | Pick p and s from real requirements |
FLOAT, REAL, MONEY… | Floating-point or platform-specific numeric types | Sizes differ between products (the book contrasts MONEY in PostgreSQL and SQL Server); floats bring rounding errors |
DATETIME | Date and time together; the standard also defines separate DATE and TIME | Usually the best choice when you need either |
Summarized from the book’s type descriptions, pp. 123–124.
The simple choices. Names are VARCHAR; SecurityCode and MiddleInitial are CHAR. The author’s length rules: a CHAR is as long as its longest value, and a VARCHAR gets the next size up from a fixed ladder (20, 50, 100, 250, 1000, 2000), so you can recall any column’s limit while coding. The phone number is CHAR too, because the application never does arithmetic on it; text keeps codes like *999 or 2377544#44 possible. Rule of thumb: use numeric types only when you will treat the data as a number.
The harder choices needed another round of questions to the client, which is the iterative cycle from 4.1 in action. Credit risk turned out to be yes/no; prices need four decimal places with room above a few thousand dollars; purchases need the time to the minute. And “store the customer’s age” hides a trap.
Optionality decides whether a column may be null. Platforms implement it differently, and a modeling tool can handle that for you; you only decide which columns are optional. Null is useful as “no value was given”, but remember it can’t be compared with ordinary operators. In the example, the three detail columns of SecurityHolding allow null so you can tell when a customer chose not to enter them, and MiddleInitial allows null so people without one don’t get a padded blank.
A default value fills a column when an insert doesn’t mention it, and some databases accept a function, such as the current date. Use defaults with care: where “not entered” matters, null is better. A default of 0 for PricePaid would be wrong, because 0 is a real possibility (shares received as part of a pay package). The example uses one default: CreditRisk starts at 0, so the system is never unsure whether a risk exists.
A check constraint (or check clause) is a rule the database tests on every insert and update: SecurityCode must be letters, PricePaid must not be negative. Ideally you set it in the modeling tool so the tool creates it. Here is the example table written out in full (a course addition in PostgreSQL syntax; the book shows the constraint in SQL Server form):
PostgreSQL · the holding table with its domain rules
CREATE TABLE security_holding (
holding_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(customer_id),
security_id INT NOT NULL REFERENCES equity_securities(security_id),
number_held INT, -- optional: NULL = not entered
price_paid DECIMAL(9,4), -- e.g. 3.6525; no float rounding
date_purchased TIMESTAMP, -- to the minute
CONSTRAINT price_paid_chk CHECK (price_paid >= 0 OR price_paid IS NULL)
);
-- and on customers: credit_risk BOOLEAN NOT NULL DEFAULT falseOR PricePaid IS NULL the check would reject nulls. In standard SQL (including SQL Server and PostgreSQL) a CHECK fails only when its condition is false. NULL >= 0 is unknown, so the row is accepted anyway. The extra clause is harmless and states the intent, but nullability is still controlled by NOT NULL.number is exact only up to 253, so Node drivers such as node-postgres return BIGINT and DECIMAL values as strings. Decide on purpose whether to parse them, use BigInt, or use a decimal library for money.Referential integrity (RI) keeps relationships valid: every foreign key is either null or matches a primary key in the related table. The database enforces it with a foreign-key constraint. Diagram tools and database editors usually write these for you; the DDL varies by platform. The book’s example is a SQL Server link table, PropertyFeature, with a composite primary key and two foreign keys.
RI blocks three problems. Each would leave an orphaned row: a child whose foreign key points at no parent.
In practice: insert the parent first (a customer before their holdings); delete the children before the parent; and you can never change a referenced primary key, because the parent can’t change while children point at it and the children can’t point at a value that doesn’t exist yet. You could write code for all of this, but it is better to tell the database what to do.
A referential action tells the DBMS what remedial step to take when a change to a parent would break the rules. It can’t let you break them, only repair things automatically. The refusing behavior above is itself an action, RESTRICT.
| Action | Update parent key | Delete parent row |
|---|---|---|
RESTRICT (default in most RDBMSs) | Refused | Refused |
CASCADE | Children’s foreign keys change to the new value | Children are deleted too |
SET NULL | Children’s foreign keys become null | Children’s foreign keys become null |
Based on the book’s table on p. 130, reworded.
Some products leave out SET NULL because it is seen as less than ideal; a trigger can imitate it. Try every combination below.
Choosing the action is a design decision. CASCADE fits true parent–child ownership: deleting a folder deletes its files, in one statement instead of three (check for children, delete them, delete the parent). It is wrong when the children have value of their own. If an employee manages many projects, ON DELETE CASCADE would wipe the projects when the employee leaves; RESTRICT forces you to reassign or delete them deliberately.
PostgreSQL · choosing actions per relationship
CREATE TABLE orders (
order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INT NOT NULL
REFERENCES customers(customer_id) ON DELETE RESTRICT, -- keep order history
order_date TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE, -- lines belong to the order
product_id INT NOT NULL REFERENCES products(product_id),
quantity INT NOT NULL,
purchase_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);CASCADE on everything because it makes deletes “just work”. Using SET NULL on a foreign key declared NOT NULL. Relying on application code for RI when other programs, scripts or admins also write to the database.Back to the publisher’s book count from 4.2. Counting matching rows every time you show a publisher is correct but costs work on each read; storing the count is redundant but instant. Web databases often carry a much higher read load than traditional systems, so sometimes the redundant, denormalized form wins.
That should be the exception. The book’s order of operations: build a properly normalized design first, test it under a realistic production-like load, and only then denormalize where the measurements say so. For web projects that often means deciding after development is complete.
Denormalized models make reads cheaper and writes dearer, which suits most sites, where selects vastly outnumber inserts and updates. When the summary is costly to maintain, don’t update it during the user’s request. The book’s example is “customers who bought X also bought…”: rebuild those summary tables in the background or as a nightly or weekly batch job. Chapter 8 returns to these trade-offs with indexing.
REFRESH MATERIALIZED VIEW), or a cache such as Redis in front of the database. The rule hasn’t changed: measure first.CASE (Computer Aided Software Engineering) tools let you draw the model in software and then generate the database from it. The book lists options from free to many thousands of dollars: Dia (free); Datanamic DeZign and Model Creator ERCreator (low end); Charonware Case Studio (low to mid); Oracle Designer and Microsoft Visio (mid); Quest Q Designer, Sybase PowerDesigner, Embarcadero ER/Studio and Computer Associates ERwin (high end). Whichever you pick, make sure it supports your target databases and really does entity modeling.
The demonstration uses Visio 2002 and its Database Model Diagram template, whose window has four parts:
Forward engineering turns the model into a database: the tool validates the model (at minimum, that each foreign key’s type matches its primary key) and either writes a DDL script of CREATE TABLE statements, builds the database directly through a driver for your DBMS, or both. Visio can also merge changes into an existing database. This removes the old double work of drawing a model and then retyping every table by hand, which was the main reason people skipped modeling.
Reverse engineering goes the other way: point the tool at an existing database and get a model and diagram back, to document a legacy system or a project that started in the database and outgrew it. It also rescues models that drifted, since developers inevitably change the live database without updating the diagram. In the book’s walk-through, a phone-number column added to Publishers in Access shows up in the reverse-engineered diagram. The Visio wizard steps: choose the data source, the object types (procedures and triggers on bigger platforms), the specific objects, and whether to add them to the current diagram. For more than about 30 tables, say no and place them on several diagrams yourself; Visio draws the relationship lines when you drag tables in.
Round-trip engineering combines both and tracks changes over the life of the database. ER diagrams make excellent documentation, but a CASE tool isn’t essential: plenty of good designers prefer pen and paper or a whiteboard.
drizzle-kit pull or prisma db pull is reverse engineering. Diagram-first tools such as dbdiagram.io export DDL, and database clients like DBeaver draw ER diagrams from live databases.Keep checking the model, and later the database, against sample data, ideally real data from the client in a format they can work with. That shows whether your normalization matches how the client actually handles their information. The book describes three layers:
Stress testing matters especially on the web, where traffic can spike without warning: you need to know your breaking point to plan hardware and hosting before it bites. (The book recommends a free Microsoft web stress tool of the time; today you’d reach for k6, JMeter or Locust.)
| Structure | How it’s built | Book example | Watch for |
|---|---|---|---|
| One-to-many | Foreign key on the child | Customers → Orders | The many end may be zero |
| Many-to-many | Associative table holding both keys | Orders ↔ Products via OrderItems | Never use repeating columns |
| One-to-many recursive | Nullable foreign key to the same table | Category tree, manager hierarchy | Cycles; null at the top |
| Many-to-many recursive | Associative table with two keys to the same table | Product bundles | A bundle must not contain itself |
| One-to-one | Actual: FK with one row max. Sub-type: FK on super-type (one sub-type) or sub-type PK = FK (several) | Department manager; Items/Files; contract and salaried employees | Often better as one-to-many |
A crash course on Chapter 5 of Practical Database Design for the Web: the book starts its running web store project. You gather data requirements from the store owner and the customer, sort them into entities and attributes, test every relationship in both directions until its cardinality is settled, and assemble one conceptual ER diagram that Chapter 6 will turn into tables.
The chapter opens with the (reportedly Einstein’s) idea of spending most of your time understanding a problem before solving it. The book doesn’t ask for five-sixths of the project, but its point stands: a database planned in a hurry is redesigned later at much greater cost.
Web databases do many jobs: content management systems, search engine link stores, forum posts, product catalogs. To show planning and design in practice, this chapter and the next build one example end to end: the database behind a web store. It has two stakeholders, the people whose needs shape the data:
You play the database designer. Web designers, developers and a project manager exist too, and the book assumes the stakeholder interviews have already happened. Your job is to mine them for data. The planning runs in a fixed order, because each step needs the previous one’s output: you can’t draw a model until you know the entities and relationships, and you can’t know those until you know what the database must store.
Most requirements come from the store owner. The book’s advice is to respect how big the application design is (pages for browsing and ordering) but not to get absorbed in how the store will work. Focus on the product and customer data that has to be stored and processed.
The owner’s list of product information: name, short description, long description, size, color, weight, price, availability (quantity on hand), a picture, special discounts, comments from satisfied customers, shipping cost, and the product’s category or categories. Whatever tables you end up with, each of these must live somewhere. Most are attributes of a product; a few, like categories, are really relationships. At this stage you simply collect them.
Two more owner requirements shape the data:
The owner needs pages to add products, remove poor sellers, edit descriptions and set discounts. These are mostly processing, but you skim them for anything that must be stored:
Course addition · the status flag in SQL (a “soft delete”)
ALTER TABLE product ADD COLUMN status VARCHAR(10) NOT NULL DEFAULT 'active';
-- "Delete" a product that has sales: hide it, keep the history
UPDATE product SET status = 'deleted' WHERE product_id = 42;
-- The storefront only ever lists active products
SELECT name, price FROM product WHERE status = 'active';Because this owner ships orders themselves (rather than passing them to a third-party fulfiller), the store should record as much about each order as it can, for tracking and sales analysis. The product side is easy: products ordered, quantity of each, discounts, total shipping cost, taxes, total cost. Without these the order can’t be fulfilled. The customer side is harder, and comes down to privacy.
To buy anything, a customer must give two things: valid payment authorization and where (and perhaps how) to ship. Both may include a name, and the second includes an address. If the owner wants to keep names and addresses for reports, check the privacy law where the store operates; the book cites European data protection rules, which allow only the minimum, kept briefly, unless the customer agrees.
The book’s rule of thumb: all items of customer information should be optional
. A question like “gift or personal use?” is useful to the owner, but the customer gains nothing by answering, and a form that won’t submit until it’s answered may lose the sale. Two items are safe and useful:
Best practice is not to store payment details at all. The customer pays on a third-party processor’s secure site, the processor answers “approved” or “denied”, and the store records only that outcome on the order. If payment is denied, the customer may want to abandon the order or save it and come back with a different payment method. The book lists Authorize.Net, LinkPoint, VeriSign, PayPal and 2CheckOut as examples.
Gathering is about noticing what each requirement means for the data. Pick one:
As in Chapter 4, a conceptual (ER) design starts by listing the entities and their attributes. The key discipline is to leave out anything that looks like a relationship. Listing plain attributes first gets them out of the way, so the next step can concentrate on relationships, which is where the real design decisions are.
Compare the lists with the requirements: “product category or categories” has gone from Product, and “subcategories” has gone from Category. Both will come back as relationships. The book’s lists already hint at the relationships: products are classified by category, and customers have orders.
Two things are worth noticing in the book’s own lists. The status attribute from the administration requirements isn’t on Product’s list, though the design needs it. And Order still lists “product(s) ordered” and “quantity of each product”; strictly, those describe the Order–Product relationship, and Chapter 6 is where they find their real home.
categories attribute on Product (a list in one field) or a customer attribute holding a name on Order. If an “attribute” names another entity, it is a relationship. Also, don’t create a separate entity for every word in the requirements: Subcategory turns out not to be one (section 5.4).Now the real work: cardinality, how many instances of one entity relate to an instance of another. The book’s technique is to examine every relationship in both directions, writing a sentence of the form each X is related to how many Y
. “Related” becomes a verb (contains, orders), and “how many” is the cardinality in that direction. Each answer has a minimum (must there be any?) and a maximum (can there be more than one?).
Taking each entity in turn: Product relates to Category and to Order. It does not relate directly to Customer: products reach customers only through the orders customers place.
The book first draws this as a general many-to-many, with crow’s feet at both ends and two verb phrases, one per direction: a category contains products, a product belongs to categories. Then two design decisions settle the minimums:
Databases can’t implement a many-to-many directly. It will be split into two one-to-many relationships through an associative entity, in Chapter 6.
Also many-to-many. A new product isn’t on any order yet, so a product may be on zero orders; most products sell repeatedly, so many. (A one-of-a-kind item fits too: its quantity on hand simply drops from 1 to 0 after one order.) An order without a product isn’t an order, and one product per order would be very unfriendly, so an order is for one or more products. The zero shows up as the small circle at the Order end.
The book adds that ER conventions vary and that perfect notation matters less than confirming each cardinality and recording it for later stages.
Is Subcategory its own entity? The test is whether it is intrinsically different from a category. It isn’t: both have a name and a description, and there can be sub-sub-levels. So the book models it as a one-to-many recursive relationship: Category related to itself.
But surely “Programming” can sit under several parents? The book’s Open Directory example shows it doesn’t need to: Computers: Programming, Computers: Education: Programming and Computers: Parallel Computing: Programming are three different categories that share a name, each with one parent.
Course addition · a recursive relationship is a foreign key to the same table
CREATE TABLE category (
category_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description TEXT,
parent_id INT NULL REFERENCES category(category_id) -- zero or one parent
);
-- Top-level categories have no parent
SELECT name FROM category WHERE parent_id IS NULL;Customer relates only to Order, and this one is one-to-many. A customer can place many orders, or none yet: they may register first, or just join a mailing list (only the e-mail is needed). An order must belong to a customer, because the application shouldn’t create an order without knowing who placed it, and orders shared between customers are too complex and unrealistic. So: exactly one customer per order.
The conceptual data model shows every entity of interest and the relationships between them, without tables or columns. You get it by assembling the small ER diagrams from the previous step into one. Crow’s feet mark the “many” ends (product–category is many-to-many, customer–order is one-to-many) and circles mark where zero is allowed (a customer may have no orders).
Two of the four relationships are many-to-many (Category–Product, Product–Order). Each will need an associative entity, but that belongs to physical design, which is Chapter 6. Without the gathering step, the book reminds you, all of this would be guesswork, and a good guess for one project can be disastrously wrong for another.
Category with an optional parentId, Order with a required customerId. The many-to-many lines are the ones that can’t be a single ID field on either side. They need their own type, which is exactly what Chapter 6 adds.| Relationship | One direction | Other direction | Type |
|---|---|---|---|
| Category–Product | Each category contains one or more products | Each product belongs to one or more categories | Many-to-many |
| Product–Order | Each product is ordered by zero or more orders | Each order is for one or more products | Many-to-many |
| Category–Category | Each category contains zero or more subcategories | Each subcategory belongs to zero or one category | One-to-many, recursive |
| Customer–Order | Each customer orders zero or more orders | Each order is ordered by exactly one customer | One-to-many |