Index: docs/P2-RelationalDesign/RelationalDesign.md
===================================================================
--- docs/P2-RelationalDesign/RelationalDesign.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05)
+++ docs/P2-RelationalDesign/RelationalDesign.md (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd)
@@ -1,19 +1,27 @@
# Relational Design
+This page transforms [ERModel](../P1-ConceptualModel/ERModel.md) **v05** into
+relations. Every relation below corresponds to exactly one entity set of the
+model, and every foreign key corresponds to exactly one relationship, so the
+two diagrams can be compared box for box and line for line (see
+[Relational diagram](#relational-diagram)).
+
## Descriptive representation of the relational schema
-Notation: **bold** = primary key, *italic* = foreign key.
-
-- **Users**(**id**, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at)
- - Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`.
+Notation: **bold** = primary key, *italic* = foreign key. After each foreign key
+comes the ER relationship it implements.
+
+- **Users**(**id**, username, email, full_name, password_hash, available_balance, invested_balance, reserved_balance, created_at, updated_at)
+ - Entity set `Users`. Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`.
- **Crypto**(**id**, symbol, name, created_at)
- - Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
-- **Markets**(**id**, *crypto_id*, quote_currency, is_active, created_at)
- - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`.
-- **Holdings**(**id**, *user_id*, *crypto_id*, quantity, reserved_quantity, avg_price, created_at, updated_at)
- - Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and
- `{user_id, crypto_id}` — the latter is the relationship's own key and is
- enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for
- consistency with the other relations.
+ - Entity set `Cryptos`. Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
+- **Markets**(**id**, *crypto_id* [`QuotedOn`], quote_currency, is_active, created_at)
+ - Entity set `Markets`. Candidate keys: `{id}`, `{crypto_id, quote_currency}`
+ (the model's rule "a crypto is quoted at most once per currency"),
+ enforced with `UNIQUE(crypto_id, quote_currency)`.
+- **Holdings**(**id**, *user_id* [`Holds`], *crypto_id* [`PositionIn`], quantity, reserved_quantity, avg_price, created_at, updated_at)
+ - Entity set `Holdings`. Candidate keys: `{id}` and `{user_id, crypto_id}`
+ (the model's rule "one holding per user and crypto"), enforced with
+ `UNIQUE(user_id, crypto_id)`.
- `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
- `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0
@@ -23,60 +31,102 @@
needed, in `v_portfolio` as `available_quantity` and in the sell path of
[UseCase0005](../P3-UseCaseModel/UseCase0005.md). See
- [ERModel](../P1-ConceptualModel/ERModel.md#holds--users-m--cryptos-n-partial-on-both-sides-with-attributes)
+ [ERModel](../P1-ConceptualModel/ERModel.md#holdings)
for why this mirrors `available_balance`/`invested_balance` on `Users`.
-- **Orders**(**id**, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
- - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
-- **Transactions**(**id**, *user_id*, type, amount, currency, *related_order*, created_at, description)
- - `type ∈ {deposit, buy, sell, fee}`.
-- **MarketTrades**(**id**, *market_id*, executed_at, price, quantity, side, source)
-- **MarketCandles**(**id**, *market_id*, timeframe, open, high, low, close, volume, candle_time)
- - `UNIQUE(market_id, timeframe, candle_time)`.
-- **Watchlists**(**id**, *user_id*, name, created_at)
-- **WatchlistItems**(**id**, *watchlist_id*, *crypto_id*, added_at)
- - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}`
- and `{watchlist_id, crypto_id}`, the latter enforced with
- `UNIQUE(watchlist_id, crypto_id)`.
+- **Orders**(**id**, *user_id* [`Places`], *market_id* [`PlacedOn`], side, type, status, quantity, filled_quantity, price, placed_at, executed_at)
+ - Entity set `Orders`. `side ∈ {buy, sell}`, `type ∈ {market, limit}`,
+ `status ∈ {open, partially_filled, executed, cancelled}`,
+ `0 ≤ filled_quantity ≤ quantity`.
+- **Transactions**(**id**, *user_id* [`Records`], type, amount, currency, *related_order* [`Settles`], created_at, description)
+ - Entity set `Transactions`. `type ∈ {deposit, buy, sell, fee}`.
+ `related_order` is nullable (see below).
+- **MarketTrades**(**id**, *market_id* [`Fills`], executed_at, price, quantity, side, source, *buy_order_id* [`FillsBuy`], *sell_order_id* [`FillsSell`])
+ - Entity set `MarketTrades`. `buy_order_id` and `sell_order_id` are both
+ nullable (see below).
+- **OrderEvents**(**id**, *order_id* [`Logs`], event_type, quantity, price, status_after, created_at)
+ - Entity set `OrderEvents`. `event_type ∈ {placed, partially_filled, filled, cancelled}`.
+- **MarketCandles**(**id**, *market_id* [`Aggregates`], timeframe, open, high, low, close, volume, candle_time)
+ - Entity set `MarketCandles`. Candidate keys: `{id}`, `{market_id, timeframe,
+ candle_time}` (the model's rule "one candle per market, timeframe and
+ bucket"), enforced with `UNIQUE(market_id, timeframe, candle_time)`.
+- **Watchlists**(**id**, *user_id* [`Owns`], name, created_at)
+ - Entity set `Watchlists`.
+- **WatchlistItems**(**id**, *watchlist_id* [`Contains`], *crypto_id* [`Lists`], added_at)
+ - Entity set `WatchlistItems`. Candidate keys: `{id}` and `{watchlist_id,
+ crypto_id}` (the model's rule "an asset at most once per list"), enforced
+ with `UNIQUE(watchlist_id, crypto_id)`.
### Transformation method used
-**Partial transformation.** Applied as follows:
-
-- Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping
- its UUID (or serial) primary key.
-- Each **1:N relationship without attributes** is transformed by adding the
- parent's primary key as a foreign-key column on the child table — the "N"
- side. This is where every foreign key in the schema comes from, and it is why
- no foreign keys appear in the ER diagram itself:
- `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`,
- `Places` → `orders.user_id`, `Records` → `transactions.user_id`,
- `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`,
- `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`.
-- Each **M:N relationship** becomes its own table holding the two foreign keys
- plus the relationship's own attributes: `Holds` → `holdings`,
- `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's
- key and is enforced as a `UNIQUE` constraint in both tables.
-- **Total participation** in the ER model becomes `NOT NULL` on the
- corresponding foreign key; partial participation stays nullable. `Settles` is
- partial on both sides, which is exactly why `transactions.related_order` is
- the one nullable foreign key in the schema — a deposit has no originating
- order.
+**Partial transformation.** The model has 11 entity sets and 15 relationships.
+Every relationship is binary and 1:N with no attributes of its own (the two M:N
+relationships of earlier versions, `Holds` and `Contains`, were corrected into
+the entity sets `Holdings` and `WatchlistItems` in v05). The rules:
+
+- **Each entity set becomes one relation**, with its own attributes and its
+ own key `id` as primary key. 11 entity sets → 11 relations.
+- **Each 1:N relationship becomes one foreign key** on the relation of the "N"
+ side, pointing to the primary key of the "1" side. No relationship gets its
+ own table, because none is M:N and none has attributes. 15 relationships →
+ 15 foreign keys:
+
+ | ER relationship | 1 side → N side | Foreign key | Participation of the N side | `NULL`? |
+ |---|---|---|---|---|
+ | `QuotedOn` | Cryptos → Markets | `markets.crypto_id` | total | `NOT NULL` |
+ | `PlacedOn` | Markets → Orders | `orders.market_id` | total | `NOT NULL` |
+ | `Places` | Users → Orders | `orders.user_id` | total | `NOT NULL` |
+ | `Records` | Users → Transactions | `transactions.user_id` | total | `NOT NULL` |
+ | `Settles` | Orders → Transactions | `transactions.related_order` | partial | nullable |
+ | `Fills` | Markets → MarketTrades | `market_trades.market_id` | total | `NOT NULL` |
+ | `FillsBuy` | Orders → MarketTrades | `market_trades.buy_order_id` | partial | nullable |
+ | `FillsSell` | Orders → MarketTrades | `market_trades.sell_order_id` | partial | nullable |
+ | `Logs` | Orders → OrderEvents | `order_events.order_id` | total | `NOT NULL` |
+ | `Aggregates` | Markets → MarketCandles | `market_candles.market_id` | total | `NOT NULL` |
+ | `Owns` | Users → Watchlists | `watchlists.user_id` | total | `NOT NULL` |
+ | `Holds` | Users → Holdings | `holdings.user_id` | total | `NOT NULL` |
+ | `PositionIn` | Cryptos → Holdings | `holdings.crypto_id` | total | `NOT NULL` |
+ | `Contains` | Watchlists → WatchlistItems | `watchlist_items.watchlist_id` | total | `NOT NULL` |
+ | `Lists` | Cryptos → WatchlistItems | `watchlist_items.crypto_id` | total | `NOT NULL` |
+
+- **Participation decides `NULL`.** Total participation of the N side means
+ every row must reference a parent, so the foreign key is `NOT NULL`. Partial
+ participation leaves it nullable. There are exactly three partial ones:
+ `Settles` (a deposit has no originating order), and `FillsBuy` / `FillsSell`
+ (a trade against the simulated market has no user order on that side).
+ Partial participation of the **1** side (for example, a user with no orders)
+ needs no column at all. It simply means no row points at that parent.
+- **Uniqueness rules of the model become `UNIQUE` constraints.** The four
+ rules the model states in words ("a crypto quoted once per currency", "one
+ candle per market, timeframe and bucket", "one holding per user and crypto",
+ "an asset once per list") involve a relationship, so Chen notation cannot
+ draw them as keys. After transformation, the relationship is a foreign-key
+ column, and each rule becomes an ordinary composite `UNIQUE` constraint, i.e. a
+ second candidate key.
+
+Nothing in the schema comes from anywhere else. Every column is either an ER
+attribute or the foreign key of one listed relationship.
### Normalisation
-> **Validated in P5.** [Normalization](../P5-Normalization/Normalization.md) derives this
-> exact schema independently — starting only from a single de-normalized relation of every
-> model attribute and its functional dependencies, with no reference to the ER-to-relational
-> transformation below — and shows it decomposes to **BCNF**, one normal form stronger than
-> the 3NF claimed here. The two designs agree relation for relation and key for key, so
-> nothing here changed as a result; see that page's
-> [discussion](../P5-Normalization/Normalization.md#discussion) for what the one real
-> difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this
-> design is still the one used from P5 onward.
-
-All relations are in **3NF**:
+> **Checked in P5.** [Normalization](../P5-Normalization/Normalization.md)
+> starts from a single de-normalized relation containing only the attributes
+> of the ER model and the functional dependencies that follow from its rules.
+> It decomposes that relation step by step to BCNF and arrives at these same 11
+> relations, with one deliberate difference: `transactions.user_id` (see the last
+> bullet below). The comparison is in the *Discussion* section at the end of that
+> page.
+
+All relations except `transactions` are in **BCNF**, as P5 shows. `transactions`
+is in 2NF but not in 3NF, because of the deliberately kept `user_id` (last
+bullet):
- Every attribute is atomic (no repeating groups, no composite fields).
-- No partial dependency exists because every primary key is a single UUID column.
-- No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate.
+- No partial dependency exists: every candidate key is either the single
+ column `id` or a composite key (`{user_id, crypto_id}`, …) on which no
+ non-key attribute depends only partially.
+- No transitive dependency exists, except `transactions.user_id` (last bullet):
+ every other non-key attribute depends directly on the row's own entity, never
+ on another entity reached through a foreign key.
+ For example, `holdings.quantity` depends on `holdings.id`, and nothing about
+ the user or the crypto is copied into `holdings`.
- `avg_price` in `Holdings` is a **derived value** cached for performance (it is
the weighted-average entry price across all `buy` transactions for that
@@ -95,4 +145,11 @@
("available") is the derived value here, and it is never stored, only
computed where it is needed.
+- `transactions.user_id` is kept **deliberately**, although for an entry that
+ settles an order it repeats that order's user (`related_order → user_id`, a
+ transitive dependency). A deposit has no order (`Settles` is partial), so
+ `user_id` is the only way to record whose deposit it is. For entries with an
+ order, the only code that sets `related_order` (the buy and sell inserts in
+ `advanced_db.sql`) writes both from the same order row. No database
+ constraint enforces this.
### Reservation and the order lifecycle
@@ -109,14 +166,23 @@
`SELECT … FOR UPDATE` locking that already protected `users.available_balance`
on the buy path is what makes two concurrent sell orders against the same
-holding serialize correctly instead of racing.
+holding serialize correctly instead of racing. The cash side of a buy order
+(`users.reserved_balance`), `orders.filled_quantity` and `order_events` were
+added in P7; see
+[AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md).
## DDL script
-The script that creates the entire schema is [`../server/db/schema_creation.sql`](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema.
+The script that creates the schema is [`../server/db/schema_creation.sql`](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema.
The script creates:
-- 10 tables with check constraints, primary keys, foreign keys and unique constraints.
-- 5 performance indexes.
+- 10 of the 11 tables, with check constraints, primary keys, foreign keys and unique constraints.
+- 8 performance indexes.
- 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L, plus `reserved_quantity` and the derived `available_quantity`).
+
+The 11th table, `order_events`, is created by
+[`../server/db/advanced_db.sql`](../../server/db/advanced_db.sql) together with
+the P7 triggers that fill it. `./eduberza -init` runs both scripts in that
+order, so a freshly initialised database always has all 11 tables and all 15
+foreign keys.
## DML script (sample data)
@@ -132,22 +198,52 @@
## Relational diagram
-
-
-Generated in **Pgadmin** from the **live** `project` schema, in crow's-foot
-notation — not drawn by hand, so it is evidence that the deployed database
-actually matches the design described above. Each box is a table with its
-columns and declared types; key icons mark primary keys and the arrowed lines
-are the 12 declared foreign keys.
+
+
+Generated in **DBeaver** from the **live** `project` schema (after
+`./eduberza -init`), not drawn by hand, so it shows what the deployed database
+actually contains. Each box is a table with its columns; the key icon marks the
+primary key, and the lines are the 15 declared foreign keys. The two foreign keys from
+`market_trades` to `orders` (`buy_order_id`, `sell_order_id`) connect the same
+two boxes, so DBeaver draws them on top of each other as one line.
+
+The tables are arranged in the **same positions** as the entity sets in
+`ERModel_v05.png`, so the two can be compared directly:
+
+- every rectangle of the ER diagram is one table in the same place;
+- every diamond of the ER diagram is one foreign-key line between the same two
+ boxes. The dot is on the referencing ("N") table, next to the foreign-key
+ column;
+- a double (total) line in the ER diagram is a `NOT NULL` foreign key, drawn by
+ DBeaver as a solid line. The three single lines on the N side (`Settles`,
+ `FillsBuy`, `FillsSell`) are the three nullable foreign keys, which DBeaver
+ draws dashed, with a hollow diamond on the `orders` side. The table under
+ [Transformation method used](#transformation-method-used) lists all 15.
+
+Earlier images (`relational_schema.jpg`, `relational_schema_v2.png`,
+`relational_schema_v3.png`) were exported from pgAdmin, with a different layout
+and from an older schema. They are kept only as history.
### How to regenerate it
-**With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same
-way, so the result is equivalent in substance:
-
-1. Connect to the project database.
-2. Right-click the database → **ERD For Database** (or open a blank ERD and drag
- the `project` tables in).
-3. Arrange the tables to mirror `ERModel_v03.png`.
-4. **Download image** → PNG, then convert:
- `convert relational_schema.png relational_schema.jpg`
-
+1. Initialise the database: `./eduberza -init` (runs `schema_creation.sql` and
+ `advanced_db.sql`, so `order_events` is included).
+2. In DBeaver, connect to the project database and expand
+ *Schemas → project → Tables*.
+3. Select all 11 tables → right-click → **View Diagram** (or create a new ER
+ diagram and drag the tables in).
+4. Drag each table to the position of its entity set in `ERModel_v05.png`:
+
+ ```
+ column 1 column 2 column 3 column 4
+ row 1 watchlist_items crypto markets market_candles
+ row 2 watchlists holdings . market_trades
+ row 3 users . orders .
+ row 4 . transactions order_events .
+ ```
+
+ Leave the empty cells (`.`) empty. They are where the relationship
+ diamonds are in the ER diagram, so the foreign-key lines will run through
+ the same gaps.
+
+5. Right-click the canvas → **Export diagram** → PNG, saved as
+ `relational_diagram_v4.png` in this folder.