We go back to looking at the complete blueprint, as we promised when closing module 7. For seven modules you have built BiblioRed out of pieces: a table here, a JOIN there, an index when something ran slow. Each piece was explained separately because it had to be learned separately. In a real project nobody hands you the pieces in order: they hand you a conversation with a client and you have to get from there to a production system on your own.

That is what this lesson does. Vallmar City Council, happy with the library network, has just commissioned a second system: VallBici, the municipal bike-sharing service. It is not an extension of BiblioRed; it is a new domain, with its own rules, its own concurrency problems and its own reports. BiblioRed will show up now and then as a point of comparison —"we already solved this that way, here it changes because…"—, but the work is new from start to finish.

The route is that of a real project and in its real order: the commission, the written requirements, the conceptual model, the physical schema, the normalization check, the data load, the critical transactions, the reports, performance and operations. At every point where something has to be decided, the decision comes with its justification and the alternatives that were discarded. That is the content of the lesson: not the final SQL —which is copied in ten minutes— but the reasoning that leads to it.

And it ends where an honest case study has to end: by listing the three things this schema handles badly, which are exactly the material of lesson 08-02.

1. The commission: the initial conversation
2. VallBici requirements document v1.0
3. Conceptual model: entities, relationships and three hard decisions
4. The ER diagram
5. Physical schema: the annotated CREATE TABLE
6. The constraints that encode the business rules
7. Normalization check and two deliberate denormalizations
8. Data load and realistic volume
9. The two critical transactions: unlock and dock
10. Reporting queries: the city council's reports
11. Performance: the two queries that degrade
12. Operations: roles, personal data and backups
13. What this schema handles badly
14. Common Mistakes and Tips
15. Exercises
16. Conclusion

The first meeting with the council's Mobility department produces this, transcribed almost verbatim:

"We have 60 stations spread across the city's five districts and 900 bicycles, some mechanical and some electric. Each station has a fixed number of docks; a bike occupies a dock when it is parked. There are 24,000 subscribers. A person unlocks a bike at one station, makes their trip and docks it at another. There are three subscription types —annual, monthly and a three-day tourist one— and if you go over the included time a surcharge is billed. Bikes go through inspections, break down and are pulled into the workshop. The mobile app has to show availability in real time, remember the session, let people search for stations by name or by address and store the GPS trace of every trip. And we want monthly usage reports by district and time slot."

We apply the technique from 04-01: underline the nouns (entity candidates), the verbs (relationship candidates) and the phrases containing "cannot", "always", "each" (constraint candidates). But first we have to do something 04-01 insisted on a lot: ask again. A two-paragraph commission always hides decisions the client takes as obvious that are not.

Those seven answers are worth more than the next hundred lines of SQL. The fourth one in particular is what avoids the expensive mistake: if the fare is not frozen, the first price change rewrites two years of billing.

This list is part of the requirement, not an extra. It is what in 04-01 we called the schema's success criterion: a model that cannot answer them is a failed model, however elegant it may be.

• Q1 — Trips by district and time slot, month by month.
• Q2 — The ten most frequent origin→destination station pairs.
• Q3 — Stations that systematically empty out or fill up, by slot (the rebalancing problem: this is the one that really costs money, because it forces you to move bikes by van).
• Q4 — Revenue by subscription type, separating fees from surcharges.
• Q5 — Bicycles with the most breakdowns per hour of use (not in absolute terms: a heavily used bike breaks down more and that does not make it a bad one).

The text yields these without discussion: district, station, dock, bicycle, bike model, subscriber, subscription type, fare, subscription, trip, workshop order and charge. What is up for discussion are three decisions, and they are the ones that separate a model that holds up from one that does not.

The temptation: store in stations a total_docks column and an available_bikes one, and not model the dock at all. It is simpler and apparently sufficient: to draw the app you only need to know how many bikes there are.

Why it is discarded. Three reasons, in order of weight:

1. The client said that a dock can break down on its own. An attribute has no status; an entity does. Without docks, a station with 20 docks and 3 broken ones still "has capacity 20" and the system promises slots that do not exist.
2. The app has to tell the person which dock holds the bike they reserved and which one they should leave it in. That data does not exist if the dock does not exist.
3. BR1 and BR2 —"one dock, one bike; one bike, one dock"— are integrity constraints, and in module 4 we set the principle: a constraint the schema can enforce is not delegated to code. With docks as a table they are a primary key and a UNIQUE; without it, they are application code and trust.

How it is modeled: the dock is a weak entity of the station (rule 8 of 04-03), with composite primary key (station_id, dock_number). The number 7 only means something inside a specific station, exactly as the receipt number of a fine depended on the fine in BiblioRed.

A trip connects a person (via their subscription), a bicycle and two stations. In the terms of 04-02 it would be a degree-4 relationship, and high-degree relationships are almost always a symptom of a missing entity.

It is modeled as an entity, and for four reasons:

1. It has its own and abundant attributes: two instants, duration, amount, frozen fare.
2. It has identity: the same person can make the same trip between the same two stations with the same bike twice on the same day, and those are two distinct facts. A junction table with a composite key would confuse them.
3. It is born incomplete. When the bike is unlocked only half of the trip is known. A relationship that exists halfway is an entity with null columns, not a relationship.
4. Other things reference it: the surcharge charge points at the trip.

The two stations are two distinct 1:N relationships toward stations (rule 5 of 04-03): origin_station and destination_station. It is not a rare case: it is the same pattern as "flight with departure and arrival airport", and the only precaution is not to forget that the two foreign keys point at the same table, which forces you to use aliases in every query that uses them.

Fine detail: the foreign key does not point at stations but at docks(station_id, dock_number), because we care about knowing which specific dock it left from and which one it entered. The station is determined by the dock.

Every bicycle is either mechanical or electric (a total and disjoint hierarchy). Electric ones have three attributes that make no sense for mechanical ones: battery capacity, range and battery serial number. In 04-03, rule 10, we saw the three strategies. Let us go over the decision with the criteria from there:

We choose table per subclass (strategy 2), just as we did with BiblioRed's materials and for the same dominant reason: there are tables that reference the superclass. trips, docks and workshop_orders point at "a bicycle", regardless of type, and strategy 3 would make that impossible without duplicating every foreign key.

Strategy 1 (single table) was defensible —they are only three columns— and with 900 rows the waste is irrelevant. It is discarded on the fourth criterion: battery_serial has to be mandatory and unique on electric ones, and in a single table it could only be optional. It is exactly the argument that in BiblioRed made us split off materials_book for the isbn.

And as in 04-03, strategy 2 drags along its known hole: nothing on its own prevents a bike with type = 'mechanical' from having a row in electric_bicycles. That is plugged with the discriminated foreign key trick, which you will see in the schema.

