Changes between Version 4 and Version 5 of ERModel


Ignore:
Timestamp:
09/17/26 00:19:39 (12 days ago)
Author:
231285
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v4 v5  
    1 == Diagram
    2 
    3 [[Image(https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModel_v01.png)]]
    4 
    5 == Data requirements
    6 
    7 === Entity sets
    8 
    9 ==== Users
    10 Registered participants of the platform. Every action in the simulation is
    11 attributed to a user, and the two balance attributes are what makes the
    12 simulation work: cash that is free to trade is tracked separately from cash that
    13 is currently committed to open positions, so the platform can refuse a purchase
    14 without having to recompute the whole portfolio first.
    15 
    16 - **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**.
    17   A surrogate UUID was chosen because it is opaque and stable — `username` and
    18   `email` are both things a user may legitimately want to change later, and
    19   every relationship in the diagram points at `Users`, so a mutable key would
    20   propagate changes across the whole database.
    21 - **Attributes:**
    22   - `id` — UUID, required, primary key.
    23   - `username` — text, max 50, required, unique.
    24   - `email` — text, max 255, required, unique, must contain `@`.
    25   - `full_name` — text, max 200, optional.
    26   - `password_hash` — text, max 255, required. Never the password itself; the
    27     prototype stores a SHA-256 hex digest.
    28   - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0.
    29   - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0.
    30   - `created_at` — timestamp with time zone, required, defaults to now.
    31   - `updated_at` — timestamp with time zone, optional (null until first change).
    32 
    33 ==== Cryptos
    34 The catalog of crypto assets the platform knows about. Kept separate from
    35 `Markets` because an asset exists independently of the pairs it is traded in —
    36 the same asset can be quoted against several currencies, and a user's holding is
    37 in the *asset*, not in a particular pair.
    38 
    39 - **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same
    40   reason as in `Users`; `symbol` is kept as a unique natural key because that is
    41   what users type and see.
    42 - **Attributes:**
    43   - `id` — UUID, required, primary key.
    44   - `symbol` — text, max 20, required, unique (e.g. `BTC`).
    45   - `name` — text, max 255, required (e.g. `Bitcoin`).
    46   - `created_at` — timestamptz, required, defaults to now.
    47 
    48 ==== Markets
    49 A tradeable pair: one crypto asset quoted in one currency, e.g. BTC/USD. This is
    50 where prices live, and it is the thing an order is placed *on*. Modeled as its
    51 own entity set rather than an attribute of `Cryptos` because a market has its own
    52 lifecycle — it can be deactivated without deleting the asset — and because
    53 trades, candles and orders all reference the pair, not the asset.
    54 
    55 - **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is
    56   unique by definition, since a given asset can only be quoted once per
    57   currency. Primary key: **`id`**, so that the many entity sets referencing a
    58   market carry one narrow column instead of a composite key.
    59 - **Attributes:**
    60   - `id` — UUID, required, primary key.
    61   - `quote_currency` — text, exactly 3 characters, required, default `USD`.
    62   - `is_active` — boolean, required, default true. Inactive markets are hidden
    63     from the trading menus but keep their history.
    64   - `created_at` — timestamptz, required, defaults to now.
    65 
    66 ==== Orders
    67 A user's instruction to buy or sell on a market. Needed as a separate entity set
    68 because an order is a record of *intent* that outlives its execution: it keeps
    69 the requested quantity and price even after it has been filled, which is what
    70 makes the ledger auditable.
    71 
    72 - **Candidate keys:** `{id}` only. There is no natural key — the same user can
    73   place two identical orders on the same market in the same second, and both are
    74   legitimately distinct. Primary key: **`id`**.
    75 - **Attributes:**
    76   - `id` — UUID, required, primary key.
    77   - `side` — text, required, restricted to `buy` or `sell`.
    78   - `type` — text, required, restricted to `market` or `limit`. The prototype
    79     executes only `market` orders; `limit` exists so the model does not have to
    80     change when limit orders are implemented.
    81   - `status` — text, required, restricted to `open`, `executed`, `cancelled`.
    82   - `quantity` — numeric(20,4), required, must be > 0.
    83   - `price` — numeric(18,6), optional — null for a market order until it fills,
    84     then the fill price.
    85   - `placed_at` — timestamptz, required, defaults to now.
    86   - `executed_at` — timestamptz, optional, set when the order fills.
    87 
    88 ==== Transactions
    89 The financial ledger: every movement of virtual cash, in one place. This exists
    90 so that a balance is never just a number someone edited — it is the sum of an
    91 auditable list of entries, which is also what the "explain every step" goal of
    92 the project needs.
    93 
    94 - **Candidate keys:** `{id}` only. Primary key: **`id`**.
    95 - **Attributes:**
    96   - `id` — UUID, required, primary key.
    97   - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`.
    98   - `amount` — numeric(18,4), required. Signed: negative for money leaving the
    99     cash balance, positive for money arriving.
    100   - `currency` — text, exactly 3 characters, required, default `USD`.
    101   - `created_at` — timestamptz, required, defaults to now.
    102   - `description` — text, optional, free-form human-readable explanation.
    103 
    104 ==== MarketTrades
    105 Individual executed trades on a market, from the user's own fills and from the
    106 market simulator. This is the single source of truth for the current price: the
    107 price of a market is the price of its most recent trade, never a column someone
    108 writes directly.
    109 
    110 - **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks
    111   unique, but two trades can share a timestamp, so it is not a safe key.
    112   Primary key: **`id`** (a plain auto-incrementing integer here rather than a
    113   UUID, because this is the highest-volume entity set and it is only ever read
    114   in timestamp order, never referenced by anything else).
    115 - **Attributes:**
    116   - `id` — integer, required, primary key, auto-generated.
    117   - `executed_at` — timestamptz, required.
    118   - `price` — numeric(18,6), required, must be > 0.
    119   - `quantity` — numeric(20,6), required, must be > 0.
    120   - `side` — text, optional, `buy` or `sell`.
    121   - `source` — text, max 50, required, default `simulation`. Distinguishes a
    122     simulated trade from a user's own fill (`user`).
    123 
    124 ===== MarketCandles
    125 OHLCV aggregates per market and timeframe — the data a price chart is drawn
    126 from. Stored rather than computed on the fly because the point of the project is
    127 a chart-driven interface, and re-aggregating the whole trade history for every
    128 screen refresh does not scale.
    129 
    130 - **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a
    131   market has exactly one candle per timeframe per time bucket. Primary key:
    132   **`id`**; the composite is enforced as a uniqueness rule because it is the
    133   real-world constraint and it is what prevents duplicate candles.
    134 - **Attributes:**
    135   - `id` — integer, required, primary key, auto-generated.
    136   - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`.
    137   - `open`, `high`, `low`, `close` — numeric(18,6), all required.
    138   - `volume` — numeric(20,6), required.
    139   - `candle_time` — timestamptz, required — the start of the bucket.
    140 
    141 ===== Watchlists
    142 A named list of assets a user wants to monitor. A separate entity set rather than
    143 a flag on the relationship between users and assets, because a user may want
    144 several lists ("long term", "watching today") and each needs its own name.
    145 
    146 - **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names
    147   are required to be unique per user; the model does not impose that, so it is
    148   not listed as a candidate key. Primary key: **`id`**.
    149 - **Attributes:**
    150   - `id` — UUID, required, primary key.
    151   - `name` — text, max 100, required.
    152   - `created_at` — timestamptz, required, defaults to now.
    153 
    154 ==== Relationships
    155 
    156 ===== QuotedOn — Cryptos (1) : Markets (N), total on Markets
    157 Ties a market to the asset it trades. One asset can be quoted in many markets;
    158 every market must have exactly one asset, hence total participation on the
    159 `Markets` side. No attributes of its own.
    160 
    161 ===== PlacedOn — Markets (1) : Orders (N), total on Orders
    162 Records which market an order was placed on. Every order must name a market;
    163 a market may have no orders yet. No attributes.
    164 
    165 ===== Places — Users (1) : Orders (N), total on Orders
    166 Records who placed an order. Every order belongs to exactly one user; a new user
    167 has no orders. No attributes.
    168 
    169 ===== Records — Users (1) : Transactions (N), total on Transactions
    170 Attributes each ledger entry to a user. Every entry belongs to exactly one user.
    171 No attributes.
    172 
    173 ===== Settles — Orders (1) : Transactions (N), partial on both sides
    174 Links a ledger entry to the order that caused it. Partial on the `Transactions`
    175 side because deposits have no originating order, and partial on the `Orders` side
    176 because an order that never executes never produces a ledger entry. This is why
    177 the corresponding column is nullable in P2. No attributes.
    178 
    179 ===== Fills — Markets (1) : MarketTrades (N), total on MarketTrades
    180 Every executed trade happened on exactly one market. No attributes.
    181 
    182 ===== Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles
    183 Every candle summarises trades of exactly one market. No attributes.
    184 
    185 ===== Owns — Users (1) : Watchlists (N), total on Watchlists
    186 Every watchlist belongs to exactly one user. No attributes.
    187 
    188 ===== Holds — Users (M) : Cryptos (N), partial on both sides, **with attributes**
    189 A user's position in an asset. M:N because one user holds many assets and one
    190 asset is held by many users, and partial on both sides because a user may hold
    191 nothing and an asset may be held by nobody. Modeled as a relationship rather
    192 than an entity set because a position has no identity of its own — it is
    193 entirely described by *which user*, *which asset*, and how much.
    194 
    195 - **Attributes:**
    196   - `quantity` — numeric(20,4), required, must be ≥ 0.
    197   - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse):
    198     the weighted average of the prices at which the position was accumulated.
    199     It is derivable from the buy history, and is stored anyway so that
    200     unrealised P/L can be shown without replaying the whole ledger.
    201   - `created_at` — timestamptz, required, defaults to now.
    202   - `updated_at` — timestamptz, optional.
    203 
    204 ===== Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute**
    205 Which assets are on which watchlist. M:N: a list holds many assets, an asset
    206 appears on many lists. Partial on both sides — an empty list is valid and an
    207 asset need not be on any list.
    208 
    209 - **Attributes:**
    210   - `added_at` — timestamptz, required, defaults to now. Recorded so a list can
    211     be shown in the order the user built it.
    212 
    213 === Entity-Relationship Model History
    214 
    215 - **v01** — First complete version. Built from the entity notes in
    216   [`ep-diagram.md`](ep-diagram.md) (the initial hand-written model), with three
    217   changes made to that initial model while drawing it:
    218   1. `Markets` was promoted from an implied attribute of the asset to its own
    219      entity set, so that prices, orders, trades and candles can all reference a
    220      pair rather than an asset.
    221   2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships
    222      `Holds` and `Contains` with their own attributes, instead of entity sets
    223      with foreign keys — the initial notes listed them as tables, which is a
    224      relational concept that does not belong in a Chen ERD.
    225   3. `avg_price` was marked as a derived attribute rather than a plain one, to
    226      make the denormalisation explicit rather than hidden.
    227 
    228 Reasoning for the AI-assisted part of this phase, and the full interaction log,
    229 are on https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md.
     1== Normalization
     2
     3This phase deliberately ignores the design from
     4[ERModel](../P1-ConceptualModel/ERModel.md) (P1) and
     5[RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) (P2) as a
     6starting point.
     7
     8Instead, it starts from a single flat relation containing every attribute of the
     9model, derives the functional dependencies that hold on that relation, and
     10decomposes it formally using Armstrong's axioms and the standard definitions of
     111NF, 2NF, 3NF and BCNF.
     12
     13The [Final result and discussion](#final-result-and-discussion) section then
     14compares the result of this independent normalization process with the P2
     15relational design.
     16
     17== De-normalized database form
     18
     19=== Building one relation from the whole model
     20
     21The ER model contains ten entity/relationship sets carrying attributes:
     22
     23* `Users`
     24* `Cryptos`
     25* `Markets`
     26* `Holds`
     27* `Orders`
     28* `Transactions`
     29* `MarketTrades`
     30* `MarketCandles`
     31* `Watchlists`
     32* `Contains`
     33
     34Eight additional relationships (`QuotedOn`, `PlacedOn`, `Places`, `Records`,
     35`Settles`, `Fills`, `Aggregates`, `Owns`) carry no attributes of their own.
     36
     37In Chen notation these relationships require no attributes because the
     38relationship itself represents the connection between entity sets. A single flat
     39relation has no such mechanism, so the referenced key must be represented by an
     40ordinary attribute. The following foreign-key-style attributes are therefore
     41included in the flat relation:
     42
     43* `M_CRYPTO_ID`
     44* `O_USER_ID`
     45* `O_MARKET_ID`
     46* `T_USER_ID`
     47* `T_RELATED_ORDER`
     48* `MT_MARKET_ID`
     49* `MC_MARKET_ID`
     50* `W_USER_ID`
     51* `H_USER_ID`
     52* `H_CRYPTO_ID`
     53* `WI_WATCHLIST_ID`
     54* `WI_CRYPTO_ID`
     55
     56These attributes are not copied from the P2 schema as a design decision. They
     57are required because a flat relation cannot otherwise preserve the links
     58expressed by the original ER relationships.
     59
     60Because names such as `id`, `created_at`, `quantity`, `type`, `name`, `price`
     61and `side` occur in more than one entity or relationship set, every attribute
     62is prefixed with a short code identifying its origin.
     63
     64| Prefix | Origin                              | Attributes                                                                                                                     |
     65| ------ | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------ |
     66| `U_`   | Users                               | `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` |
     67| `C_`   | Cryptos                             | `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT`                                                                                         |
     68| `M_`   | Markets + `QuotedOn`                | `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT`                                                               |
     69| `H_`   | Holds + surrogate key               | `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT`                       |
     70| `O_`   | Orders + `PlacedOn`, `Places`       | `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT`                      |
     71| `T_`   | Transactions + `Records`, `Settles` | `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION`                                  |
     72| `MT_`  | MarketTrades + `Fills`              | `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE`                                               |
     73| `MC_`  | MarketCandles + `Aggregates`        | `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME`                             |
     74| `W_`   | Watchlists + `Owns`                 | `W_ID, W_USER_ID, W_NAME, W_CREATED_AT`                                                                                        |
     75| `WI_`  | Contains + surrogate key            | `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT`                                                                            |
     76
     77`H_ID` and `WI_ID` are included because `Holds` and `Contains` are M:N
     78relationships with attributes and therefore require their own identifying
     79attribute in the relational representation.
     80
     81The resulting flat relation is:
     82
     83## [source,sql]
     84
     85R_EDUBERZA(
     86U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH,
     87U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT,
     88
     89C_ID, C_SYMBOL, C_NAME, C_CREATED_AT,
     90
     91M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT,
     92
     93H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY,
     94H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT,
     95
     96O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS,
     97O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT,
     98
     99T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER,
     100T_CREATED_AT, T_DESCRIPTION,
     101
     102MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY,
     103MT_SIDE, MT_SOURCE,
     104
     105MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW,
     106MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME,
     107
     108W_ID, W_USER_ID, W_NAME, W_CREATED_AT,
     109
     110WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT
     111)
     112-
     113
     114This relation contains 68 attributes.
     115
     116Every attribute is single-valued and atomic: balances, timestamps, identifiers,
     117symbols, amounts and similar values are all scalar values rather than lists or
     118nested records.
     119
     120Therefore `R_EDUBERZA` satisfies 1NF as written. The remaining sections
     121determine whether it satisfies 2NF, 3NF or BCNF.
     122
     123== Functional dependencies
     124
     125=== Canonical cover
     126
     127The initial functional dependencies are read directly from the identifying
     128constraints of the entity and relationship sets.
     129
     130Each entity's or relationship's key determines the attributes belonging to that
     131entity or relationship, while unique candidate keys determine the corresponding
     132primary key.
     133
     134| #    | Functional dependency                                                                                                            | Source                                                      |
     135| ---- | -------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------- |
     136| FD1  | `U_ID -> U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | Users                                                       |
     137| FD2  | `U_USERNAME -> U_ID`                                                                                                             | Users (`UNIQUE(username)`)                                  |
     138| FD3  | `U_EMAIL -> U_ID`                                                                                                                | Users (`UNIQUE(email)`)                                     |
     139| FD4  | `C_ID -> C_SYMBOL, C_NAME, C_CREATED_AT`                                                                                         | Cryptos                                                     |
     140| FD5  | `C_SYMBOL -> C_ID`                                                                                                               | Cryptos (`UNIQUE(symbol)`)                                  |
     141| FD6  | `M_ID -> M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT`                                                               | Markets                                                     |
     142| FD7  | `{M_CRYPTO_ID, M_QUOTE_CURRENCY} -> M_ID`                                                                                        | Markets (`UNIQUE(crypto_id, quote_currency)`)               |
     143| FD8  | `H_ID -> H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT`                       | Holds                                                       |
     144| FD9  | `{H_USER_ID, H_CRYPTO_ID} -> H_ID`                                                                                               | Holds (`UNIQUE(user_id, crypto_id)`)                        |
     145| FD10 | `O_ID -> O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT`                      | Orders                                                      |
     146| FD11 | `T_ID -> T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION`                                  | Transactions                                                |
     147| FD12 | `MT_ID -> MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE`                                               | MarketTrades                                                |
     148| FD13 | `MC_ID -> MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME`                             | MarketCandles                                               |
     149| FD14 | `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} -> MC_ID`                                                                          | MarketCandles (`UNIQUE(market_id, timeframe, candle_time)`) |
     150| FD15 | `W_ID -> W_USER_ID, W_NAME, W_CREATED_AT`                                                                                        | Watchlists                                                  |
     151| FD16 | `WI_ID -> WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT`                                                                            | Contains                                                    |
     152| FD17 | `{WI_WATCHLIST_ID, WI_CRYPTO_ID} -> WI_ID`                                                                                       | Contains (`UNIQUE(watchlist_id, crypto_id)`)                |
     153
     154No dependency above contains an extraneous attribute on its determinant, and the
     155dependent attributes are assigned to the entity or relationship that actually
     156owns them.
     157
     158=== Minimality of the composite dependencies
     159
     160The composite determinants in FD7, FD9, FD14 and FD17 are minimal.
     161
     162For example, in FD7:
     163
     164`{M_CRYPTO_ID, M_QUOTE_CURRENCY} -> M_ID`
     165
     166`M_CRYPTO_ID` alone does not determine `M_ID`, because the same crypto may be
     167traded in several quote currencies. `M_QUOTE_CURRENCY` alone also does not
     168determine `M_ID`, because many different crypto assets may use the same quote
     169currency.
     170
     171Therefore neither attribute can be removed from the determinant.
     172
     173The same reasoning applies to:
     174
     175* FD9: `{H_USER_ID, H_CRYPTO_ID} -> H_ID`
     176* FD14: `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} -> MC_ID`
     177* FD17: `{WI_WATCHLIST_ID, WI_CRYPTO_ID} -> WI_ID`
     178
     179Their left-hand sides are exactly the uniqueness constraints of the corresponding
     180relations.
     181
     182=== Dependencies carried by foreign keys
     183
     184The flat relation also carries dependencies induced by foreign keys.
     185
     186Because a referencing attribute takes its values from the candidate-key domain of
     187another entity, the attributes determined by that referenced key are also fixed
     188for the corresponding foreign-key value.
     189
     190| Foreign key       | References | Therefore determines                                                     |
     191| ----------------- | ---------- | ------------------------------------------------------------------------ |
     192| `M_CRYPTO_ID`     | `C_ID`     | `C_SYMBOL, C_NAME, C_CREATED_AT`                                         |
     193| `H_USER_ID`       | `U_ID`     | all `U_*` attributes                                                     |
     194| `H_CRYPTO_ID`     | `C_ID`     | all `C_*` attributes                                                     |
     195| `O_USER_ID`       | `U_ID`     | all `U_*` attributes                                                     |
     196| `O_MARKET_ID`     | `M_ID`     | all `M_*` attributes, and transitively `C_*`                             |
     197| `T_USER_ID`       | `U_ID`     | all `U_*` attributes                                                     |
     198| `T_RELATED_ORDER` | `O_ID`     | all `O_*` attributes, and transitively `U_*`, `M_*`, `C_*` when not null |
     199| `MT_MARKET_ID`    | `M_ID`     | all `M_*` attributes, and transitively `C_*`                             |
     200| `MC_MARKET_ID`    | `M_ID`     | all `M_*` attributes, and transitively `C_*`                             |
     201| `W_USER_ID`       | `U_ID`     | all `U_*` attributes                                                     |
     202| `WI_WATCHLIST_ID` | `W_ID`     | all `W_*` attributes, and transitively `U_*`                             |
     203| `WI_CRYPTO_ID`    | `C_ID`     | all `C_*` attributes                                                     |
     204
     205These dependencies are not added as separate entries to the canonical cover.
     206They are derivable from FD1-FD17 together with the foreign-key constraints and
     207transitivity.
     208
     209They are nevertheless important during the 3NF analysis because they are the
     210source of the transitive dependencies that would exist if the referenced
     211attributes were copied into the referencing relation.
     212
     213== Candidate keys and primary key
     214
     215=== Candidate-key structure of `R_EDUBERZA`
     216
     217The entity and relationship clusters in `R_EDUBERZA` are structurally
     218independent.
     219
     220For example, an order identifier tells us nothing about which unrelated
     221transaction, watchlist item, holding or market-candle tuple happens to coexist
     222in the same flat row. A user may have zero orders, zero holdings, zero
     223transactions, and zero watchlists, so none of those identifiers can be inferred
     224from another cluster.
     225
     226The nullable `T_RELATED_ORDER` attribute also cannot serve as a universal bridge
     227between transactions and orders.
     228
     229Therefore a key for `R_EDUBERZA` must contain one determinant from each
     230independent cluster.
     231
     232=== Chosen primary key
     233
     234The chosen key is:
     235
     236## [source,text]
     237
     238## {U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID}
     239
     240=== Closure check
     241
     242Applying FD1-FD17 to this attribute set gives the following closure:
     243
     244| Step  | Attributes added                                                                                                         | Dependency |
     245| ----- | ------------------------------------------------------------------------------------------------------------------------ | ---------- |
     246| Start | `U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID`                                                          | —          |
     247| 1     | `U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | FD1        |
     248| 2     | `C_SYMBOL, C_NAME, C_CREATED_AT`                                                                                         | FD4        |
     249| 3     | `M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT`                                                               | FD6        |
     250| 4     | `H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT`                       | FD8        |
     251| 5     | `O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT`                      | FD10       |
     252| 6     | `T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION`                                  | FD11       |
     253| 7     | `MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE`                                                | FD12       |
     254| 8     | `MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME`                              | FD13       |
     255| 9     | `W_USER_ID, W_NAME, W_CREATED_AT`                                                                                        | FD15       |
     256| 10    | `WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT`                                                                             | FD16       |
     257
     258The closure contains all 68 attributes, so the set is a superkey.
     259
     260Removing any one of the ten identifier attributes leaves an entire independent
     261cluster unreachable. Therefore the key is minimal and is a candidate key.
     262
     263=== Other candidate keys
     264
     265The chosen primary key is not the only candidate key.
     266
     267Any determinant that uniquely identifies one cluster can replace that cluster's
     268surrogate identifier:
     269
     270* `U_USERNAME` or `U_EMAIL` can replace `U_ID`.
     271* `C_SYMBOL` can replace `C_ID`.
     272* `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` can replace `M_ID`.
     273* `{H_USER_ID, H_CRYPTO_ID}` can replace `H_ID`.
     274* `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` can replace `MC_ID`.
     275* `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` can replace `WI_ID`.
     276
     277This produces:
     278
     279`3 x 2 x 2 x 2 x 1 x 1 x 1 x 2 x 1 x 2 = 96`
     280
     281candidate keys.
     282
     283The all-surrogate-ID combination is chosen as the primary key because the
     284surrogate identifiers are opaque, stable and independent of user-facing values
     285such as usernames, e-mail addresses and symbols.
     286
     287=== Normal form before decomposition
     288
     289`R_EDUBERZA` is in 1NF only.
     290
     291It is not in 2NF because non-prime attributes depend on proper subsets of its
     292ten-attribute candidate key. Since 2NF is a precondition for both 3NF and BCNF,
     293the relation is also not in 3NF or BCNF.
     294
     295== 1NF decomposition
     296
     297No decomposition is necessary for 1NF.
     298
     299`R_EDUBERZA` already contains only atomic, single-valued attributes and has no
     300repeating groups.
     301
     302The real decomposition therefore begins with the 2NF analysis.
     303
     304== 2NF decomposition
     305
     306=== Relation analyzed
     307
     308`R_EDUBERZA`, containing all 68 attributes, with primary key
     309
     310`{U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID}`.
     311
     312FD1-FD17 are in force.
     313
     314=== 2NF violations
     315
     3162NF forbids a non-prime attribute from depending on a proper subset of a
     317candidate key.
     318
     319Every dependency in the canonical cover has a determinant smaller than the
     320ten-attribute key:
     321
     322* `U_ID`
     323* `C_ID`
     324* `M_ID`
     325* `H_ID`
     326* `O_ID`
     327* `T_ID`
     328* `MT_ID`
     329* `MC_ID`
     330* `W_ID`
     331* `WI_ID`
     332* or one of the corresponding composite candidate keys.
     333
     334Therefore every non-prime attribute in `R_EDUBERZA` is involved in a partial
     335dependency.
     336
     337This is not a small collection of isolated violations. The entire flat relation
     338violates 2NF because ten independent record types were artificially combined
     339under one composite key.
     340
     341=== Decomposition by 3NF/BCNF synthesis
     342
     343Since a canonical cover is already available, the decomposition uses the
     344standard synthesis approach rather than repeatedly applying binary
     345decomposition.
     346
     347Grouping the functional dependencies by determinant produces ten relations:
     348
     349| New relation        | Attributes                                                                                                                     | Candidate keys                                          | Source FDs |
     350| ------------------- | ------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------- | ---------- |
     351| `R_USERS`           | `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | `U_ID`, `U_USERNAME`, `U_EMAIL`                         | FD1-FD3    |
     352| `R_CRYPTO`          | `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT`                                                                                         | `C_ID`, `C_SYMBOL`                                      | FD4-FD5    |
     353| `R_MARKETS`         | `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT`                                                               | `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}`               | FD6-FD7    |
     354| `R_HOLDINGS`        | `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT`                       | `H_ID`, `{H_USER_ID, H_CRYPTO_ID}`                      | FD8-FD9    |
     355| `R_ORDERS`          | `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT`                      | `O_ID`                                                  | FD10       |
     356| `R_TRANSACTIONS`    | `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION`                                  | `T_ID`                                                  | FD11       |
     357| `R_MARKET_TRADES`   | `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE`                                               | `MT_ID`                                                 | FD12       |
     358| `R_MARKET_CANDLES`  | `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME`                             | `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` | FD13-FD14  |
     359| `R_WATCHLISTS`      | `W_ID, W_USER_ID, W_NAME, W_CREATED_AT`                                                                                        | `W_ID`                                                  | FD15       |
     360| `R_WATCHLIST_ITEMS` | `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT`                                                                            | `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}`              | FD16-FD17  |
     361
     362Every non-prime attribute now depends on the whole key of its own relation, so
     363the 2NF violations have been removed.
     364
     365=== Dependency preservation
     366
     367FD1-FD17 form the canonical cover of `R_EDUBERZA`.
     368
     369Every dependency is contained completely within one of the ten new relations.
     370Therefore the union of the dependencies preserved by the decomposed relations
     371is again FD1-FD17.
     372
     373No functional dependency from the canonical cover is lost.
     374
     375=== Lossless join
     376
     377The resulting relations are connected through foreign-key/primary-key pairs.
     378
     379The important joins are:
     380
     381* `R_MARKETS.M_CRYPTO_ID -> R_CRYPTO.C_ID`
     382* `R_HOLDINGS.H_USER_ID -> R_USERS.U_ID`
     383* `R_HOLDINGS.H_CRYPTO_ID -> R_CRYPTO.C_ID`
     384* `R_ORDERS.O_USER_ID -> R_USERS.U_ID`
     385* `R_ORDERS.O_MARKET_ID -> R_MARKETS.M_ID`
     386* `R_TRANSACTIONS.T_USER_ID -> R_USERS.U_ID`
     387* `R_TRANSACTIONS.T_RELATED_ORDER -> R_ORDERS.O_ID`
     388* `R_MARKET_TRADES.MT_MARKET_ID -> R_MARKETS.M_ID`
     389* `R_MARKET_CANDLES.MC_MARKET_ID -> R_MARKETS.M_ID`
     390* `R_WATCHLISTS.W_USER_ID -> R_USERS.U_ID`
     391* `R_WATCHLIST_ITEMS.WI_WATCHLIST_ID -> R_WATCHLISTS.W_ID`
     392* `R_WATCHLIST_ITEMS.WI_CRYPTO_ID -> R_CRYPTO.C_ID`
     393
     394For each such join, the shared attribute on the referenced side is a candidate
     395key of that relation.
     396
     397Consequently, joining the relations through these foreign-key/primary-key
     398pairs is lossless: no original tuples are lost and no spurious tuples are
     399introduced.
     400
     401== 3NF decomposition
     402
     403=== Relations analyzed
     404
     405The ten relations produced by the 2NF synthesis are checked individually for
     4063NF.
     407
     408The main question is whether a non-prime attribute is transitively dependent on
     409a candidate key through another non-prime attribute.
     410
     411The foreign-key-carried dependencies from
     412[Dependencies carried by foreign keys](#dependencies-carried-by-foreign-keys)
     413are considered here because they are exactly the dependencies that could create
     414transitive relationships.
     415
     416=== Worked example: `R_MARKETS`
     417
     418`R_MARKETS` contains:
     419
     420`M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT`
     421
     422The relation has:
     423
     424`M_ID -> M_CRYPTO_ID`
     425
     426and, through the foreign-key dependency:
     427
     428`M_CRYPTO_ID -> C_SYMBOL, C_NAME, C_CREATED_AT`
     429
     430Therefore, if the crypto attributes were also stored inside `R_MARKETS`, the
     431following transitive dependency would exist:
     432
     433`M_ID -> M_CRYPTO_ID -> C_SYMBOL`
     434
     435That would violate 3NF.
     436
     437The crypto attributes are not stored there. They are already isolated in
     438`R_CRYPTO`, where:
     439
     440`C_ID -> C_SYMBOL, C_NAME, C_CREATED_AT`
     441
     442Therefore the transitive dependency points to another relation rather than
     443existing between attributes of `R_MARKETS` itself.
     444
     445=== Remaining relations
     446
     447The same reasoning applies to all other foreign keys:
     448
     449* `H_USER_ID` and `H_CRYPTO_ID`
     450* `O_USER_ID` and `O_MARKET_ID`
     451* `T_USER_ID` and `T_RELATED_ORDER`
     452* `MT_MARKET_ID`
     453* `MC_MARKET_ID`
     454* `W_USER_ID`
     455* `WI_WATCHLIST_ID` and `WI_CRYPTO_ID`
     456
     457In every case, the referenced attributes are stored in their own relation.
     458
     459Within each individual relation, there is no non-prime attribute that determines
     460another non-prime attribute.
     461
     462Therefore no further decomposition is required for 3NF.
     463
     464=== Conclusion
     465
     466The ten relations produced by the 2NF synthesis are already in 3NF.
     467
     468No additional decomposition is necessary.
     469
     470== BCNF if possible
     471
     472=== BCNF criterion
     473
     474BCNF is stricter than 3NF.
     475
     476For every non-trivial functional dependency `X -> Y` that holds in a relation,
     477`X` must be a candidate key of that relation.
     478
     479The ten relations are therefore checked against all dependencies that hold within
     480each relation.
     481
     482| Relation            | Functional dependencies | Determinant                                             | Candidate key?  |
     483| ------------------- | ----------------------- | ------------------------------------------------------- | --------------- |
     484| `R_USERS`           | FD1, FD2, FD3           | `U_ID`, `U_USERNAME`, `U_EMAIL`                         | Yes — all three |
     485| `R_CRYPTO`          | FD4, FD5                | `C_ID`, `C_SYMBOL`                                      | Yes — both      |
     486| `R_MARKETS`         | FD6, FD7                | `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}`               | Yes — both      |
     487| `R_HOLDINGS`        | FD8, FD9                | `H_ID`, `{H_USER_ID, H_CRYPTO_ID}`                      | Yes — both      |
     488| `R_ORDERS`          | FD10                    | `O_ID`                                                  | Yes             |
     489| `R_TRANSACTIONS`    | FD11                    | `T_ID`                                                  | Yes             |
     490| `R_MARKET_TRADES`   | FD12                    | `MT_ID`                                                 | Yes             |
     491| `R_MARKET_CANDLES`  | FD13, FD14              | `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` | Yes — both      |
     492| `R_WATCHLISTS`      | FD15                    | `W_ID`                                                  | Yes             |
     493| `R_WATCHLIST_ITEMS` | FD16, FD17              | `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}`              | Yes — both      |
     494
     495Every determinant is therefore a candidate key of its own relation.
     496
     497=== Conclusion
     498
     499All ten relations satisfy BCNF.
     500
     501No further decomposition is necessary.
     502
     503Splitting any of these relations further would not resolve a normal-form
     504violation because every determinant already identifies a complete candidate key.
     505
     506The result is therefore stronger than a 3NF-only decomposition: the final
     507relations are in BCNF.
     508
     509== Final result and discussion
     510
     511=== Normalized relational model
     512
     513The normalized schema is:
     514
     515## [source,text]
     516
     517R_USERS(
     518U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH,
     519U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT
     520)
     521
     522R_CRYPTO(
     523C_ID, C_SYMBOL, C_NAME, C_CREATED_AT
     524)
     525
     526R_MARKETS(
     527M_ID,
     528M_CRYPTO_ID -> R_CRYPTO,
     529M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT
     530)
     531
     532R_HOLDINGS(
     533H_ID,
     534H_USER_ID -> R_USERS,
     535H_CRYPTO_ID -> R_CRYPTO,
     536H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE,
     537H_CREATED_AT, H_UPDATED_AT
     538)
     539
     540R_ORDERS(
     541O_ID,
     542O_USER_ID -> R_USERS,
     543O_MARKET_ID -> R_MARKETS,
     544O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE,
     545O_PLACED_AT, O_EXECUTED_AT
     546)
     547
     548R_TRANSACTIONS(
     549T_ID,
     550T_USER_ID -> R_USERS,
     551T_TYPE, T_AMOUNT, T_CURRENCY,
     552T_RELATED_ORDER -> R_ORDERS,
     553T_CREATED_AT, T_DESCRIPTION
     554)
     555
     556R_MARKET_TRADES(
     557MT_ID,
     558MT_MARKET_ID -> R_MARKETS,
     559MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE
     560)
     561
     562R_MARKET_CANDLES(
     563MC_ID,
     564MC_MARKET_ID -> R_MARKETS,
     565MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW,
     566MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME
     567)
     568
     569R_WATCHLISTS(
     570W_ID,
     571W_USER_ID -> R_USERS,
     572W_NAME, W_CREATED_AT
     573)
     574
     575R_WATCHLIST_ITEMS(
     576WI_ID,
     577WI_WATCHLIST_ID -> R_WATCHLISTS,
     578WI_CRYPTO_ID -> R_CRYPTO,
     579WI_ADDED_AT
     580)
     581-
     582
     583The result contains ten relations connected through eleven foreign keys.
     584Every relation satisfies BCNF.
     585
     586=== Comparison with P2
     587
     588This normalized result is the same relational structure as the P2 design.
     589
     590After removing the `U_`, `C_`, `M_`, `H_`, `O_`, `T_`, `MT_`, `MC_`, `W_` and
     591`WI_` prefixes, the relations correspond directly to:
     592
     593* `users`
     594* `crypto`
     595* `markets`
     596* `holdings`
     597* `orders`
     598* `transactions`
     599* `market_trades`
     600* `market_candles`
     601* `watchlists`
     602* `watchlist_items`
     603
     604The candidate keys also match, including the composite keys:
     605
     606* `{user_id, crypto_id}` on `holdings`
     607* `{crypto_id, quote_currency}` on `markets`
     608* `{market_id, timeframe, candle_time}` on `market_candles`
     609* `{watchlist_id, crypto_id}` on `watchlist_items`
     610
     611The foreign-key structure is also identical.
     612
     613P2 arrived at this structure through the standard ER-to-relational
     614transformation:
     615
     616* each entity becomes a relation keyed by its identifier;
     617* each attributed M:N relationship becomes a relation of its own;
     618* each 1:N relationship is represented by a foreign key on the N side.
     619
     620This normalization phase reaches the same result independently by starting from a
     621single flat relation and applying functional-dependency-based normalization.
     622
     623The agreement between the two methods therefore provides an independent check
     624of the relational design.
     625
     626=== The `holdings.avg_price` exception
     627
     628The one important distinction is `holdings.avg_price`.
     629
     630P2 identifies `avg_price` as a derived attribute: it represents the running
     631weighted-average buy price and can be recomputed from the relevant transaction
     632history. It is nevertheless stored for performance reasons.
     633
     634The functional-dependency analysis performed in this phase does not classify
     635that attribute as a normal-form violation.
     636
     637Within the relation, the dependency
     638
     639`H_ID -> H_AVG_PRICE`
     640
     641is still a valid functional dependency.
     642
     643The fact that `H_AVG_PRICE` can also be recomputed from rows in another relation
     644is a different issue from whether a functional dependency inside one relation
     645violates 2NF, 3NF or BCNF.
     646
     647Therefore normalization does not require `H_AVG_PRICE` to be removed.
     648
     649The stored value remains an intentional denormalisation for performance.
     650
     651=== Design used going forward
     652
     653The P2 design is retained unchanged.
     654
     655The normalization phase confirms that there is no need to restructure the
     656database objects: the independent normalization process produces the same ten
     657relations already implemented by the project.
     658
     659The existing
     660[`server/db/schema_creation.sql`](../../server/db/schema_creation.sql)
     661therefore remains the authoritative physical schema.
     662
     663The `reserved_quantity` attribute added to `holdings` is naturally part of
     664`R_HOLDINGS` because it depends on `H_ID`, just like `H_QUANTITY` and
     665`H_AVG_PRICE`; it does not introduce a separate relation or a new normalization
     666requirement.
     667
     668P4's prototype can therefore continue using the same database structure without
     669schema changes.
     670
     671=== Normalization history
     672
     673* **v01** — First complete normalization analysis. Started from a single
     674  68-attribute relation reconstructed from the P1 model rather than from the P2
     675  tables.
     676* **2NF decomposition** — Applied canonical-cover synthesis to separate the ten
     677  independent entity/relationship clusters.
     678* **3NF verification** — Checked foreign-key-induced transitive dependencies and
     679  confirmed that none remains inside an individual relation.
     680* **BCNF verification** — Checked every determinant and established that each is
     681  a candidate key of its relation.
     682* **Final result** — The independently derived schema matches P2 relation for
     683  relation, including candidate keys and foreign keys. The only documented
     684  denormalisation remains the stored `holdings.avg_price`.
     685
     686The formal normalization analysis therefore validates the existing relational
     687design rather than requiring a new one.