The three previous modules have been, at bottom, a course in tools: we know what a DBMS is, we know how to write CREATE TABLE, we know how to query with JOINs and aggregates, we know how to protect integrity with foreign keys and we know how to model documents in MongoDB. What we have not done yet is decide which tables need to exist. The BiblioRed schema —those seven tables we have been using since lesson 02-02— appeared almost by spontaneous generation: we needed to store books, so books; we needed to store loans, so loans. It worked because the domain was small and obvious.

That margin runs out today. The Vallmar city council has just commissioned BiblioRed to carry out a real extension of the service: managing cultural events, member registrations, rooms and capacities, fines and payments, and a catalog that is no longer only about books. It is a commission large enough that the "open the editor and start typing tables" method would produce an expensive disaster. And it is exactly the material we will be working with throughout the four lessons of this module: here we gather the requirements and set the principles, in 04-02 we draw them, in 04-03 we turn them into tables and in 04-04 we harden them with types and constraints.

This lesson is about what happens before you type. What goals a schema pursues, what phases a design goes through, how a conversation with a client becomes working material, how an entity is told apart from an attribute, how things are named so that they are still understood five years from now, and which design patterns you have to recognize in order not to fall into them. It is the least "technical" lesson of the module and probably the one that saves the most money.

1. What designing a schema is and why it happens before typing
2. The six goals of a good schema and their tensions
3. The three design phases: conceptual, logical and physical
4. Requirements gathering: from conversation to document
5. The BiblioRed commission: requirements document v1.0
6. Spotting implicit rules and ambiguities
7. Identifying entities, attributes and relationships in the text
8. Entity or attribute? Decision criteria
9. Naming conventions
10. Natural key versus surrogate key, now as a design decision
11. "One thing, one place" and "one fact, one row"
12. Frequent design anti-patterns
13. Documenting and versioning the schema
14. Common Mistakes and Tips
15. Exercises
16. Conclusion

Designing a schema means deciding, before writing a single DDL statement, which real-world things the database is going to represent, how they relate to each other and which rules they must always satisfy.

The reasonable question from a beginner is: why not design as you go? After all ALTER TABLE exists, and in module 2 we used it several times without drama. The answer has three parts:

The cost of changing grows over time, and not linearly. Changing a table on day one costs you one line. Changing it when it has 40,000 rows, three applications querying it, a monthly report aggregating it and a replicated copy in an analytics system costs you a coordinated migration, a downtime window and a rollback plan. This is the economic argument and it is the strongest one.

Data outlives applications. BiblioRed's web application will probably be rewritten two or three times in fifteen years; the 2026 loans will still be there. A badly designed schema is a debt you inherit.

A schema is a theory of the business, not a container. When we decide that a loan links a member to a copy and not to a book, we are asserting something about how the library works. If that assertion is false, no amount of application code will fix it. Designing is, above all, understanding the domain, and that is why the most valuable phase is done talking to people, not to the manager.

A useful way to see it: application code expresses what the system does; the schema expresses what the system believes to be true. The second changes far more slowly and getting it wrong is far more expensive.

A schema is not judged on being "pretty". It is judged against six concrete goals:

1. Integrity. The schema must make invalid states impossible, not merely unlikely. If a fine can never be negative, it is the schema —not the web form— that must prevent it. We already argued this in 02-06 for foreign keys; in 04-04 we will extend it to CHECK, NOT NULL and domains.
2. Absence of unnecessary redundancy. Every fact must be stored in exactly one place. If a branch's name is copied into three tables, sooner or later the three will disagree. Note the adjective: unnecessary. There is deliberate, justified redundancy, and it is called denormalization (module 5).
3. The ability to answer the business's questions. An exquisitely elegant schema that cannot answer "how many free seats are left in Thursday's book club?" is a failed schema. That is why the list of queries is part of the requirements document.
4. Maintainability. That a new person can understand the schema in an afternoon. Clear names, predictable structure, documentation.
5. Evolution without traumatic migrations. Adding a new type of event should not require ALTER TABLE. Adding a new type of material should not require rewriting the existing queries either. A good design anticipates which axis is going to grow.
6. Reasonable performance. Not "maximum": reasonable. Performance is worked on afterwards, with indexes and optimization (lesson 06-03), but the design can make it impossible: a query that needs to traverse seven junction tables to render the website's home page is a design problem, not an index problem.

