Database Design Trade-offs
This chapter gives HopPress a data model and then designs the database for the reads the application performs. It covers the schema and the post state machine, a table that records every editorial decision, denormalized columns and the transaction that keeps them consistent, indexes, queries and joins, and views and materialized views.
After reading this chapter, you should be able to:
- Read HopPress’s functional requirements and constraints for the entities and data it must store, and distinguish an entity from a role or a state that describes it
- Read the post state machine, state who may read, edit, delete, submit, publish, or return a post in each state, and decide which of those rules the database can enforce and which the application must enforce
- Check a schema against a non-functional requirement, and explain why a history of state changes needs its own table rather than more columns on the post
- Identify a duplicated value, state third normal form and why a derivable column violates it, and justify keeping the column anyway by the read cost of normalizing
- Define a transaction, state what atomicity guarantees, and identify the writes in a HopPress state transition that must succeed or fail together
- Explain what an index is and why a lookup on an indexed column does not read every row, choose indexes from the reads an application performs, and state the write cost of each one
- Read a SQL query that filters, sorts, and selects columns, say which index it can use, recognize the N+1 query pattern, and replace it with a join
- Define a view and a materialized view, state the staleness cost a materialized view introduces, and compare it with denormalization as two ways to store a derived result