| 62 | | === Normalisation === |
| 63 | | |
| 64 | | > '''Validated in P5.''' Normalization derives this |
| 65 | | > exact schema independently — starting only from a single de-normalized relation of every |
| 66 | | > model attribute and its functional dependencies, with no reference to the ER-to-relational |
| 67 | | > transformation below — and shows it decomposes to '''BCNF''', one normal form stronger than |
| 68 | | > the 3NF claimed here. The two designs agree relation for relation and key for key, so |
| 69 | | > nothing here changed as a result; see that page's |
| 70 | | > discussion section for what the one real |
| 71 | | > difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this |
| 72 | | > design is still the one used from P5 onward. |
| 73 | | |
| 74 | | All relations are in '''3NF''': |
| 75 | | |
| 76 | | * Every attribute is atomic (no repeating groups, no composite fields). |
| 77 | | * No partial dependency exists because every primary key is a single UUID column. |
| 78 | | * 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. |
| 79 | | * `avg_price` in `Holdings` is a '''derived value''' cached for performance (it is |
| 80 | | the weighted-average entry price across all `buy` transactions for that |
| 81 | | `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. |
| 82 | | We accept the denormalisation: it is recomputed by the database inside the same |
| 83 | | transaction as each buy, in the same statement that changes the quantity |
| 84 | | (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average |
| 85 | | and the stored quantity can never disagree. |
| 86 | | * `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the |
| 87 | | P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` |
| 88 | | yields `NULL`, so a nullable average would have silently blanked the |
| 89 | | unrealised-P/L column for an existing position instead of failing loudly. |
| 90 | | * `holdings.reserved_quantity`, unlike `avg_price`, is '''not''' derived — it is |
| 91 | | written directly by the application (`trade.go`) as orders are placed and |
| 92 | | settled, the same way `quantity` itself is. `quantity - reserved_quantity` |
| 93 | | ("available") is the derived value here, and it is never stored, only |
| 94 | | computed where it is needed. |
| 95 | | |