These six goals are not compatible with each other. Designing means choosing the balance point, and knowing which goal you are sacrificing.

The practical rule: when two goals collide, integrity wins, unless there is a measurement proving that the cost is unbearable. Incorrect data is the only problem that cannot be fixed afterwards.

Database design is classically organized into three phases. They are not bureaucracy: each one answers different questions, and mixing them is the most frequent cause of bad designs, because whoever starts out arguing whether a field is VARCHAR(50) or TEXT has already stopped thinking about the domain.

The dashed arrows matter: the process is not a waterfall. Each phase uncovers mistakes made in the previous one, and going back is a sign that the method is working.

In lesson 01-04 we saw the three ANSI/SPARC levels: external, conceptual and internal. The three design phases produce those three levels, although the correspondence is not exactly one to one:

The practical consequence is that the physical design can be changed without touching the queries (that is physical independence), which is why postponing it costs nothing. The logical design, on the other hand, is the contract with the applications, and that is why changing it does hurt.

A detail that is often overlooked: the conceptual design is independent even of the paradigm. The same BiblioRed ER diagram would serve to derive a relational schema or a MongoDB document model (with the techniques from 03-03). That is why it is drawn first.

Nobody turns up with a requirements document. They turn up with a sentence: "we want to manage the libraries' events too". The job consists of turning that into material you can design on.

• Interviews with whoever is going to use the system (librarians, activity coordination, administration).
• Existing documents: spreadsheets, paper forms, posters, emails. They are gold: a paper registration form contains the list of attributes somebody already considered necessary.
• The current system, if there is one. BiblioRed already has seven tables: the new material must fit with them, not ignore them.
• Regulations. A city council has room-use bylaws and fee ordinances. That is where the fine amounts are.

This list is probably the most reusable thing in the lesson. For everything the client mentions:

The two most productive rows are exceptions and queries.

The exceptions question —"has it ever happened that an event was left without a speaker?"— is the one that uncovers the rules the client believes are obvious and are not. A client never says "an event may have no room"; they say it when you ask whether that has ever happened and they answer "well, the summer storytelling session is held in the courtyard".

The queries question is the one that prevents you from designing a schema that is of no use. The list of queries is a first-class requirement and it must be in the document: in 04-03 we will use it as the validation criterion for the resulting schema.

When the client asks for something, there are three possible answers and only two of them are honest: "yes, and this is what it implies", "yes, but in version 2 and for this reason" and "no, because…". Accepting everything without sizing it produces schemas with twenty tables for features nobody will use. Scope is a design decision, and that is why a requirements document always carries an explicit "out of scope" section.

This is the outcome of three meetings with BiblioRed's coordination team and with the Culture department of Vallmar city council. It is the document the next three lessons are going to use: in 04-02 we will draw it, in 04-03 we will turn it into tables and in 04-04 we will harden it. It is worth reading in full before moving on.

Extension of the BiblioRed service — Requirements document v1.0

Context. BiblioRed manages 4 branches (Central, North, South and East), 12,000 members and 40,000 copies, on a seven-table PostgreSQL schema: branches, authors, members, books, copies, loans and reservations. The city council is extending the commission to cover the management of cultural activities, fine collection and a multi-format catalog.

A. Functional requirements

R1 — Multi-format catalog. The catalog is no longer only about books. It must accommodate books, DVDs, magazines and audiobooks. They all share title, language, publisher or producer, publication year and the date they were added to the catalog. In addition:

• books have an ISBN, a page count and a binding type;
• DVDs have a duration in minutes, a video format, a region code and a list of subtitle languages;
• magazines have an ISSN, an issue number and a frequency;
• audiobooks have a duration, a narrator and a file format.

