| | 1 | = Relational Design |
| | 2 | |
| | 3 | == Descriptive representation of the relational schema |
| | 4 | |
| | 5 | Notation: **bold** = primary key, *italic* = foreign key. |
| | 6 | |
| | 7 | - **Users**(<u>**id**</u>, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at) |
| | 8 | - Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`. |
| | 9 | - **Crypto**(<u>**id**</u>, symbol, name, created_at) |
| | 10 | - Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`. |
| | 11 | - **Markets**(<u>**id**</u>, *crypto_id*, quote_currency, is_active, created_at) |
| | 12 | - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`. |
| | 13 | - **Holdings**(<u>**id**</u>, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at) |
| | 14 | - Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and |
| | 15 | `{user_id, crypto_id}` — the latter is the relationship's own key and is |
| | 16 | enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for |
| | 17 | consistency with the other relations. |
| | 18 | - `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`. |
| | 19 | - **Orders**(<u>**id**</u>, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at) |
| | 20 | - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`. |
| | 21 | - **Transactions**(<u>**id**</u>, *user_id*, type, amount, currency, *related_order*, created_at, description) |
| | 22 | - `type ∈ {deposit, buy, sell, fee}`. |
| | 23 | - **MarketTrades**(<u>**id**</u>, *market_id*, executed_at, price, quantity, side, source) |
| | 24 | - **MarketCandles**(<u>**id**</u>, *market_id*, timeframe, open, high, low, close, volume, candle_time) |
| | 25 | - `UNIQUE(market_id, timeframe, candle_time)`. |
| | 26 | - **Watchlists**(<u>**id**</u>, *user_id*, name, created_at) |
| | 27 | - **WatchlistItems**(<u>**id**</u>, *watchlist_id*, *crypto_id*, added_at) |
| | 28 | - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}` |
| | 29 | and `{watchlist_id, crypto_id}`, the latter enforced with |
| | 30 | `UNIQUE(watchlist_id, crypto_id)`. |
| | 31 | |
| | 32 | ==== Transformation method used |
| | 33 | |
| | 34 | **Partial transformation.** Applied as follows: |
| | 35 | |
| | 36 | - Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping |
| | 37 | its UUID (or serial) primary key. |
| | 38 | - Each **1:N relationship without attributes** is transformed by adding the |
| | 39 | parent's primary key as a foreign-key column on the child table — the "N" |
| | 40 | side. This is where every foreign key in the schema comes from, and it is why |
| | 41 | no foreign keys appear in the ER diagram itself: |
| | 42 | `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`, |
| | 43 | `Places` → `orders.user_id`, `Records` → `transactions.user_id`, |
| | 44 | `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`, |
| | 45 | `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`. |
| | 46 | - Each **M:N relationship** becomes its own table holding the two foreign keys |
| | 47 | plus the relationship's own attributes: `Holds` → `holdings`, |
| | 48 | `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's |
| | 49 | key and is enforced as a `UNIQUE` constraint in both tables. |
| | 50 | - **Total participation** in the ER model becomes `NOT NULL` on the |
| | 51 | corresponding foreign key; partial participation stays nullable. `Settles` is |
| | 52 | partial on both sides, which is exactly why `transactions.related_order` is |
| | 53 | the one nullable foreign key in the schema — a deposit has no originating |
| | 54 | order. |
| | 55 | |
| | 56 | ==== Normalisation |
| | 57 | |
| | 58 | All relations are in **3NF**: |
| | 59 | |
| | 60 | - Every attribute is atomic (no repeating groups, no composite fields). |
| | 61 | - No partial dependency exists because every primary key is a single UUID column. |
| | 62 | - 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. |
| | 63 | - `avg_price` in `Holdings` is a **derived value** cached for performance (it is |
| | 64 | the weighted-average entry price across all `buy` transactions for that |
| | 65 | `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. |
| | 66 | We accept the denormalisation: it is recomputed by the database inside the same |
| | 67 | transaction as each buy, in the same statement that changes the quantity |
| | 68 | (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average |
| | 69 | and the stored quantity can never disagree. |
| | 70 | - `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the |
| | 71 | P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` |
| | 72 | yields `NULL`, so a nullable average would have silently blanked the |
| | 73 | unrealised-P/L column for an existing position instead of failing loudly. |
| | 74 | |
| | 75 | === DDL script |
| | 76 | |
| | 77 | 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. |
| | 78 | |
| | 79 | The script creates: |
| | 80 | - 10 tables with check constraints, primary keys, foreign keys and unique constraints. |
| | 81 | - 5 performance indexes. |
| | 82 | - 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L). |
| | 83 | |
| | 84 | === DML script (sample data) |
| | 85 | |
| | 86 | The script that loads realistic sample data is [`../server/db/data_load.sql`](../../server/db/data_load.sql). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded: |
| | 87 | - 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets. |
| | 88 | - 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex). |
| | 89 | - 18 recent market trades across all markets so `v_latest_prices` is populated. |
| | 90 | - 10 one-hour candles (BTC and ETH). |
| | 91 | - One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly. |
| | 92 | - Two watchlists with five watchlist items. |
| | 93 | |
| | 94 | === Relational diagram |
| | 95 | |
| | 96 |  |
| | 97 | |
| | 98 | Generated in **Pgadmin** from the **live** `project` schema, in crow's-foot |
| | 99 | notation — not drawn by hand, so it is evidence that the deployed database |
| | 100 | actually matches the design described above. Each box is a table with its |
| | 101 | columns and declared types; key icons mark primary keys and the arrowed lines |
| | 102 | are the 12 declared foreign keys. |
| | 103 | |
| | 104 | ==== How to regenerate it |
| | 105 | |
| | 106 | **With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same |
| | 107 | way, so the result is equivalent in substance: |
| | 108 | |
| | 109 | 1. Connect to the project database. |
| | 110 | 2. Right-click the database → **ERD For Database** (or open a blank ERD and drag |
| | 111 | the `project` tables in). |
| | 112 | 3. Arrange the tables to mirror `ERModel_v01.png`. |
| | 113 | 4. **Download image** → PNG, then convert: |
| | 114 | `convert relational_schema.png relational_schema.jpg` |
| | 115 | |
| | 116 | === AI use |
| | 117 | |
| | 118 | Reasoning for the AI-assisted part of this phase, and the full interaction log, are on https://github.com/StefanTrsunov/bp/blob/main/docs/P2-RelationalDesign/RelationalDesignAIUsage.md |
