We ended the previous lesson with a complete schema and a confession: the types were provisional and the ten business rules from the requirements document were nowhere to be found. Today, in the schema as it stands, it is perfectly possible to insert a fine of −€40, an event that ends before it starts, a room with zero capacity, a registration with the status 'confimed' and a member with forty phone numbers. The schema is structurally correct and semantically defenseless.

This lesson armors it. It has two blocks and both are equally important.

The first is the reasoned choice of data types. It is not the list of PostgreSQL types —that is in the manual— but the decisions behind them: when an integer falls short, why the money in BiblioRed's fines can never go in floating point (with an executable demonstration whose result is surprising), what the difference is between TIMESTAMP and TIMESTAMPTZ and why that difference wrecks event agendas, whether limiting the length of a text achieves anything, and why collation decides whether "Àngels" comes before or after "Angel" when a member searches the catalog.

The second is the catalog of constraints: NOT NULL, DEFAULT, UNIQUE with its surprising behavior in the face of NULL, single- and multi-column CHECK, generated columns, reusable domains, how to name them so that production errors are readable, and how to add them to a table that already has data without taking the service down.

The deliverable is the definitive, hardened version of the extended BiblioRed schema, and with it module 4 closes.

1. Why the data type is a design decision
2. Integers: SMALLINT, INTEGER, BIGINT and the day they run out
3. NUMERIC versus REAL: why money never goes in floating point
4. Text: CHAR, VARCHAR(n) and TEXT
5. Dates and times: TIMESTAMP versus TIMESTAMPTZ, and INTERVAL
6. BOOLEAN and the traps of 'Y'/'N'
7. UUID versus a sequential integer as a key
8. Closed sets of values: ENUM, lookup table or CHECK
9. JSONB and arrays as controlled escape hatches
10. BYTEA and why cover images do not belong in the database
11. Encoding and collation: searching for titles in Spanish and Catalan
12. SQLite's permissive types versus PostgreSQL's strict ones
13. NOT NULL: the decision to allow absences
14. DEFAULT: fallback values
15. Simple and composite UNIQUE, and its relationship with NULL
16. PRIMARY KEY as a combination of the above
17. CHECK: encoding business rules into the schema
18. Generated columns and domains
19. Naming constraints and reading production errors
20. Adding constraints to a table that already has data
21. Which rules go in the database and which in the application
22. Deliverable: the definitive extended BiblioRed schema
23. Common Mistakes and Tips
24. Exercises
25. Conclusion

Choosing a type looks like paperwork. In reality you are deciding four things at once:

A badly chosen type does not raise an error: it silently produces incorrect results, which is worse. A date stored as VARCHAR works perfectly until the day somebody sorts the event agenda and December turns up before February.

The right question is not "how many rows will there be?", but "how many times will the counter be incremented?", and they are different things: deleted rows consume values that are not reused.

BiblioRed's three-year volumes (section D of the requirements document) are 55,000 copies, 30,000 registrations and 9,000 fines. INTEGER gives you room for 2,147 million. Even if BiblioRed multiplied its size by a thousand, it would not come close. INTEGER is correct for every key in this schema.

The case where it is not has a clear signal: tables where rows are inserted by automated events, not by human actions. A web access log with 500 inserts per second exhausts an INTEGER in about fifty days. The practical rule:

If the rows are generated by a person, INTEGER is more than enough. If they are generated by a machine, do the arithmetic.

And a real warning: changing from INTEGER to BIGINT in a large, heavily referenced table is one of the most painful migrations there is, because it rewrites the table, all its indexes and every foreign key column that references it. If there is reasonable doubt, BIGINT from the start costs 4 bytes per row.

It is not about saving bytes: it is about documenting the intent. A capacity SMALLINT tells whoever reads the schema that no municipal room will hold 40,000 people. It is a weak constraint but a free one, and it complements the CHECK we will add later.

This is the most important section of the lesson and the one most often ignored in real projects.