A material may have an associated author; magazines usually do not have one. Everything that is a book today must keep working exactly as it does now.

R2 — Copies. Each material has from 0 to N physical copies, each one located in a branch, with a status and an acquisition date. Copies are identified by a printed code (EJ-3081) and, in addition, are numbered within their material: "copy 3 of The Map of Time". Copies are loaned; materials are reserved.

R3 — Rooms. Each branch has between 1 and 6 rooms. For each room we care about the name, the maximum capacity in people, the floor and whether it is accessible for people with reduced mobility. A room name is unique only within its branch: there is a "Multipurpose Room" in Central and another one in North.

R4 — Events. A cultural event has a title, a description, a type, the room where it is held, a start date and time, an end date and time, a number of offered seats, a status (scheduled, open, full, held, cancelled) and whether it is published on the website. An event is held in a single room; the event's branch is the branch of its room. In version 1.0, an event is a single session.

R5 — Event types. Today they are: book club, workshop, presentation, storytelling and talk. The city council wants to be able to add new types without calling the IT department. For each type we care about the name, a description and an indicative standard duration.

R6 — Registrations. A member registers for an event. An event accepts many members and a member registers for many events. For each registration we store the date, the status (confirmed, waiting_list, cancelled, attended) and the number of companions (from 0 to 3). A member cannot register twice for the same event.

R7 — Speakers. Events are led by speakers, who may be BiblioRed's own staff or external professionals. An event may have several speakers and a speaker takes part in many events. In the same event, one person may play more than one role (moderating the discussion and also running the workshop). For each participation we record the fee, which is 0 for in-house staff.

R8 — Materials covered in an event. A book club discusses one or more materials from the catalog; a workshop may recommend a reading list. We record whether the material is the event's main one or merely recommended.

R9 — Follow-up report. Once an event has been held, the coordination team writes a report with the real number of attendees, the average rating from the surveys and free-text notes. Not every event has a report, and none has more than one.

R10 — Fines. A loan returned after the due date generates a late-return fine. Fines may also be issued for damage or for loss of the copy. The same loan can generate at most one fine of each reason. A fine has an amount in euros, an issue date and a status (pending, paid, waived, voided).

R11 — Payments. A fine can be settled in several partial payments, in cash at the desk, by card or through the city council's web gateway. For each payment we store the date and time, the amount, the method and an external reference (receipt number or gateway identifier). The outstanding amount of a fine is its amount minus the sum of its payments.

R12 — Member phone numbers. Today there is a single phone number per member. We are asked to be able to store up to three per member, each with its type (mobile, landline, work).

R13 — Branch address. Today the address is a single free-text field. The new website needs to show the city separately and search for branches by postal code.

R14 — Blocking for debt. A member with more than €20 outstanding cannot register for events or take out new loans.

B. Business rules

Code	Rule
BR1	An event's offered seats cannot exceed the capacity of its room.
BR2	Confirmed registrations, counting companions, cannot exceed the offered seats; beyond that point they go on the waiting list.
BR3	An event's end date and time must be later than its start.
BR4	Two non-cancelled events cannot overlap in time in the same room.
BR5	A fine's amount is never negative, and the sum of its payments never exceeds its amount.
BR6	Only active members can register.
BR7	The registration date cannot be later than the start of the event.
BR8	An event published on the website must have a room assigned and offered seats greater than zero.
BR9	The ISBN uniquely identifies a book; the ISSN together with the issue number uniquely identifies a magazine.
BR10	A material cannot be reserved if it has no copies in the catalog.

C. Queries the system must be able to answer

Code	Query
Q1	Public agenda of the month's events, by branch, published ones only.
Q2	Free seats for a specific event, in real time.
Q3	List of members registered for an event with their contact phone number.
Q4	History of the events a member has registered for.
Q5	Average occupancy of each room by branch and quarter.
Q6	Fine revenue by month and payment method.
Q7	Members with outstanding debt above €20.
Q8	Catalog filtered by material type and language.
Q9	DVDs that have Catalan subtitles.
Q10	The ten most borrowed materials, broken down by material type.
Q11	Speakers who have taken part in more than three events, with their total fees.
Q12	Events held more than 15 days ago that still have no report.

