| 1 | | == Normalization |
| 2 | | |
| 3 | | This phase deliberately ignores the design from |
| 4 | | [ERModel](../P1-ConceptualModel/ERModel.md) (P1) and |
| 5 | | [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) (P2) as a |
| 6 | | starting point. |
| 7 | | |
| 8 | | Instead, it starts from a single flat relation containing every attribute of the |
| 9 | | model, derives the functional dependencies that hold on that relation, and |
| 10 | | decomposes it formally using Armstrong's axioms and the standard definitions of |
| 11 | | 1NF, 2NF, 3NF and BCNF. |
| 12 | | |
| 13 | | The [Final result and discussion](#final-result-and-discussion) section then |
| 14 | | compares the result of this independent normalization process with the P2 |
| 15 | | relational design. |
| 16 | | |
| 17 | | == De-normalized database form |
| 18 | | |
| 19 | | === Building one relation from the whole model |
| 20 | | |
| 21 | | The 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 | | |
| 34 | | Eight additional relationships (`QuotedOn`, `PlacedOn`, `Places`, `Records`, |
| 35 | | `Settles`, `Fills`, `Aggregates`, `Owns`) carry no attributes of their own. |
| 36 | | |
| 37 | | In Chen notation these relationships require no attributes because the |
| 38 | | relationship itself represents the connection between entity sets. A single flat |
| 39 | | relation has no such mechanism, so the referenced key must be represented by an |
| 40 | | ordinary attribute. The following foreign-key-style attributes are therefore |
| 41 | | included 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 | | |
| 56 | | These attributes are not copied from the P2 schema as a design decision. They |
| 57 | | are required because a flat relation cannot otherwise preserve the links |
| 58 | | expressed by the original ER relationships. |
| 59 | | |
| 60 | | Because names such as `id`, `created_at`, `quantity`, `type`, `name`, `price` |
| 61 | | and `side` occur in more than one entity or relationship set, every attribute |
| 62 | | is 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 |
| 78 | | relationships with attributes and therefore require their own identifying |
| 79 | | attribute in the relational representation. |
| 80 | | |
| 81 | | The resulting flat relation is: |
| 82 | | |
| 83 | | ## [source,sql] |
| 84 | | |
| 85 | | R_EDUBERZA( |
| 86 | | U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, |
| 87 | | U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT, |
| 88 | | |
| 89 | | C_ID, C_SYMBOL, C_NAME, C_CREATED_AT, |
| 90 | | |
| 91 | | M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT, |
| 92 | | |
| 93 | | H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, |
| 94 | | H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT, |
| 95 | | |
| 96 | | O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, |
| 97 | | O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT, |
| 98 | | |
| 99 | | T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, |
| 100 | | T_CREATED_AT, T_DESCRIPTION, |
| 101 | | |
| 102 | | MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, |
| 103 | | MT_SIDE, MT_SOURCE, |
| 104 | | |
| 105 | | MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, |
| 106 | | MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME, |
| 107 | | |
| 108 | | W_ID, W_USER_ID, W_NAME, W_CREATED_AT, |
| 109 | | |
| 110 | | WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT |
| 111 | | ) |
| 112 | | - |
| 113 | | |
| 114 | | This relation contains 68 attributes. |
| 115 | | |
| 116 | | Every attribute is single-valued and atomic: balances, timestamps, identifiers, |
| 117 | | symbols, amounts and similar values are all scalar values rather than lists or |
| 118 | | nested records. |
| 119 | | |
| 120 | | Therefore `R_EDUBERZA` satisfies 1NF as written. The remaining sections |
| 121 | | determine whether it satisfies 2NF, 3NF or BCNF. |
| 122 | | |
| 123 | | == Functional dependencies |
| 124 | | |
| 125 | | === Canonical cover |
| 126 | | |
| 127 | | The initial functional dependencies are read directly from the identifying |
| 128 | | constraints of the entity and relationship sets. |
| 129 | | |
| 130 | | Each entity's or relationship's key determines the attributes belonging to that |
| 131 | | entity or relationship, while unique candidate keys determine the corresponding |
| 132 | | primary 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 | | |
| 154 | | No dependency above contains an extraneous attribute on its determinant, and the |
| 155 | | dependent attributes are assigned to the entity or relationship that actually |
| 156 | | owns them. |
| 157 | | |
| 158 | | === Minimality of the composite dependencies |
| 159 | | |
| 160 | | The composite determinants in FD7, FD9, FD14 and FD17 are minimal. |
| 161 | | |
| 162 | | For 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 |
| 167 | | traded in several quote currencies. `M_QUOTE_CURRENCY` alone also does not |
| 168 | | determine `M_ID`, because many different crypto assets may use the same quote |
| 169 | | currency. |
| 170 | | |
| 171 | | Therefore neither attribute can be removed from the determinant. |
| 172 | | |
| 173 | | The 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 | | |
| 179 | | Their left-hand sides are exactly the uniqueness constraints of the corresponding |
| 180 | | relations. |
| 181 | | |
| 182 | | === Dependencies carried by foreign keys |
| 183 | | |
| 184 | | The flat relation also carries dependencies induced by foreign keys. |
| 185 | | |
| 186 | | Because a referencing attribute takes its values from the candidate-key domain of |
| 187 | | another entity, the attributes determined by that referenced key are also fixed |
| 188 | | for 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 | | |
| 205 | | These dependencies are not added as separate entries to the canonical cover. |
| 206 | | They are derivable from FD1-FD17 together with the foreign-key constraints and |
| 207 | | transitivity. |
| 208 | | |
| 209 | | They are nevertheless important during the 3NF analysis because they are the |
| 210 | | source of the transitive dependencies that would exist if the referenced |
| 211 | | attributes were copied into the referencing relation. |
| 212 | | |
| 213 | | == Candidate keys and primary key |
| 214 | | |
| 215 | | === Candidate-key structure of `R_EDUBERZA` |
| 216 | | |
| 217 | | The entity and relationship clusters in `R_EDUBERZA` are structurally |
| 218 | | independent. |
| 219 | | |
| 220 | | For example, an order identifier tells us nothing about which unrelated |
| 221 | | transaction, watchlist item, holding or market-candle tuple happens to coexist |
| 222 | | in the same flat row. A user may have zero orders, zero holdings, zero |
| 223 | | transactions, and zero watchlists, so none of those identifiers can be inferred |
| 224 | | from another cluster. |
| 225 | | |
| 226 | | The nullable `T_RELATED_ORDER` attribute also cannot serve as a universal bridge |
| 227 | | between transactions and orders. |
| 228 | | |
| 229 | | Therefore a key for `R_EDUBERZA` must contain one determinant from each |
| 230 | | independent cluster. |
| 231 | | |
| 232 | | === Chosen primary key |
| 233 | | |
| 234 | | The 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 | | |
| 242 | | Applying 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 | | |
| 258 | | The closure contains all 68 attributes, so the set is a superkey. |
| 259 | | |
| 260 | | Removing any one of the ten identifier attributes leaves an entire independent |
| 261 | | cluster unreachable. Therefore the key is minimal and is a candidate key. |
| 262 | | |
| 263 | | === Other candidate keys |
| 264 | | |
| 265 | | The chosen primary key is not the only candidate key. |
| 266 | | |
| 267 | | Any determinant that uniquely identifies one cluster can replace that cluster's |
| 268 | | surrogate 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 | | |
| 277 | | This produces: |
| 278 | | |
| 279 | | `3 x 2 x 2 x 2 x 1 x 1 x 1 x 2 x 1 x 2 = 96` |
| 280 | | |
| 281 | | candidate keys. |
| 282 | | |
| 283 | | The all-surrogate-ID combination is chosen as the primary key because the |
| 284 | | surrogate identifiers are opaque, stable and independent of user-facing values |
| 285 | | such as usernames, e-mail addresses and symbols. |
| 286 | | |
| 287 | | === Normal form before decomposition |
| 288 | | |
| 289 | | `R_EDUBERZA` is in 1NF only. |
| 290 | | |
| 291 | | It is not in 2NF because non-prime attributes depend on proper subsets of its |
| 292 | | ten-attribute candidate key. Since 2NF is a precondition for both 3NF and BCNF, |
| 293 | | the relation is also not in 3NF or BCNF. |
| 294 | | |
| 295 | | == 1NF decomposition |
| 296 | | |
| 297 | | No decomposition is necessary for 1NF. |
| 298 | | |
| 299 | | `R_EDUBERZA` already contains only atomic, single-valued attributes and has no |
| 300 | | repeating groups. |
| 301 | | |
| 302 | | The 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 | | |
| 312 | | FD1-FD17 are in force. |
| 313 | | |
| 314 | | === 2NF violations |
| 315 | | |
| 316 | | 2NF forbids a non-prime attribute from depending on a proper subset of a |
| 317 | | candidate key. |
| 318 | | |
| 319 | | Every dependency in the canonical cover has a determinant smaller than the |
| 320 | | ten-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 | | |
| 334 | | Therefore every non-prime attribute in `R_EDUBERZA` is involved in a partial |
| 335 | | dependency. |
| 336 | | |
| 337 | | This is not a small collection of isolated violations. The entire flat relation |
| 338 | | violates 2NF because ten independent record types were artificially combined |
| 339 | | under one composite key. |
| 340 | | |
| 341 | | === Decomposition by 3NF/BCNF synthesis |
| 342 | | |
| 343 | | Since a canonical cover is already available, the decomposition uses the |
| 344 | | standard synthesis approach rather than repeatedly applying binary |
| 345 | | decomposition. |
| 346 | | |
| 347 | | Grouping 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 | | |
| 362 | | Every non-prime attribute now depends on the whole key of its own relation, so |
| 363 | | the 2NF violations have been removed. |
| 364 | | |
| 365 | | === Dependency preservation |
| 366 | | |
| 367 | | FD1-FD17 form the canonical cover of `R_EDUBERZA`. |
| 368 | | |
| 369 | | Every dependency is contained completely within one of the ten new relations. |
| 370 | | Therefore the union of the dependencies preserved by the decomposed relations |
| 371 | | is again FD1-FD17. |
| 372 | | |
| 373 | | No functional dependency from the canonical cover is lost. |
| 374 | | |
| 375 | | === Lossless join |
| 376 | | |
| 377 | | The resulting relations are connected through foreign-key/primary-key pairs. |
| 378 | | |
| 379 | | The 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 | | |
| 394 | | For each such join, the shared attribute on the referenced side is a candidate |
| 395 | | key of that relation. |
| 396 | | |
| 397 | | Consequently, joining the relations through these foreign-key/primary-key |
| 398 | | pairs is lossless: no original tuples are lost and no spurious tuples are |
| 399 | | introduced. |
| 400 | | |
| 401 | | == 3NF decomposition |
| 402 | | |
| 403 | | === Relations analyzed |
| 404 | | |
| 405 | | The ten relations produced by the 2NF synthesis are checked individually for |
| 406 | | 3NF. |
| 407 | | |
| 408 | | The main question is whether a non-prime attribute is transitively dependent on |
| 409 | | a candidate key through another non-prime attribute. |
| 410 | | |
| 411 | | The foreign-key-carried dependencies from |
| 412 | | [Dependencies carried by foreign keys](#dependencies-carried-by-foreign-keys) |
| 413 | | are considered here because they are exactly the dependencies that could create |
| 414 | | transitive 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 | | |
| 422 | | The relation has: |
| 423 | | |
| 424 | | `M_ID -> M_CRYPTO_ID` |
| 425 | | |
| 426 | | and, through the foreign-key dependency: |
| 427 | | |
| 428 | | `M_CRYPTO_ID -> C_SYMBOL, C_NAME, C_CREATED_AT` |
| 429 | | |
| 430 | | Therefore, if the crypto attributes were also stored inside `R_MARKETS`, the |
| 431 | | following transitive dependency would exist: |
| 432 | | |
| 433 | | `M_ID -> M_CRYPTO_ID -> C_SYMBOL` |
| 434 | | |
| 435 | | That would violate 3NF. |
| 436 | | |
| 437 | | The 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 | | |
| 442 | | Therefore the transitive dependency points to another relation rather than |
| 443 | | existing between attributes of `R_MARKETS` itself. |
| 444 | | |
| 445 | | === Remaining relations |
| 446 | | |
| 447 | | The 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 | | |
| 457 | | In every case, the referenced attributes are stored in their own relation. |
| 458 | | |
| 459 | | Within each individual relation, there is no non-prime attribute that determines |
| 460 | | another non-prime attribute. |
| 461 | | |
| 462 | | Therefore no further decomposition is required for 3NF. |
| 463 | | |
| 464 | | === Conclusion |
| 465 | | |
| 466 | | The ten relations produced by the 2NF synthesis are already in 3NF. |
| 467 | | |
| 468 | | No additional decomposition is necessary. |
| 469 | | |
| 470 | | == BCNF if possible |
| 471 | | |
| 472 | | === BCNF criterion |
| 473 | | |
| 474 | | BCNF is stricter than 3NF. |
| 475 | | |
| 476 | | For every non-trivial functional dependency `X -> Y` that holds in a relation, |
| 477 | | `X` must be a candidate key of that relation. |
| 478 | | |
| 479 | | The ten relations are therefore checked against all dependencies that hold within |
| 480 | | each 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 | | |
| 495 | | Every determinant is therefore a candidate key of its own relation. |
| 496 | | |
| 497 | | === Conclusion |
| 498 | | |
| 499 | | All ten relations satisfy BCNF. |
| 500 | | |
| 501 | | No further decomposition is necessary. |
| 502 | | |
| 503 | | Splitting any of these relations further would not resolve a normal-form |
| 504 | | violation because every determinant already identifies a complete candidate key. |
| 505 | | |
| 506 | | The result is therefore stronger than a 3NF-only decomposition: the final |
| 507 | | relations are in BCNF. |
| 508 | | |
| 509 | | == Final result and discussion |
| 510 | | |
| 511 | | === Normalized relational model |
| 512 | | |
| 513 | | The normalized schema is: |
| 514 | | |
| 515 | | ## [source,text] |
| 516 | | |
| 517 | | R_USERS( |
| 518 | | U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, |
| 519 | | U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT |
| 520 | | ) |
| 521 | | |
| 522 | | R_CRYPTO( |
| 523 | | C_ID, C_SYMBOL, C_NAME, C_CREATED_AT |
| 524 | | ) |
| 525 | | |
| 526 | | R_MARKETS( |
| 527 | | M_ID, |
| 528 | | M_CRYPTO_ID -> R_CRYPTO, |
| 529 | | M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT |
| 530 | | ) |
| 531 | | |
| 532 | | R_HOLDINGS( |
| 533 | | H_ID, |
| 534 | | H_USER_ID -> R_USERS, |
| 535 | | H_CRYPTO_ID -> R_CRYPTO, |
| 536 | | H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, |
| 537 | | H_CREATED_AT, H_UPDATED_AT |
| 538 | | ) |
| 539 | | |
| 540 | | R_ORDERS( |
| 541 | | O_ID, |
| 542 | | O_USER_ID -> R_USERS, |
| 543 | | O_MARKET_ID -> R_MARKETS, |
| 544 | | O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, |
| 545 | | O_PLACED_AT, O_EXECUTED_AT |
| 546 | | ) |
| 547 | | |
| 548 | | R_TRANSACTIONS( |
| 549 | | T_ID, |
| 550 | | T_USER_ID -> R_USERS, |
| 551 | | T_TYPE, T_AMOUNT, T_CURRENCY, |
| 552 | | T_RELATED_ORDER -> R_ORDERS, |
| 553 | | T_CREATED_AT, T_DESCRIPTION |
| 554 | | ) |
| 555 | | |
| 556 | | R_MARKET_TRADES( |
| 557 | | MT_ID, |
| 558 | | MT_MARKET_ID -> R_MARKETS, |
| 559 | | MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE |
| 560 | | ) |
| 561 | | |
| 562 | | R_MARKET_CANDLES( |
| 563 | | MC_ID, |
| 564 | | MC_MARKET_ID -> R_MARKETS, |
| 565 | | MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, |
| 566 | | MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME |
| 567 | | ) |
| 568 | | |
| 569 | | R_WATCHLISTS( |
| 570 | | W_ID, |
| 571 | | W_USER_ID -> R_USERS, |
| 572 | | W_NAME, W_CREATED_AT |
| 573 | | ) |
| 574 | | |
| 575 | | R_WATCHLIST_ITEMS( |
| 576 | | WI_ID, |
| 577 | | WI_WATCHLIST_ID -> R_WATCHLISTS, |
| 578 | | WI_CRYPTO_ID -> R_CRYPTO, |
| 579 | | WI_ADDED_AT |
| 580 | | ) |
| 581 | | - |
| 582 | | |
| 583 | | The result contains ten relations connected through eleven foreign keys. |
| 584 | | Every relation satisfies BCNF. |
| 585 | | |
| 586 | | === Comparison with P2 |
| 587 | | |
| 588 | | This normalized result is the same relational structure as the P2 design. |
| 589 | | |
| 590 | | After 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 | | |
| 604 | | The 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 | | |
| 611 | | The foreign-key structure is also identical. |
| 612 | | |
| 613 | | P2 arrived at this structure through the standard ER-to-relational |
| 614 | | transformation: |
| 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 | | |
| 620 | | This normalization phase reaches the same result independently by starting from a |
| 621 | | single flat relation and applying functional-dependency-based normalization. |
| 622 | | |
| 623 | | The agreement between the two methods therefore provides an independent check |
| 624 | | of the relational design. |
| 625 | | |
| 626 | | === The `holdings.avg_price` exception |
| 627 | | |
| 628 | | The one important distinction is `holdings.avg_price`. |
| 629 | | |
| 630 | | P2 identifies `avg_price` as a derived attribute: it represents the running |
| 631 | | weighted-average buy price and can be recomputed from the relevant transaction |
| 632 | | history. It is nevertheless stored for performance reasons. |
| 633 | | |
| 634 | | The functional-dependency analysis performed in this phase does not classify |
| 635 | | that attribute as a normal-form violation. |
| 636 | | |
| 637 | | Within the relation, the dependency |
| 638 | | |
| 639 | | `H_ID -> H_AVG_PRICE` |
| 640 | | |
| 641 | | is still a valid functional dependency. |
| 642 | | |
| 643 | | The fact that `H_AVG_PRICE` can also be recomputed from rows in another relation |
| 644 | | is a different issue from whether a functional dependency inside one relation |
| 645 | | violates 2NF, 3NF or BCNF. |
| 646 | | |
| 647 | | Therefore normalization does not require `H_AVG_PRICE` to be removed. |
| 648 | | |
| 649 | | The stored value remains an intentional denormalisation for performance. |
| 650 | | |
| 651 | | === Design used going forward |
| 652 | | |
| 653 | | The P2 design is retained unchanged. |
| 654 | | |
| 655 | | The normalization phase confirms that there is no need to restructure the |
| 656 | | database objects: the independent normalization process produces the same ten |
| 657 | | relations already implemented by the project. |
| 658 | | |
| 659 | | The existing |
| 660 | | [`server/db/schema_creation.sql`](../../server/db/schema_creation.sql) |
| 661 | | therefore remains the authoritative physical schema. |
| 662 | | |
| 663 | | The `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 |
| 666 | | requirement. |
| 667 | | |
| 668 | | P4's prototype can therefore continue using the same database structure without |
| 669 | | schema changes. |
| 670 | | |
| | 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. |