Floating-point types represent numbers in binary. And there are simple decimals that in binary are infinitely repeating, just as 1/3 is in decimal. 0.1 is one of them. What gets stored is not 0.1: it is the closest thing to 0.1 that fits in 64 bits.

Three BiblioRed fines: €3.10, €2.20 and €4.30. The total should be exactly €9.60.

It is not a PostgreSQL bug: it is how binary works, and the same thing happens in Java, Python, JavaScript and C. Now the practical consequences:

A fully paid fine would show up as unpaid. The check for BR5 ("the sum of the payments never exceeds the amount") and query Q7 ("members with debt above €20") would give random results.

The error gets worse as it accumulates. BiblioRed's surcharges are €0.20/day:

Three hundred-thousandths of a euro of difference over a thousand operations. In a municipal accounting close, that is an incident somebody has to investigate. (The exact value may vary slightly by platform; what is invariable is that it is not exact.)

Anything counted in money, anything invoiced and anything compared for equality goes in NUMERIC(p,s). Floating point is for physical magnitudes where an error of 10⁻¹⁵ is irrelevant.

In BiblioRed:

About NUMERIC(6,2): 6 is the total number of digits (precision) and 2 the decimals (scale), so the integer part takes four digits. The manager rounds on insert and rejects what overflows:

Overflow warns you; rounding does not. If BiblioRed ever issued fines for lost material of more than €10,000, it would have to be widened to NUMERIC(8,2).

A note on MONEY: PostgreSQL has a MONEY type, but it depends on the server's locale settings and does not carry the currency inside it. It is discouraged: NUMERIC is the portable option.

The value carries three padding spaces that show up when concatenating, when exporting to CSV and when comparing from an application that does not apply SQL's rules. It is a source of errors out of all proportion to the benefit, which in PostgreSQL is none: CHAR(n) is neither faster nor smaller.

In PostgreSQL, VARCHAR(n) and TEXT are stored exactly the same way. VARCHAR(200) does not reserve 200 bytes; the n is only a validation constraint. So the question is whether that validation contributes anything.

It does contribute when the limit is a real business rule:

It contributes nothing when the number is invented. title VARCHAR(200) answers to no rule: it is a number somebody picked because one had to be picked. And it has a real cost, because the day a 214-character title arrives —they exist— the insert will fail in production, with an error the team will have to diagnose in the small hours:

BiblioRed's criterion: VARCHAR(n) only where n comes from an external standard. TEXT for titles, descriptions, notes, biographies and names. Where the business wants an indicative but non-critical cap, it goes in as a named CHECK, which can be relaxed without rewriting the table:

A non-obvious advantage: widening VARCHAR(200) to VARCHAR(300) is an ALTER TABLE that in older versions rewrote the table; relaxing a CHECK is dropping and recreating one constraint.

TIMESTAMPTZ does not store the time zone. It stores the absolute instant (UTC) and converts it to the session's zone when reading it. TIMESTAMP stores a wall-clock reading with no context.

Now a member checks the agenda from abroad, or an automated process runs with a different configuration:

with_tz correctly says that the 18:00 book club in Vallmar is 11:00 in Bogotá: it is the same instant. without_tz says 18:00 in both, which is false in one of them and there is no way to know which.

And there is a case where the damage happens without leaving Vallmar: the clock change. On the last Sunday of October, 02:30 exists twice. A TIMESTAMP with no zone cannot tell them apart; a TIMESTAMPTZ can, because internally they are two different UTC instants.

Rule: every instant of a fact —when an event starts, when a registration was made, when a payment was recorded— goes in TIMESTAMPTZ. Use it by default and justify yourself when you do not.

TIMESTAMP without a zone has a legitimate and narrow use: recurring schedules that are "local time" by definition, such as "the library opens at 9:00" regardless of the season. There the right thing is usually TIME, not TIMESTAMP.