D. Estimated volumes over three years

Concept	Today	In 3 years
Members	12,000	16,000
Materials (titles)	18,000	25,000
Copies	40,000	55,000
Events	0	1,200 (≈400/year)
Registrations	0	30,000
Fines	0	9,000

E. Out of scope in version 1.0

• Room booking by private individuals or outside organizations.
• Events with several sessions (series, multi-week courses).
• Ticket sales or paid events.
• Staff management, payroll and contracts for external speakers.
• Accounting integration with the city council's system (payments are recorded, not booked).

Notice three things about this document, because they are what make it useful:

1. The cardinalities are written in prose, not drawn yet ("an event is held in a single room", "a member registers for many events"). That is the raw material for 04-02.
2. The business rules are numbered and kept separate from the functional requirements. In 04-04 we will turn them, one by one, into CHECK, UNIQUE or application logic.
3. The queries and the scope are explicit. Without the former you cannot validate the design; without the latter, the design never ends.

The document above did not come out like that from the first meeting. It came out of spotting ambiguities in what the client was saying and handing them back turned into questions. These are the five that showed up in BiblioRed, and they are a fairly representative catalog of what you run into:

And these are the linguistic signals that give away an implicit rule, useful as a checklist when reading any statement of requirements:

• "Normally", "almost always", "generally" → there are undocumented exceptions. "Normally the event has a single speaker" means that sometimes it has two, and that changes the cardinality.
• "And also" at the end of a sentence → a requirement that slipped in without being analyzed.
• A plural where you expected a singular → "the subtitle languages" (R1) is literally a multivalued attribute, and you can see it in the word.
• An evaluative adjective → "important events", "problem members": you have to ask for the operational definition, because it will end up being a column or a rule.
• A verb in the past or the future → "the speakers who took part": it implies history, and it implies that deleting is not an option.
• "You already know" or "that's obvious" → it almost never is.

There is a classic, almost mechanical technique to get started: underline the nouns and the verbs in the statement of requirements.

• Nouns are candidates for entity (if they are things with an identity of their own) or for attribute (if they are properties of something else).
• Verbs connecting two nouns are candidates for relationship: a member registers for an event, an event is held in a room, a loan generates a fine.
• Adjectives and quantifiers supply cardinality and obligation: "a single room", "up to three", "from 0 to N".

Let us apply it to R4 and R6:

A cultural event has a title, a description, a type, the room where it is held, a start date and time… A member registers for an event… For each registration we store the date, the status and the number of companions.

Result of the first pass:

The underlining technique starts off well and then misleads you. Its three typical failures:

1. Synonyms that look like different entities. "Member", "user", "reader" and "subscriber" may be the same thing said by four different people. You have to consolidate the vocabulary, and that is where the project glossary comes from.
2. Homonyms that look like the same entity. "Status" appears in copies, in reservations, in events, in registrations and in fines, and they are five completely different sets of values.
3. Entities that are never named. Nobody said the word "participation" in R7, and yet it is an entity (the relationship between speaker and event with its fee). Entities that emerge from an N:M relationship almost never appear as a noun in the statement. You have to hunt them down by asking: "when this crosses with that, is there anything to record about the crossing?".

That is why underlining is a starting point, not an algorithm. The definitive list is closed by drawing, which is what we will do in the next lesson.

This is the question that eats up the most time in a real design. Let us go back to the "event type" from R5.

Option A — attribute: events.event_type is a text with the values 'book_club', 'workshop', 'presentation'…

Option B — entity: there is an event_types table and events points at it with a foreign key.

Five criteria to decide, applied to this case:

Three clear criteria in favor: event_types will be an entity. Compare it with a material's language attribute: it has no interesting properties, the list is stable (ISO 639) and nobody is going to "manage languages". That one stays as an attribute.