Read it with the notation from 04-02: || is total participation and cardinality 1, o{ is cardinality N with partial participation, |{ is N with total participation. The fact that STATION ||--|{ DOCK is total on both sides says something true and non-trivial: a station with no docks at all is not a station, and a dock without a station does not exist. The fact that DOCK |o--o| BICYCLE is partial on both sides says the opposite: there are empty docks and there are bikes outside any dock (in circulation or in the workshop).

Two extensions before we start. btree_gist lets you mix in an EXCLUDE equality columns (an integer) with overlap columns (a range), which is exactly what BR3 and the fares ask for.

Why these types, one by one:

The discriminant trick, explained. The composite foreign key (bicycle_id, type) forces the referenced row in bicycles to have type = 'electric', because this table's CHECK pins type to that value. Result: it is impossible to register a battery for a mechanical bike. The hole that 04-03 left open in strategy 2 is plugged in the half that matters. The other half —an electric one having no row here— still cannot be enforced declaratively and is controlled in the registration process.

The two most important rules of the system are two lines of schema. The composite primary key stops dock 7 of station 12 from holding two bikes. The UNIQUE (bicycle_id) stops bike 417 from being in two docks simultaneously; it works because in PostgreSQL UNIQUE accepts as many NULLs as it likes, so all the empty docks coexist without conflict.

ON DELETE CASCADE from docks toward stations is justified: a dock has no life of its own outside its station (it is the action that 02-06 reserved precisely for weak entities). ON DELETE SET NULL toward bicycles is the right thing for the opposite: if a bike were to disappear from the system, the dock should be left free, not disappear.

About NUMERIC for money there is no debate and it is worth repeating because it is the most expensive mistake made with types: FLOAT does not represent 0.10 exactly, and a system that adds up 1.6 million amounts with FLOAT produces an accounting mismatch that nobody will be able to explain. NUMERIC(6,2) goes up to €9,999.99, more than enough for an annual fee.

DATERANGE instead of two start_date/end_date columns is what makes the EXCLUDE possible. With two loose columns, "these two subscriptions overlap" is a query with four comparisons that you have to get right every time; with a range it is the && operator and the engine enforces it.

Three types that deserve a comment:

• TIMESTAMPTZ, not TIMESTAMP. Vallmar changes its clocks twice a year. On the October Sunday when the clocks go back, a TIMESTAMP without a zone turns "02:30" into an ambiguous instant, and the trips of that early morning can come out with a negative duration. TIMESTAMPTZ stores an absolute instant and the ambiguity disappears. The price is remembering to convert to local time when grouping by time slot, which is what we will do explicitly in Q1.
• Generated INTERVAL. duration is not stored by hand: it is a generated column (04-04). It satisfies rule 4 of 04-03 —derived values are not stored— without giving up the ability to index it, because STORED does take disk space but is computed on its own and can never disagree with its sources.
• BIGINT on trip_id. With 1.6 million trips a year, INTEGER (2,147 million) would take more than a thousand years to run out. BIGINT is used anyway: changing the type of a primary key in production is one of the most painful migrations there is, and the cost today is four bytes per row.

chk_charge_source is an example of the pattern 04-04 called a cross-column coherence constraint: it is not enough for each column to be valid on its own; the combination has to make sense. A charge with concept subscription and a filled-in trip_id is incoherent data, and the schema rejects it.

It is worth seeing the three most interesting ones fail, because a constraint you have never seen fire is one you do not know works.

That last one is the most valuable of the three. The rule "a bike cannot be on two open trips" looks like pure application code, and in 90% of systems it is —with the result that, under load, two simultaneous requests cheerfully violate it. A partial unique index turns it into an engine guarantee that no race condition can dodge.

We go over the schema with the method from 05-03. For each table: identify the key, list the functional dependencies and check that every non-prime attribute depends on the whole key and on nothing else.

The note on bicycles.type. The dependency model_id → type exists, and model_id is not a key: it is a transitive dependency and therefore a textbook 3NF violation. It is kept for a concrete and verifiable reason: it is the column that makes the hierarchy discriminant work, and a foreign key cannot point at a value you have to go and look up in another table. And it generates no anomalies, because a model never changes type: a mechanical bike does not turn into an electric one. It is the case 05-02 described as "a transitive dependency on an immutable attribute", where the risk of an update anomaly is zero. Even so, it is shielded with a verification trigger on registration.

The note on subscriptions.subscription_type. Same reasoning and same conclusion: fare_id → subscription_type. It is kept because the reporting queries group by subscription type constantly and avoiding a JOIN in 80% of the reports justifies it. It is shielded with a composite foreign key:

Now the redundancy is impossible to contradict, which is the only acceptable way to live with a redundancy. It is exactly the same pattern as the bikes' discriminant.

What is broken: the value is derivable with SELECT COUNT(*) FROM docks WHERE station_id = ? AND bicycle_id IS NOT NULL.

Why it is accepted: the mobile app asks for the availability of all 60 stations every time somebody opens the map, and with 24,000 subscribers that is tens of thousands of requests a day, each degenerating into a count over docks. It is the textbook case from 05-04: massive reads, infrequent writes, small value.

How it is maintained: with a trigger on docks, not by hand from the application. Having the application maintain it is what guarantees it will disagree some day.

And, as 05-04 requires, an audit query that runs every night and warns if the counter has drifted:

What is broken: included_minutes, fraction_minutes, fraction_price and electric_surcharge depend on fare_id, not on trip_id.

Why it is accepted: it is not performance, it is correctness. It is the answer to the fourth question of the initial meeting. If the trip only stored fare_id, any later recomputation would apply the current fare; the day the city council raises the fraction price, the whole history would change amount. Historical data must be reproducible: it is an accounting requirement and sometimes a legal one.

How it is maintained: by copying the fare at the moment of unlocking, inside the transaction, and never touching it again. A BEFORE UPDATE trigger that rejects changes to those four columns is cheap and sensible protection.

Notice the difference between the two: the first is denormalized for speed and has to be watched; the second is denormalized for semantics and watching it would be a mistake, because its value must differ from the current one.

That 1,512 is the first useful sizing figure: 1,512 docks for 900 bicycles, a 68% average occupancy. That is healthy slack; below 80% rebalancing becomes an operational nightmare.

Every performance decision in section 11 refers to those 1.6 million annual trips. The other tables are irrelevant as far as execution plans go, and confusing that is the most usual way to waste an afternoon indexing the wrong thing.

This is where module 6 stops being theory. The scenario to solve is concrete: it is 08:12, the Harbor station has a single free bike and two people press "unlock" 40 milliseconds apart.

Why SKIP LOCKED and not plain FOR UPDATE. With FOR UPDATE, session B is left waiting for A to commit, and then re-evaluates: since the dock no longer holds a bike, it gets 0 rows. The final result is correct, but B has waited for nothing. With SKIP LOCKED, B ignores the locked row and keeps looking for another bike at the same station; only if there really is none left does it return 0 rows. At a station with 8 bikes and 8 people unlocking at once, the difference is that all 8 succeed in parallel instead of queueing. It is exactly the queue pattern we saw in 06-02, applied to an inventory.

Why FOR UPDATE OF d. Without the OF d, PostgreSQL would also lock the bicycles row, and that is not needed: the row that decides who wins is the dock's. Locking more than necessary multiplies deadlocks.

The isolation level is READ COMMITTED, the default one. There is no need to go up to REPEATABLE READ: there is no read that must repeat stably, and the explicit lock already resolves the race. Raising isolation "just in case" only adds serialization errors that would have to be retried.

A 68-minute trip on an annual subscription: 68 − 30 included = 38 minutes exceeded, ceil(38/15) = 3 fractions × €0.60 = €1.80… plus the €0.35 electric surcharge. The result above, €1.55, corresponds to 2 fractions (€1.20) plus €0.35, that is, to a 55-minute trip. Check the arithmetic yourself with both cases: verifying by hand the first amount a billing system produces is a habit that saves grief.

The three details that make this transaction robust:

1. AND t.end_ts IS NULL in the UPDATE's WHERE makes it idempotent. If the app retries the docking because it lost the response over the network, the second attempt affects 0 rows and does not bill again.
2. The RETURNING lets the application check how many rows it changed. Zero rows is not a silent success: it is an error that has to be handled.
3. The available_bikes counter is not touched here: the docks trigger updates it. If it were also touched by hand, the counter would go up by two at a time, and that is the most common denormalization bug there is.

Q1 — Trips by district and time slot.

You can read a real pattern there: Midtown and Industrial are clearly commuter districts (two peaks, little midday) while the Harbor peaks at midday — that is tourism, not commuting. The AT TIME ZONE 'Europe/Madrid' is not decorative: without it, in summer the slots would come out shifted by two hours.

Q2 — The most frequent origin→destination pairs, with their rank inside the district.

The RANK() OVER (PARTITION BY ...) over an aggregated COUNT(*) is the "top N per group" pattern you practiced in 07-04. Note that the window function is evaluated after the GROUP BY, which is why it can order by COUNT(*).

Q3 — The rebalancing problem. This is the query that really saves money, and the one that best demonstrates why we modeled the trip as an entity with two endpoints.

The signs tell the whole story: 7 and 41 are residential (they empty out in the morning, they fill back up in the afternoon) and 22 is a work destination. The net_month column says how many bikes have to be moved by van each month, and the 08-12 against the 16-20 says at what time they have to be moved. The technique —turning two columns of one row into two signed rows by means of UNION ALL— is the canonical way to treat any entity with two endpoints.

Q4 — Revenue by subscription type.

The pct_surcharge of the tourist subscription (36.9%) is the kind of finding that justifies the report: visitors go over the included time almost half the time. Either 15 minutes is too little, or the app does not warn them. It is a business conversation that only exists because the query made it possible.

Q5 — Breakdowns per hour of use.

The WHERE u.hours >= 50 is the important part and the one almost nobody includes: without it, a bike with 2 hours of use and one breakdown tops the list with 50 breakdowns/100 h and the report is worthless. Every rate needs a minimum denominator threshold.

With an empty table everything is fast. The day trips holds 1.6 million rows, two things break. We follow the order of intervention from 06-03: measure first, understand the plan, and only then touch anything.

Diagnosis: Rows Removed by Filter: 488153 for each of the 3 processes. 1.6 million rows are read to keep 45,180. It is the classic symptom from 06-03: a very selective filter (2.8%) resolved with a sequential scan.

Intervention. Here there is a real choice between two indexes:

trips is always inserted in chronological order and never reordered: the physical correlation of start_ts is practically 1. It is the exact case BRIN exists for.

From 2,189 ms to 119 ms, with a 48 KB index. Rows Removed by Index Recheck: 2841 is normal in BRIN: the index works by blocks, so it returns a few extra blocks and the engine discards the leftover rows. In exchange it takes up a thousand times less space than the B-tree and its maintenance on insertions is negligible.

It is the most frequent query in the system: the app runs it every time somebody opens it with a bike in progress.

And here is the pretty detail: the index that fixes it already exists. It is uq_open_trip_subscription, the partial unique index we created in section 5 to enforce BR5. A partial index over WHERE end_ts IS NULL covers, with 1.6 million rows in the table, only the 900 at most that can be open simultaneously. The only thing missing was an ANALYZE:

From 1,893 ms to 0.049 ms. The lesson is the one 06-03 kept repeating: a well-chosen constraint is also an index, and very often the index you need is one you already created without realizing it.

The three remaining indexes that do have to be created by hand:

The column order in the composite ones follows the rule from 06-03 —equality first, range afterwards— and answers Q2 and Q3. The second one is partial because open trips have no destination and contribute nothing to the index.

The GRANT SELECT, UPDATE (status) ON bicycles is the literal application of the least-privilege principle from 06-04: the workshop changes the status of a bike, and only the status. It cannot touch accumulated_km or plate.

The city council's reports do not need to know who made each trip. The view consumed by the analyst profile breaks the link:

Two honest observations about this:

1. The view does not truly anonymize. With district of residence, decade of age and trip pattern, a person with a distinctive route can be re-identifiable. Reducing the risk requires minimum aggregation (not publishing cells with fewer than N people) and that is a policy decision, not a SQL one.
2. Regulatory compliance is not decided by whoever designs the database. Legal basis for processing, retention periods for telemetry, impact assessment, right to erasure: all of that is reviewed by a data protection or compliance professional. What is a technical responsibility is that the schema allows compliance: that is why the ID document is stored as document_hash and not in the clear, that is why ON DELETE RESTRICT forces an explicit erasure procedure instead of cascade-deleting the accounting history, and that is why the view exists.

The last row is the one most often skipped and the only one that guarantees anything. In the words of 06-04: a backup that has never been restored is not a backup, it is a hope. With PITR configured, the scenario "at 11:40 somebody ran an UPDATE without a WHERE on fares" is solved by restoring to 11:39.

The system works, it meets the eight functional requirements and it answers the five queries. And even so there are three things from the initial commission that this schema handles badly. It is worth being precise, because they are exactly the material of the next lesson.

1. GPS telemetry. The commission asked to store the trace of every trip: positions every 5 seconds, battery level of the electric ones, incidents. An average 14-minute trip is ~170 points. With 1.6 million trips a year, that is a positions table of 272 million rows a year, each with trip_id, ts, lat, lon and little else. It can be done —PostgreSQL can take it— but it is a bad fit: this is data that is written massively, always read whole and by trip, never updated, does not need strict referential integrity and expires within a few months. You are paying the price of the relational model (index per row, WAL per row, visibility per row) for data that uses none of its advantages.

2. The enriched station profile. Our stations table has 11 flat columns. The profile the app wants has photos, access hours that vary per station, accessibility, whether it is under cover, whether it has an air pump, notes from the maintenance contractor, tags for nearby points of interest… and each station has a different subset of those attributes. Modeling that in the relational world leads to one of three outcomes, all bad: 40 null columns, a generic key-value table (the EAV anti-pattern that 04-01 flagged in red) or a new table for every attribute anyone comes up with.

3. Incidents with a variable structure. workshop_orders has a reason VARCHAR(60). But a "brakes" incident needs to record which brake and what measurement; a "battery" one needs charge cycles and voltage; a "vandalism" one needs photos and a police report number. These are different structures per type, and adding a new type should not require an ALTER TABLE in production.

All three have something in common: variable structure or high volume without needing transactions. And all three have something else in common, more important still: none of them is the transactional core. Nobody bills money on the basis of a GPS point. That is what makes it possible to take them out of PostgreSQL without putting at risk what matters, and it is exactly what lesson 08-02 does.

Mistake 1: starting with the CREATE TABLE. It is the mistake almost all the others derive from. The seven questions in section 1 cost twenty minutes of meeting and determined half the schema. If we had not asked them, we would have discovered the need for the frozen fare on the day of the first price rise, with two years of billing already issued.

Mistake 2: not modeling the dock "because a counter is enough". It works until the first broken dock. The general rule: if the client says something has its own status, it is an entity, even if it looks like a number.

Mistake 3: FLOAT for money. It still happens. NUMERIC for everything that is billed, invoiced or added up.

Mistake 4: TIMESTAMP without a zone in a system with a clock change. The October Sunday will produce trips with a negative duration and restoring that night will be a puzzle.

Mistake 5: maintaining a denormalized counter by hand. If available_bikes is updated from the application and from the trigger, the increment is doubled. One of the two, and preferably the trigger.

Mistake 6: raising the isolation level instead of locking the right row. SERIALIZABLE is not a magic solution: it turns a race into a serialization error the application has to retry. A FOR UPDATE SKIP LOCKED on the deciding row is cheaper and more predictable.

Tip 1: write the list of queries before the schema. It is the best detector of missing entities. Q3 is what confirmed that the trip needed its two endpoints as independent foreign keys.

Tip 2: every constraint you write, test it by failing. A constraint you have never seen reject an INSERT is one you do not know is written correctly.

Tip 3: check whether the index already exists before creating it. Degradation 2 solved itself. Unique indexes, partial or not, are full-blown indexes.

Tip 4: document the denormalizations in the schema itself. The comment -- Deliberate denormalization no. 1 stops somebody three years from now from "fixing" the schema by removing it. Better still: COMMENT ON COLUMN.

The city council wants to allow reserving a bicycle from the app: the person reserves a specific bike at a station and has 10 minutes to get there and unlock it; after that time, the reservation expires and the bike becomes available again. A person can only have one active reservation.

1. Decide whether the reservation is a new entity or a status of something existing, and justify it.
2. Write the CREATE TABLE (or the ALTER TABLE) with all the necessary constraints.
3. Write the reservation transaction, with its concurrency control.
4. Explain how reservations expire and why that decision.

A reduced fare is introduced for people holding a municipal social pass. Eligibility is certified every year and may stop being met. A trip is billed at the reduced fare if the person held a valid social pass at the moment of unlocking.

1. Model the social pass certification without duplicating the information in subscribers.
2. Explain why the EXCLUDE constraint on subscriptions is not enough here.
3. Write the query that computes, for last month, how much revenue the city council has forgone because of the reduced fares.

A ghost bicycle is one that has spent more than 48 hours on an open trip: it was stolen, the docking system broke or the app failed to close it. Write the query that lists them with the last known station, how long they have been out and the contact name of the person who unlocked them, ordered from oldest to most recent. Add the index that makes it efficient, or explain why none is needed.

Solution 1

1. A new entity. Three arguments: (a) it has its own attributes —reservation instant, expiry instant, outcome—; (b) it has history: we will want to know how many reservations expire unused, and an overwritten status leaves no trace; (c) it relates three things (person, bike and dock) with a temporality of its own. A status = 'reserved' in bicycles would allow none of the three.

2. The schema:

The two partial unique indexes enforce "one active reservation per person" and "one active reservation per bike" — the same pattern as BR4/BR5. The third one is a service index, for the expiry process.

3. The transaction:

The NOT EXISTS anti-join excludes bikes that are already reserved; the partial unique index is the safety net if two requests dodge it. Note that the bike is not marked as reserved in bicycles: the status lives in reservations, in a single place.

4. Expiry. Two options and one choice.

Discarded: a process that every minute marks the overdue ones as expired. It works, but it introduces a window in which the reservation is overdue and still shows as active, and it depends on the process not crashing.

Chosen: implicit expiry at read time. A reservation counts as active if outcome = 'active' AND expires_at > now(); the NOT EXISTS above adds that condition. The cleanup process still exists, but only to keep the history tidy, and its running late does not affect correctness. The general rule: when time determines the validity of a value, the truth must live in the query, not in a process.

Solution 2

1. The model. A certifications table with a validity range, not a boolean column in subscribers:

A has_social_pass BOOLEAN column would be the classic mistake: it does not store since when, it does not store the case file and, above all, it does not let you reconstruct whether they had it on the day of the trip. A certification is a fact with a date, and facts with dates go in rows, not in columns.

2. Why the EXCLUDE on subscriptions is not enough. That EXCLUDE guarantees there are no two overlapping subscriptions for the same person; it says nothing about which fare applies. The social pass is a condition orthogonal to the subscription and with its own validity: a person can have an annual subscription from 1 January to 31 December and a social pass that expires on 30 June. From 1 July onward, the same subscription must generate trips at the normal fare. Since the trip already freezes its fare (BR9), the system stays correct without touching anything else: it is enough for the unlock transaction to choose the fare by consulting social_passes with validity @> CURRENT_DATE.

3. The fiscal cost query:

The NULLIF protects against zero: if the reduced fare had fraction_price = 0, the division would blow up.

Solution 3

About the index: none is needed. The end_ts IS NULL filter is enormously selective —at most 900 rows out of 1.6 million— and uq_open_trip_bicycle already covers exactly that partial predicate. The planner can scan it whole (900 entries) and filter afterwards by start_ts; adding an index over (start_ts) WHERE end_ts IS NULL would marginally improve a query that runs once a day and would add maintenance to 4,400 daily insertions. It is not worth it. This is the reasoning 06-03 asked for: an index is justified by the frequency of the query and the cost of writing, not by the size of the table.

You have gone through an entire relational project: from a two-paragraph meeting transcript to a system with eleven tables, twenty constraints that encode real business rules, two correct concurrent transactions, five reporting queries and an operations plan.

What is worth taking away is not the schema —your next project's will be a different one— but the shape of the decisions. Every one of the important ones had the same format: a simple temptation, a question to the client that dismantled it, two or three alternatives laid out in a table and an explicit criterion for choosing. The dock is an entity because it has its own status. The trip is an entity because it is born incomplete and has identity. The hierarchy uses table per subclass because other tables reference the superclass and because the battery serial number has to be mandatory. available_bikes breaks 3NF for speed and is watched; the frozen fare breaks it for semantics and watching it would be a mistake. None of those sentences is an aesthetic preference: they are all arguments that can be discussed and, if need be, rebutted.

You have also seen that the tools of the course are not used one at a time. The EXCLUDE USING gist from 04-04 enforces a business rule from 04-01; the partial unique index that enforces BR5 turns out to be, without changing a line, the index that fixes the most frequent query in the system; the denormalization from 05-04 needs the trigger from 05-04 and the nightly audit from 05-04, all three or none. A production system is this: pieces of the syllabus working at the same time and holding each other up.

And it ends with a list of three failures, which is the most honest part of the case study. GPS telemetry, the enriched station profile and the incidents with a variable structure do not fit well in this schema, and forcing them in there would produce null columns, EAV tables and migrations every time somebody invents a new incident type. In lesson 08-02 we take those three exact pieces —not one more— and model them in MongoDB with the method from 03-03, checking what is gained, what is lost and why the temptation to "migrate everything to Mongo" is the wrong answer. The transactional core you have just built stays where it is: it is the one that bills the money.