Not everything needs a time. loan_date, due_date, a fine's issue_date and a material's added_date are dates: the library does not charge by the hour. Using TIMESTAMPTZ there forces you to drag a 00:00:00 around that confuses people and complicates range comparisons.

INTERVAL represents a duration and is added directly to dates:

A detail worth noticing: DATE − DATE gives an integer number of days, not an interval, which is exactly what you need to compute R10's surcharge at €0.20/day. TIMESTAMPTZ − TIMESTAMPTZ, on the other hand, does return an INTERVAL, and we will use it in section 18 for the duration of events.

PostgreSQL has BOOLEAN with three possible values: TRUE, FALSE and NULL (three-valued logic from 02-01).

Let us compare it with the habit inherited from old systems of using CHAR(1) with 'Y'/'N':

In BiblioRed these are BOOLEAN: members.active, rooms.accessible, events.published, speakers.external.

A clarification that avoids a frequent mistake: a NULL boolean means "not known", and that is sometimes legitimate (accessible for a room that has not been inspected yet) and sometimes an oversight. If the third value makes no sense in the domain, declare NOT NULL DEFAULT and remove the doubt. Every boolean in BiblioRed is NOT NULL.

When UUID pays off: identifiers that travel in public URLs, distributed systems that generate rows offline, merging independent databases.

BiblioRed's case: internal keys of a centralized database with moderate volumes. Sequential integer, no argument. The member_id and event_id values do not appear in public URLs; if tomorrow the website exposed /events/47, the solution is not to change the primary key but to add a separate public identifier:

That way the primary key stays compact for the foreign keys and the indexes, and public exposure does not leak the volume of the business. It is the best of both options and almost nobody considers it.

BiblioRed has eight columns with a closed set: materials.material_type, copies.status, reservations.status, events.status, registrations.status, fines.reason, fines.status, payments.method, member_phones.type, participations.role and events_materials.role. There are three ways of implementing it.

Compact (4 bytes), natural ordering following the declaration order, a perfectly clear error. Its two drawbacks are real: values cannot be removed (only added, and since PostgreSQL 12 with ADD VALUE), and it does not admit attributes: you cannot store a description for each status or its display order.

The values are managed with INSERT/UPDATE without touching the schema, they admit attributes and they can be translated. The price is a JOIN every time you want the readable name, and one more table.

No new types, no new tables, and modifying the list is dropping and recreating the constraint.

If the set is managed by the user, a lookup table. If it is managed by the developer and rarely changes, a CHECK. ENUM only when the set is genuinely immutable.

No column uses ENUM. The reason is portability —the schema must be loadable into SQLite for testing— and the rigidity of removing values. It is a defensible decision, not the only possible one.

In 03-03 we modeled documents in MongoDB and in 03-04 we saw that PostgreSQL stores documents with jsonb. Here comes the design question: when is it legitimate to use them in a relational schema?

An array is not a comma-separated list: it preserves the structure, it has operators of its own (@> contains, && overlaps) and it can be indexed with GIN (06-03). But compared with the dvd_subtitles table from 04-03 it loses three things: it cannot have a foreign key to a catalog of languages, it does not admit per-element attributes (are they subtitles for the hard of hearing?) and aggregations ("how many DVDs per language?") require unnest.

BiblioRed's decision: the table. The array is the right option when the elements are unstructured tags and are only queried for membership.

The legitimate use is genuinely variable data whose structure is not known at design time. In BiblioRed there is one case: the responses to the satisfaction surveys for events (R9). Each type of event has its own questionnaire, the number and type of questions changes, and questions get added without warning.

The line you must not cross: JSONB is not a place to dump columns so you do not have to design them. If a value is always queried, filtered, aggregated or has a business rule attached, it is a column. Putting actual_attendees inside the JSON would be the EAV anti-pattern from 04-01 with modern syntax.

Criterion: a column if the value has a known name, a known type and rules; JSONB if the shape is decided by somebody outside the schema. And if you end up writing a complex CHECK over a JSON key, that key wanted to be a column.