It is an entity if it has attributes of its own, or a life cycle of its own, or if somebody manages the set of values. It is an attribute if it is a simple, stable value with no properties.

And a golden rule about timing: when in reasonable doubt, start with an attribute. Turning an attribute into an entity later is a mechanical migration (create the table, populate it with the distinct values, replace the column with an FK). Turning an entity into an attribute can also be done, but it means you have been maintaining a meaningless table for two years. The cost asymmetry favors simplicity.

The schema's names are the interface every person working with it will see for years. These are the decisions you have to make, with BiblioRed's choice and its reason:

There are names that look natural and are SQL reserved words. Using them forces you to quote forever, in every query, in every language, in every report.

Words to avoid as table or column names: user, order, group, table, select, from, where, check, default, end, desc, all, any, case, column, constraint, grant, limit, offset, references, union, unique, values, window. In English the risk is particularly high, because the most natural word for a thing —user, order, group— is very often precisely the reserved one.

The principle governing this whole section: almost none of these decisions is objectively correct. Plural or singular makes no difference. What does make a difference is half the schema being plural and the other half singular, because then nobody can write a query without checking the catalog first. Consistency is worth more than correctness.

In lesson 02-01 we raised the debate with the ISBN: books has isbn (candidate natural key) and book_id (surrogate). Back then it was a theoretical matter. Now it is a decision that has to be made for each of the new tables, and it deserves firm criteria.

1. No natural key is as stable as it looks. The ISBN is the canonical example and serves as a cautionary tale: it went from 10 to 13 digits in 2007, they get reused by mistake, there are editions with no ISBN and there are books with two. A national ID number changes with nationality. A room code changes when the floor is refurbished. Any value managed by a third party can change, and if it is your primary key, that change cascades through every table referencing it.

2. The surrogate key does not remove the need for the natural one: it complements it. This is the frequent mistake. Making material_id the PK does not entitle you to forget about the ISBN: if UNIQUE is not declared on the ISBN, two rows for the same book will end up existing and no constraint will prevent it. The correct combination is surrogate PK + UNIQUE on the natural key, which is exactly what BiblioRed's current schema does (book_id PK, isbn UNIQUE).

3. The exception is junction tables. In registrations, the pair (event_id, member_id) is a perfect natural key: it does not change (if it changed, it would be a different registration), it is short, and it is exactly the business rule from R6 ("a member cannot register twice"). Adding a surrogate registration_id there is often just noise; it is only justified if another table has to reference the registration. We will decide it table by table in 04-03.

BiblioRed's rule for the extension: a surrogate key <entity>_id in every strong entity, always accompanied by UNIQUE on the natural key when one exists; a composite natural key in junction tables and in weak entities, unless they need to be referenced from outside.

Two principles that can be stated in a single line and that explain most design problems.

Every fact must be stored exactly once. If the capacity of Central's Multipurpose Room is in rooms and is also copied into every row of events, there is a physical possibility of them disagreeing. And what can disagree, given enough time, does disagree.

The concrete damage of breaking this rule takes the form of the three classic anomalies:

Every row must represent a single fact about the world. A row in loans says "member 14 took away copy EJ-3081 on such and such a date". That is one fact. If that same row also carried the member's address, it would be representing two different facts —the loan and the home address— glued together by accident, and it would suffer the three anomalies above.

These two principles, formalized with mathematics, are called normalization, and the process of applying them produces the normal forms (1NF, 2NF, 3NF, BCNF…). It is a body of theory with precise definitions of functional dependency, and it is the whole of module 5 of this course: the concepts in 05-01, the normal forms one by one in 05-02, the process applied in 05-03 and the cases where you deliberately decide to break them —denormalizing— in 05-04.

In this module we will work with the intuitive version ("one thing, one place") and we will come back to the BiblioRed schema in module 5 with the formal toolkit to verify that it holds up. Do not try to apply normal forms yet: the design in this module is done through understanding of the domain, and that is precisely the way it is done in professional practice.