BYTEA stores binary data. The temptation is to store book covers and report PDFs inside the database. Do not do it, except for very good reasons:

The right approach: store the file in the file system or in object storage, and in the database the path and the metadata:

The legitimate exceptions are few and recognizable: small binaries (less than a few KB), not very numerous, that must take part in the database's transaction and access control —a digital signature, a timestamp token—.

BiblioRed handles titles in Spanish and Catalan. That turns two normally invisible parameters into design decisions.

UTF-8 represents any character of any language: ñ, ç, à, ï, · (the Catalan middle dot in "col·lecció"), typographic quotes and emoji. Any single-byte encoding (LATIN1) will break something sooner or later, and migrating afterwards is laborious. There is no decision to make here: UTF-8.

Collation is the set of rules for sorting and comparing strings. It is not a cosmetic detail: it decides the result of ORDER BY, of < and >, and of the indexes that rely on that order.

The "C" collation sorts by the byte value: all the uppercase letters before all the lowercase ones, and the accented characters at the end. It is fast and completely unacceptable for a library catalog.

This is the order anybody would expect: accents and capitalization do not alter the alphabetical position.

A member searching for "map of time" must find "The Map of Time", and somebody searching for "angels" must find "Àngels". Two pieces:

Since PostgreSQL 12 there is also a cleaner option: a non-deterministic collation that ignores accents and case for comparison purposes, applicable to a specific column.

BiblioRed's decision: the default collation en-US-x-icu on the database (correct sorting) and search with unaccent + ILIKE in the catalog queries. The code columns (isbn, copy code, language) carry an explicit "C" collation: they are codes, not text, and comparing byte by byte is the correct and fastest thing to do.

Operational warning: changing the collation of a database that has data invalidates the indexes built on text columns, because their order stops being valid. They have to be rebuilt (REINDEX). It is a decision taken when the database is created and not changed lightly.

In 01-04 we installed SQLite as a lightweight alternative. Its type system works in a way that surprises anyone coming from PostgreSQL, and you have to know about it because it is a classic source of corrupt data.

SQLite uses type affinity: the declared type is a preference, not a constraint.

No error at all. The same statement in PostgreSQL:

The differences that most affect design:

Since SQLite 3.37 (2021) there is a partial solution:

Recommendation for BiblioRed: PostgreSQL is the reference. If SQLite is used for local testing, tables have to be declared STRICT, PRAGMA foreign_keys = ON has to be enabled and money must not be stored in REAL —amounts are stored as integer cents—. Knowing that, SQLite is an excellent tool; not knowing it, it is a trap.

The second block begins. NOT NULL is the simplest constraint and the one most often decided out of inertia.

In 02-01 we saw that NULL is neither zero nor an empty string: it is "not known" or "not applicable". Allowing it in a column is asserting that that case exists in the business. The right question is: "is there a legitimate row in which this is not known?"

The companions row is the most instructive. It is the distinction between zero and unknown, and confusing it is the most common mistake with NULL. A member who comes alone has 0 companions, not an unknown number of companions. The combination NOT NULL DEFAULT 0 expresses exactly that and also makes SUM(1 + companions) work without COALESCE.

Practical tip: start by declaring everything NOT NULL and remove the constraint only where you can name the legitimate row that breaks it. The opposite default —allow nulls and restrict later— produces schemas where nobody knows which columns can be missing.

A DEFAULT applies when the column is omitted in the INSERT, not when it is explicitly given NULL:

The DEFAULT does not rescue an explicit NULL. It is correct behavior and it surprises a lot of people.

• CURRENT_DATE → today's date.
• now() / CURRENT_TIMESTAMP → the instant the transaction started, not the statement. Every row inserted in the same transaction carries the same stamp, which is usually what you want.
• clock_timestamp() → the real instant of each call. Used for measuring, almost never as a DEFAULT.

Putting the default value in the database guarantees that every insertion path respects it: the web application, an import script, a bulk load or a manual INSERT from psql. If the default value lives only in the application code, the first bulk load will skip it. It is the same argument that closes section 21.

UNIQUE guarantees that no two rows have the same value. In BiblioRed, each natural key from section 10 of 04-01 carries its own:

The composite one applies to the combination, not to each column:

Two rooms with the same name in different branches: correct according to R3.

In standard SQL, NULL is not equal to NULL (three-valued logic, 02-01). Since UNIQUE forbids equal values and two NULLs are not equal, a UNIQUE column accepts as many NULLs as you like:

Three speakers with no email, with UNIQUE (email). It is not a bug: it is what lets you combine "the email is optional" with "two speakers do not share an email", which was exactly what we needed in 04-03.

Remember the constraint uq_fines_loan_reason UNIQUE (loan_id, reason), which implemented R10 ("a loan generates at most one fine of each reason"). Since loan_id is nullable because of decision D6, it has a hole:

Three identical fines for the same loss, and the UNIQUE said nothing because each (NULL, 'loss') is different from the others. That is a real duplicate in production.

Since PostgreSQL 15 there is a declarative solution:

With NULLS NOT DISTINCT, NULLs are considered equal to each other for uniqueness purposes. In versions before 15 it was solved with a partial unique index, which is material for 06-03.

The underlying lesson: a UNIQUE over nullable columns does not guarantee what it appears to guarantee. Every time you declare one, check whether any of its columns allows NULL and decide consciously.

PRIMARY KEY is not a new constraint: it is equivalent to UNIQUE + NOT NULL, plus the role of default identifier for foreign keys.

The three differences that do matter:

1. There can only be one primary key per table; as many UNIQUE keys as you need.
2. REFERENCES table with no column implicitly points at the primary key.
3. Tools, ORMs and graphical clients use it to identify the row.

In BiblioRed the two uses coexist: materials has PRIMARY KEY (material_id) and also UNIQUE (material_id, material_type), the latter existing solely so that the subtables can reference it with the composite foreign key from rule 10.

This is where the ten business rules from 04-01 finally enter the schema.

Verification:

A CHECK declared at table level can refer to several columns of the same row:

Notice how chk_loans_return is built: it includes the NULL case explicitly. It is essential to know how CHECK treats nulls.

A CHECK accepts the row when the expression evaluates to TRUE or NULL. It only rejects it when it evaluates to FALSE.

It is accepted because return_date is NULL and NULL >= '2026-07-01' gives NULL, not FALSE. In this specific case that is what we wanted —a loan that has not been returned is valid— but the behavior is easy to get wrong. If you want to reject nulls, do it explicitly with NOT NULL or with IS NOT NULL inside the CHECK.

A CHECK only sees the row being inserted or modified. It cannot query other tables or other rows. That leaves four of the ten business rules out:

For BR4, PostgreSQL offers a specific and little-known tool, the exclusion constraint, which generalizes UNIQUE to any operator:

It reads: there cannot be two non-cancelled rows with the same room_id whose time ranges overlap.

BR4 guaranteed by the server, with correct concurrency and without a line of application code. It is one of the best things PostgreSQL offers and it has no equivalent in SQLite.

A generated column is computed from others in the same row. It is the correct way to materialize a simple derived value with no risk of going out of sync, because the manager maintains it and nobody can write to it.

You cannot lie. That is the essential difference from the manual denormalization of 05-04: here the manager guarantees consistency.

Two limits: in PostgreSQL only STORED exists (it is stored; there are no virtual columns computed on the fly), and the expression must be immutable and refer only to columns of the same row. That is why an event's free_seats —which aggregates another table— cannot be a generated column and remains the v_events_occupancy view from 04-03.

A domain is a type of your own built on top of another, with constraints baked in. It saves you repeating the same validation in fifteen places and lets the schema express the vocabulary of the business.

And they are used as if they were types:

Its drawback is portability: domains are standard SQL but SQLite does not have them, so a schema with domains needs an alternative version for local testing.

If you do not name a constraint, PostgreSQL gives it an automatic name. Compare the two messages.

Unnamed:

Named:

It looks like a cosmetic detail. It is not, for three specific reasons:

1. The support team reads the message. chk_rooms_capacity_positive is understandable without opening the schema; rooms_capacity_check forces you to investigate. With two CHECKs on the same column, the auto-generated name is rooms_capacity_check1, and at that point there is nothing to be done.
2. The application can react to the name. The code catches the error, reads the constraint name and shows the appropriate message to the user. With auto-generated names, that logic breaks as soon as somebody recreates a table and the suffixes change order.
3. Migrations need the name. ALTER TABLE ... DROP CONSTRAINT requires knowing it, and querying the catalog every time is unnecessary friction.

BiblioRed's convention:

And the shape of the name: <prefix>_<table>_<what it checks>. Names are global per schema, so including the table avoids collisions.

The real case: loans has 4,312 rows and no constraint on the surcharge. If you try to add it directly:

Two things happen. If there is data that breaks it:

And if there is not, PostgreSQL scans the entire table to verify it, holding a lock that prevents reads and writes. With 4,312 rows it is instantaneous; with ten million, it is a service outage.

Step 1 — Find out how many rows break it:

Step 2 — Fix the existing data:

Step 3 — Add the constraint with NOT VALID and validate it afterwards:

NOT VALID means: "the constraint applies from now on to every new or modified row, but do not check the ones already there". It is instantaneous and takes a much lighter lock. The database is immediately protected against incorrect new data.

Afterwards, in a quiet window:

VALIDATE CONSTRAINT scans the table, but with a lock that allows concurrent reads and writes. If it finds a row that breaks the constraint, it fails and the constraint stays NOT VALID; nothing is lost.

You can check the state in the catalog:

NOT VALID works the same way with foreign keys and is the standard technique for adding referential integrity to a large database without stopping the service. NOT NULL is the exception: it does not accept NOT VALID until PostgreSQL 18; the classic workaround is to add a CHECK (col IS NOT NULL) NOT VALID first, validate it and convert it afterwards.

In 02-06 we argued for referential integrity on the server. Now comes the general criterion, because not everything fits in the schema.

The database is the only point through which every path passes. The web application, the mobile application, the nightly catalog import script, the supplier's bulk load, the intern with psql and the migration process: they all write to the same database. A rule that lives only in the web application is broken by the other five.

Besides, applications get rewritten; the data remains. And a rule in the database applies retroactively in the sense that it prevents the problem from happening again, whereas fixing the code does not repair data that is already corrupt.

The last row makes a lot of people uncomfortable: is that not duplicated work? No, because they serve different purposes:

• The application validates to provide a good experience: a clear message next to the field, before the form is submitted, in the user's language.
• The database validates to provide a guarantee: nothing gets in wrong, no matter where it comes from.

The first can be bypassed; the second cannot. Duplicating format validation in both places is correct and expected. What is not correct is having it only in the application.

The rule that sums up the section: if the incorrect value would cause a problem even if nobody ever looked at it on a screen, the rule goes in the database.

Here is the result of the whole of module 4. It is migration V005, which revises the provisional types from 04-03 and adds the armor.

Five out of ten in the schema, with an absolute guarantee. The other five require querying other tables or aggregating rows, and they are documented with their location decided. That is design too: knowing exactly where each rule lives and why.

Storing money in REAL or DOUBLE PRECISION. The most expensive mistake in this lesson and the most frequent one in real projects. The amounts do not add up, the sums are off by cents and nobody finds the reason for weeks. NUMERIC, always.

Using TIMESTAMP with no time zone "because we only operate in one country". All it takes is a server in another zone, a container defaulting to UTC or a clock change for the event agenda to stop being reliable. TIMESTAMPTZ by default.