An anti-pattern is a solution that looks reasonable, gets used a lot and causes predictable damage. Recognizing them by name saves arguments.

Instead of columns, a generic table of triples:

It looks like the perfect solution to R1: each material type has its own attributes and this way they all fit. What you lose:

• The data types. duration_min is text; nothing stops it from holding 'yesterday'.
• The constraints. No NOT NULL is possible: you cannot require a book to have an ISBN.
• The queries. "Catalan DVDs longer than 90 minutes" needs two self-JOINs and a type conversion.
• Performance, because of the above.

When it is justified: when the attributes are defined by the user at run time and are genuinely unpredictable (configurable forms, product catalogs with thousands of families). Even then, today the answer is usually a JSONB column (03-03), which preserves types, allows GIN indexes and validates with $jsonSchema or CHECK. For BiblioRed, with four known material types, EAV would be a mistake: the correct solution is the generalization hierarchy we will see in 04-02 and 04-03.

It is the tempting answer to R12 ("up to three phone numbers"), because the requirement even states the limit. What goes wrong:

• "How many members have a mobile?" requires looking at three columns and stitching them together with UNION.
• The fourth phone number always arrives, and it brings an ALTER TABLE and a change to every query.
• The type is lost: which of the three is the mobile?
• Most rows have NULL in two of the three columns.

The right form is a member_phones table, that is, treating the multivalued attribute as what it is. In 04-03 we will formalize it as a transformation rule.

Right in the path of R1 and of query Q9 ("DVDs with Catalan subtitles"). What happens in practice:

That query also returns the DVDs with subtitles in 'cat', in 'oc-ca' and any value containing the letters ca in any position. There is no way to guarantee that the codes are valid, no way to count how many DVDs there are per language without slicing strings, and you cannot put a foreign key to a table of languages. It is the purest violation of "one fact, one row".

The correct alternative is a dvd_subtitles table. If the grouping into a single field really is needed, PostgreSQL offers arrays and JSONB with their own operators and indexes (subtitles @> ARRAY['ca']), which are not the same thing as a comma-separated string: they preserve the structure. We will see it in 04-04.

A table called data, general, parameters or misc where columns that did not fit anywhere else keep getting added. Symptoms: a generic name, more than 40 columns, half of them NULL, and nobody on the team able to explain what one row represents.

The diagnosis is always the same: if you cannot complete the sentence "each row of this table is a ______", the table is wrong. A row of loans is a loan. A row of data is… nothing.

The least discussed anti-pattern and probably the most expensive, because whoever commits it believes they are doing an especially good job. It consists of modeling today the flexibility that might be needed three years from now: a generic entities table with an entity_type, a configurable metadata system, a five-level hierarchy because "who knows".

For BiblioRed the specific temptation is real: "while we are doing events, let us do event series, with sessions, and event templates, and recurring events". The requirements document cut it off at the root by putting it in section E, out of scope. When the requirement actually arrives, you add a sessions table and events gains an optional FK: half an hour of work, with the real requirement in front of you instead of an imagined one.

The honest counterweight: there is one kind of anticipation that does pay off, and it is the kind that avoids losing information. If today you store only the balance and tomorrow you need the movements, those movements no longer exist. Storing facts instead of summaries is almost never regretted; building generic machinery almost always is.

A schema with no documentation is a schema that only the person who wrote it understands, for as long as they remember it.

The rule is simple: nobody touches the production database by hand. Every schema change is a versioned file in the repository, with a sequence number, applied exactly once.

Each migration must be idempotent in its result (applying it twice must not break anything) and, as far as possible, have its rollback written. Tools such as Flyway, Liquibase, Alembic or the migrations built into frameworks automate the record of what has been applied; the detail on specific tools is in 09-03. What is essential is the habit, not the tool.

PostgreSQL lets you attach comments to the catalog itself. The advantage over a separate document is that it cannot drift out of sync with the schema by carelessness, because it lives inside it.