Storing dates as text. '14/05/2026' sorts wrong, does not allow subtraction, does not validate and depends on the regional format. A date is a DATE.

Putting VARCHAR(n) with an invented n. The error shows up in production with the first long value, and at the worst possible moment. TEXT plus a length CHECK if you need one.

Declaring UNIQUE over nullable columns without thinking. NULLs are not considered equal: the uniqueness you thought you had does not exist. It is the hole in uq_fines_loan_reason, and it is closed with NULLS NOT DISTINCT.

Confusing zero with unknown. companions = 0 means they are coming alone; companions IS NULL means we do not know. If the business does not admit the second case, declare NOT NULL DEFAULT 0.

Forgetting that CHECK accepts NULL. CHECK (amount > 0) does not prevent amount IS NULL. If the value is mandatory, you also need NOT NULL.

Not naming constraints. The day the error comes up in production at three in the morning, somebody will have to work out what events_check2 means.

Adding a constraint to a large table without NOT VALID. It locks the table during the full verification. With NOT VALID the protection is immediate and the verification happens later without interrupting the service.

Putting into JSONB what wanted to be a column. If a value is filtered, aggregated or has rules, it is a column. JSONB is for genuinely variable structures.

Tip: write the constraint first and then try to violate it. A CHECK you have never seen fail may be written wrong. Every error message in this chapter came out of running the INSERT that was supposed to fail.

Tip: go through the schema column by column asking "what absurd value fits here?". Zero capacity, a negative amount, an event of negative duration, a status with a typo, a three-digit postal code. Every answer is a missing constraint.

Tip: domains pay for themselves from the third identical column onwards. Three emails, four amounts, five language codes: if you are repeating the same CHECK, it is already a domain.

For each column, choose the definitive type and justify it in one or two sentences. State as well whether it should carry NOT NULL and with what DEFAULT.

1. rooms.square_meters — the room's floor area, with one decimal.
2. events.reduced_capacity_pct — integer percentage of capacity allowed (0-100).
3. payments.gateway_reference — identifier returned by the city council's gateway, a 32-character alphanumeric string.
4. members.birth_date — for statistics by age bracket.
5. materials.times_loaned — total number of historical loans of the material.
6. registrations.reminder_sent_at — when the reminder was sent, if it was sent.

For each rule, decide whether it can be expressed with a declarative constraint. If it can, write the named ALTER TABLE ... ADD CONSTRAINT; if it cannot, explain why and where it should live.

1. A report's average rating is between 0 and 5.
2. A cancelled event cannot be published.
3. A copy with the status 'withdrawn' cannot be loaned out.
4. A copy's code always starts with EJ-.
5. A room cannot have two overlapping events.
6. A member cannot have more than three phone numbers.
7. The fee for a speaker who is not external is always 0.

An outside team hands in this table to manage members' annual dues. Find at least seven type or constraint problems, explain the concrete damage in BiblioRed and write the corrected version.

Solution to Exercise 1

Number 5 is the key answer: the question asked for a type and the correct answer is that the column should not exist.

Solution to Exercise 2

Verification:

A comment on number 7: the temptation is "well, I'll copy external into participations". That is redundancy and it creates the possibility of the two copies disagreeing. The correct answer in v1.0 is the application, and to note it in the data dictionary.

Solution to Exercise 3

Corrected version:

The two improvements that go beyond fixing types are chk_dues_payment_consistency —which prevents the inconsistent state "paid with no payment date", a three-column CHECK— and the generated column final_amount, which guarantees that the applied discount can never go out of sync with the base amount.

This lesson has turned a structurally correct schema into a schema that defends itself, and with it module 4 closes.

On types:

• The type decides four things at once: which values fit, which operations make sense, how things are sorted and how much it costs. A badly chosen type does not raise an error: it silently produces incorrect results.
• Integers: SMALLINT documents the intent, INTEGER is more than enough for anything a person generates, BIGINT for what a machine generates. Migrating from INTEGER to BIGINT in a large, referenced table is one of the worst migrations there is.
• NUMERIC versus floating point is the section to remember: 3.10 + 2.20 + 4.30 gives 9.600000000000001 in DOUBLE PRECISION, and that is why a fully paid fine can show up as unpaid. Anything that is money goes in NUMERIC.
• Text: CHAR(n) almost never; VARCHAR(n) only when n comes from an external standard (ISBN, ISSN, postal code); TEXT for everything else, with a length CHECK if the business wants a cap.
• Dates: TIMESTAMPTZ by default for the instants of facts, because it stores the absolute moment and survives time zones and clock changes; DATE when the time does not exist in the domain; INTERVAL for computing due dates.
• BOOLEAN instead of 'Y'/'N', and NOT NULL when the third value means nothing.
• UUID only if identifiers travel through public URLs or there is distributed generation; for BiblioRed, a sequential integer, and if something has to be exposed, an additional public identifier.
• Closed sets: a lookup table if the user manages them, a CHECK if the developer manages them, ENUM only if they are genuinely immutable.
• JSONB and arrays are legitimate escape hatches for genuinely variable structures —the event surveys— not a place to dump columns you have not designed.
• BYTEA: cover images do not go in the database; they go in object storage, with their path and their hash in the database.
• Encoding and collation: UTF-8, no argument, and the collation decides whether "Àngels" sorts where a person expects. Codes carry the "C" collation; catalog text, en-US-x-icu with unaccent for searching.
• SQLite uses type affinity: it accepts text in an INTEGER column. With STRICT, PRAGMA foreign_keys = ON and amounts in integer cents, it is an excellent tool; without that, it is a trap.

On constraints:

• NOT NULL by default, and remove it only where you can name the legitimate row that breaks it. The distinction between zero and unknown is the most common mistake.
• DEFAULT lives in the database so that every write path respects it, not just the web application. It does not rescue an explicit NULL.
• UNIQUE over nullable columns does not guarantee what it appears to: several NULLs coexist, and that opened a real hole in uq_fines_loan_reason that was closed with NULLS NOT DISTINCT.
• CHECK accepts the row when the expression gives TRUE or NULL; it only rejects it on FALSE. And it cannot look at other rows or other tables, which leaves five of the ten business rules out. For non-overlap there is the exclusion constraint, which guarantees BR4 on the server with correct concurrency.
• Generated columns materialize same-row derived values with the manager's guarantee: they cannot be written by hand, so they cannot lie.
• Domains centralize a repeated validation and make the schema speak the language of the business.
• Naming constraints turns an unreadable production error into an immediate diagnosis, lets the application react to the name and makes migrations possible.
• NOT VALID + VALIDATE CONSTRAINT is the way to add integrity to a large table without stopping the service: it protects the new data immediately and verifies the old data afterwards.
• The underlying criterion: if the incorrect value would cause a problem even if nobody ever looked at it on a screen, the rule goes in the database. The application validates to give a good experience; the database validates to give a guarantee. Duplicating it is correct; having it only in the application is not.

With this, module 4, Schema Design, comes to a close, and the BiblioRed extension is finished from beginning to end: we started from one sentence from the city council, turned it into a requirements document with fourteen points, ten business rules and twelve queries; we drew it as a conceptual model with twenty-one entities and nine reasoned decisions; we transformed it into tables by applying ten mechanical rules; and we have armored it with types chosen one by one and constraints that make invalid states impossible. The schema we have now is not the one we would have written on day one by opening the editor, and that difference is exactly what this module teaches. In module 5, Normalization, we submit that schema to an examination we have deliberately avoided so far: we leave behind the intuition of "one thing, one place" and move to the formal toolkit —functional dependencies, first, second and third normal form, Boyce-Codd— to check with mathematics whether BiblioRed's design holds up, fix whatever does not, and then understand why it is sometimes worth breaking those rules on purpose.