They are read from psql with \d+ registrations, and any graphical tool displays them. That last comment, the one on the obsolete column, is what stops somebody two years from now from building a report on a frozen value.

It is the table that accompanies the diagram and that anybody can read without knowing SQL. An extract from BiblioRed's:

And one last piece that almost nobody writes and that saves projects: a decision log. Three columns —decision, discarded alternatives, reason— with entries such as "an event is a single session; modeling series was discarded; reason: out of scope for v1.0, a sessions table is anticipated if the requirement arrives". When somebody asks a year from now "why is this like this?", the answer will exist.

Starting with the CREATE TABLE. It is the root mistake from which almost all the others follow. Writing DDL gives you a sense of progress and locks in decisions that have not been thought through yet. Draw first, even if it is on a napkin.

Designing from the screens. If the schema copies the structure of the application's forms, it will be tied to an interface that will change next year. Screens are a source of requirements, not a data model.

Confusing "they did not ask for it" with "it does not happen". The client did not ask to store two speakers per event; it simply did not occur to them to mention it until they were asked about the exceptions. Always ask about the odd cases: they are the ones that break the cardinalities.

Modeling the present and forgetting time. "We store the member's phone number" is fine; "we store which branch a member belongs to" hides a question: what if they move? Does it matter which one they belonged to when they took out that loan? Asking "do you need the history?" for each relationship costs five seconds and prevents complete redesigns.

Putting the unit in the name instead of in the type. duration_min is acceptable as an explicit convention, but amount without specifying currency or scale is not. Document the units in the data dictionary and reinforce them with the type (04-04).

Using the same name for different concepts. Five status columns with five incompatible sets of values are a permanent source of confusion. Either you qualify them (event_status, fine_status) or you document them meticulously.

Tip: validate the design by reading it out loud. "An event is held in a room; a room hosts many events; an event may have no room if it is outdoors." If the sentence sounds odd, the design is wrong. This trick works surprisingly well and costs nothing.

Tip: walk through the list of queries before calling the design good. Take Q1 to Q12 and, for each one, say out loud which tables you would go through. If any of them cannot be answered, something is missing. We will do this formally at the end of 04-03.

Tip: write down the reason, not just the decision. "An event = one session" without the why gets reopened every six months. With the why, it stays closed.

Vallmar's Culture department adds this paragraph to the commission:

"We also want to keep track of the equipment we lend to the neighborhood associations for their activities: projectors, speakers and that sort of thing. Normally the association's president asks for it, and they bring it back a few days later. If something breaks, we note it down."

Identify at least four ambiguities or implicit rules and write, for each one, the specific question you would ask the client. State as well which linguistic signal alerted you.

For each element, decide whether in the BiblioRed schema it should be an entity or an attribute, applying the five criteria from section 8. Justify your answer in one sentence.

1. The payment method of a payment (cash, card, gateway).
2. The publisher of a material.
3. The postal code of a branch.
4. The reason for a fine (late_return, damage, loss).
5. The nationality of an author.

An outside team proposes this table to solve requirements R4, R6 and R9 in one go:

Name all the anti-patterns present, explain the concrete damage each one causes with an example from BiblioRed, and describe in two or three sentences how you would restructure it (without writing SQL yet: that is 04-03).

Solution to Exercise 1

Any four of these six is a complete answer. The most important are number 2 (it introduces a new entity, associations, that was not in the model) and number 6 (it changes the cardinality of the loan).

Solution to Exercise 2

Solution to Exercise 3

Anti-patterns present:

1. Catch-all table. activities with a record_type mixes events, registrations and reports into a single table. You cannot complete the sentence "each row is a ___": some are events and others are registrations. Consequence: no column can be NOT NULL (what is mandatory for an event is not mandatory for a registration) and every query drags a WHERE record_type = ... along.
2. Numbered columns (data1, data2, data3). It is EAV in disguise: the meaning of data2 depends on record_type. Nobody will know a year from now that for registrations data2 was the status. Impossible to restrict values.
3. Comma-separated list in signed_up_members. It breaks Q2 (free seats), Q3 (registered members with phone number) and Q4 (the member's history), it prevents the foreign key to members and it means deleting a member leaves textual garbage behind. On top of that there is nowhere to put the date or the companions from R6.
4. Redundancy: room_capacity copied from the room catalog. An update anomaly the moment a room is refurbished (section 11).
5. Room as free text. "Multipurpose Room" identifies nothing: R3 says the name is only unique within the branch. You will get 'Multipurpose', 'Multipurpose Room' and 'multipurpose ' with a trailing space.
6. Inappropriate types: date VARCHAR(30) makes it impossible to sort chronologically, compare ranges and answer Q1 and Q12. It is a preview of 04-04.
7. Column named id instead of activity_id: minor, but inconsistent with the convention of the existing schema.

Proposed restructuring: split it into entities with an identity of their own —events, rooms, registrations, event_reports— joined by foreign keys; turn the list of signed-up members into rows of registrations, one per member, with its date, status and companions attributes; remove room_capacity from events and obtain it by JOIN with rooms; and replace data1..3 with named, typed columns in whichever table they belong to. It is exactly the diagram we will draw in the next lesson.

This lesson has changed the course's mode of work: from writing SQL to deciding which SQL has to be written.

• Designing before typing is justified by three reasons: the cost of changing grows non-linearly, data outlives applications and a schema is a theory of the business, not a container.
• A schema is judged against six goals —integrity, no redundancy, ability to answer the business, maintainability, evolution and reasonable performance— which conflict with each other. When they collide, integrity wins unless there is proof to the contrary.
• Design has three phases: conceptual (what exists), logical (which tables) and physical (how it is accessed fast). They correspond to the ANSI/SPARC levels from 01-04, and mixing them is the most frequent cause of bad designs.
• Requirements gathering is a technique with concrete questions. The two most productive ones are about exceptions ("has it ever happened that…?") and about queries ("what are you going to ask the system?").
• BiblioRed's requirements document v1.0 —R1 to R14, ten business rules, twelve queries, volumes and scope— is now closed and is the material for the next three lessons.
• Ambiguities are spotted through linguistic signals: "normally", unexpected plurals, evaluative adjectives, verbs in the past tense. Five real BiblioRed ambiguities were resolved and written down.
• Underlining nouns and verbs kicks off the identification of entities and relationships, but it fails with synonyms, homonyms and with the entities nobody names —the ones that emerge from an N:M crossing, such as a speaker's participation.
• The entity or attribute question is settled with five criteria: own attributes, life cycle, own relationships, multiplicity and stability of the value set. When in doubt, start with an attribute: promoting it later is cheap.
• Naming conventions —plural, snake_case, _id suffix, FK named after the referenced PK, English, named constraints— matter less for their content than for their uniform application: consistency is worth more than correctness.
• Natural versus surrogate stops being theory: a surrogate PK in every strong entity, always with UNIQUE on the natural key; a composite natural key in junction tables and weak entities.
• "One thing, one place" and "one fact, one row" prevent update, insertion and deletion anomalies. Their formal version is normalization, which is the whole of module 5 (05-01 and 05-02); here we work with the intuitive version.
• Five anti-patterns identified and named: EAV, numbered columns, comma-separated lists inside a column, the catch-all table and premature over-engineering.
• Document and version: numbered migrations in the repository, COMMENT ON glued to the catalog, a readable data dictionary and a decision log with its reasons.

We have the commission understood, written down and bounded, and we have criteria for making decisions. What we do not have yet is a drawing. In the next lesson, 04-02 Entity-Relationship Diagrams, we will learn the graphical language with which a domain is thought through before any table exists: strong and weak entities, simple, composite, multivalued and derived attributes, relationships with their cardinalities and their participation, the Chen and crow's foot notations, and the generalization hierarchies that will finally solve the problem of having books, DVDs, magazines and audiobooks in the same catalog. At the end of that lesson we will have the complete ER diagram of the extended BiblioRed, decision by decision.