Changeset 1549dae for docs/P1-ConceptualModel/ERModel.md
- Timestamp:
- 09/29/26 20:55:13 (10 hours ago)
- Branches:
- main
- Parents:
- 0cee8ec
- File:
-
- 1 edited
-
docs/P1-ConceptualModel/ERModel.md (modified) (14 diffs)
Legend:
- Unmodified
- Added
- Removed
-
docs/P1-ConceptualModel/ERModel.md
r0cee8ec r1549dae 1 # Entity-Relationship Model v.0 41 # Entity-Relationship Model v.05 2 2 3 3 ## Diagram 4 4 5 5  6 6 7 7 Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses … … 11 11 single line marks partial participation. 12 12 13 T wodeliberate modeling decisions worth stating up front:13 Three deliberate modeling decisions worth stating up front: 14 14 15 15 - **No foreign keys appear in the diagram.** Connections between entity sets are 16 16 expressed as relationships, per the notation. Foreign-key columns appear only 17 17 in the relational model in [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md). 18 - **`Holds` and `Contains` are relationships, not entity sets.** Both are M:N and 19 both carry their own attributes, which is exactly what a Chen relationship is 20 for. They become tables (`holdings`, `watchlist_items`) only in P2. 18 - **A position and a watchlist entry are entity sets, not M:N relationships.** 19 `Holdings` (a user's position in an asset) and `WatchlistItems` (an asset on a 20 watchlist) each have their own identifier `id`, and each is connected by two 21 1:N relationships: `Holds` and `PositionIn` for a holding, `Contains` and 22 `Lists` for a watchlist item. Until v04 they were drawn as the M:N 23 relationships `Holds` and `Contains`, but the database has always given 24 `holdings` and `watchlist_items` their own `id` primary key. That is how an 25 entity set is implemented, not an M:N relationship, whose key would be the 26 pair of participating keys. v05 corrects the model to match; see 27 [history](#entity-relationship-model-history). 28 - **Key and uniqueness rules are stated with entity and relationship names, 29 never with foreign-key columns.** For example: "a crypto is quoted at most 30 once per currency", not "`{crypto_id, quote_currency}` is unique". 21 31 22 32 ## Data requirements … … 46 56 | `id` | UUID | PK, required | 47 57 | `username` | text(50) | required, unique | 48 | `email` | text(255) | required, unique, contains `@` |58 | `email` | text(255) | required, unique, contains `@` (checked by the application at registration, not by a database constraint) | 49 59 | `full_name` | text(200) | optional | 50 60 | `password_hash` | text(255) | required — never the password itself; the prototype stores a SHA-256 hex digest | … … 79 89 trades, candles and orders all reference the pair, not the asset. 80 90 81 **Keys:** candidates `{id}`, `{crypto_id, quote_currency}` — that pair is 82 unique by definition, since a given asset can only be quoted once per 83 currency; primary key **`id`**, so that the many entity sets referencing a 84 market carry one narrow column instead of a composite key. 91 **Keys:** candidate `{id}`; primary key **`id`**, so that the many entity sets 92 related to a market need one narrow identifier instead of a composite one. 93 **Uniqueness rule:** a crypto is quoted at most once per currency, so the crypto 94 a market is `QuotedOn` together with its `quote_currency` identifies the market 95 as well. Chen notation cannot draw this, because half of it comes through a 96 relationship. P2 enforces it as `UNIQUE(crypto_id, quote_currency)`. 85 97 86 98 | Attribute | Type | Constraints | … … 98 110 99 111 Placing an order is what triggers a **reservation** of whatever it commits: 100 the crypto being sold (`Hold s.reserved_quantity`, below) on a sell, and the112 the crypto being sold (`Holdings.reserved_quantity`, below) on a sell, and the 101 113 cash (`Users.reserved_balance`) on a buy. Since v04 (after P7) an order can 102 114 wait in the order book and be filled in parts, so `status` is a real … … 121 133 | `quantity` | numeric(20,4) | required, > 0 | 122 134 | `filled_quantity` | numeric(20,4) | required, default 0, between 0 and `quantity` — how much has been traded; remaining = `quantity − filled_quantity` (added in v04, after P7) | 123 | `price` | numeric(18,6) | the limit price; for a market order, the market price when it was placed |135 | `price` | numeric(18,6) | optional — the limit price; for a market order, the market price when it was placed | 124 136 | `placed_at` | timestamptz | required, defaults to now | 125 137 | `executed_at` | timestamptz | optional, set when the order settles | … … 148 160 writes directly. 149 161 150 **Keys:** candidate `{id}` — `{market_id, executed_at}`looks unique in151 principle, but two trades can share a timestamp, so it is not a safe key;162 **Keys:** candidate `{id}` — "market plus `executed_at`" looks unique in 163 principle, but two trades on a market can share a timestamp, so it is not a safe key; 152 164 primary key **`id`** (a plain auto-incrementing integer here rather than a 153 165 UUID, because this is the highest-volume entity set and it is only ever read … … 156 168 | Attribute | Type | Constraints | 157 169 |---|---|---| 158 | `id` | integer | PK, required, auto-generated|170 | `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | 159 171 | `executed_at` | timestamptz | required | 160 172 | `price` | numeric(18,6) | required, > 0 | … … 177 189 | Attribute | Type | Constraints | 178 190 |---|---|---| 179 | `id` | integer | PK, required, auto-generated|191 | `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | 180 192 | `event_type` | text | required, `placed`, `partially_filled`, `filled` or `cancelled` | 181 193 | `quantity` | numeric(20,4) | required — the ordered quantity for `placed`, the filled amount for a fill, the unfilled rest for `cancelled` | 182 194 | `price` | numeric(18,6) | optional — the order price, or the trade price for a fill | 183 195 | `status_after` | text | required, the order's status after the event | 184 | `created_at` | timestamptz | required, defaults to now|196 | `created_at` | timestamptz | required, set automatically when the event is recorded (`clock_timestamp()`, so events inside one transaction keep their real order) | 185 197 186 198 #### MarketCandles … … 190 202 screen refresh does not scale. 191 203 192 **Keys:** candidates `{id}`, `{market_id, timeframe, candle_time}` — a market 193 has exactly one candle per timeframe per time bucket; primary key **`id`**, the 194 composite is enforced as a uniqueness rule because it is the real-world 195 constraint and it is what prevents duplicate candles. 196 197 | Attribute | Type | Constraints | 198 |---|---|---| 199 | `id` | integer | PK, required, auto-generated | 204 **Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** a market 205 has exactly one candle per timeframe per time bucket, so the market a candle 206 `Aggregates` together with `timeframe` and `candle_time` also identifies it. 207 This is the real-world constraint that prevents duplicate candles. P2 enforces 208 it as `UNIQUE(market_id, timeframe, candle_time)`. 209 210 | Attribute | Type | Constraints | 211 |---|---|---| 212 | `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | 200 213 | `timeframe` | text | required, `1m`, `5m`, `1h` or `1d` | 201 214 | `open`, `high`, `low`, `close` | numeric(18,6) | all required | … … 208 221 several lists ("long term", "watching today") and each needs its own name. 209 222 210 **Keys:** candidate `{id}` — `{user_id, name}` would also work if list names211 were required to be unique per user, which the model does not impose, so it is212 not listed as a candidate key; primary key **`id`**.223 **Keys:** candidate `{id}`; primary key **`id`**. "Owner plus `name`" would 224 also identify a list if names had to be unique per user, but the model does 225 not require that, so there is no uniqueness rule here. 213 226 214 227 | Attribute | Type | Constraints | … … 218 231 | `created_at` | timestamptz | required, defaults to now | 219 232 220 ### Relationships 221 222 #### QuotedOn — Cryptos (1) : Markets (N), total on Markets 223 Ties a market to the asset it trades. One asset can be quoted in many markets; 224 every market must have exactly one asset, hence total participation on the 225 `Markets` side. No attributes. 226 227 #### PlacedOn — Markets (1) : Orders (N), total on Orders 228 Records which market an order was placed on. Every order must name a market; 229 a market may have no orders yet. No attributes. 230 231 #### Places — Users (1) : Orders (N), total on Orders 232 Records who placed an order. Every order belongs to exactly one user; a new 233 user has no orders. No attributes. 234 235 #### Records — Users (1) : Transactions (N), total on Transactions 236 Attributes each ledger entry to a user. Every entry belongs to exactly one 237 user. No attributes. 238 239 #### Settles — Orders (1) : Transactions (N), partial on both sides 240 Links a ledger entry to the order that caused it. Partial on the 241 `Transactions` side because deposits have no originating order, and partial on 242 the `Orders` side because an order that never executes never produces a 243 ledger entry — which is why the corresponding column is nullable in P2. No 244 attributes. 245 246 #### Fills — Markets (1) : MarketTrades (N), total on MarketTrades 247 Every executed trade happened on exactly one market. No attributes. 248 249 #### FillsBuy — Orders (1) : MarketTrades (N), partial on both sides 250 *Added in v04, after P7.* The buy order a trade filled. An order can be 251 filled by many trades (partial fills); a trade fills at most one buy order, 252 and none when the simulated market was the buyer. No attributes. 253 254 #### FillsSell — Orders (1) : MarketTrades (N), partial on both sides 255 *Added in v04, after P7.* The sell order a trade filled, symmetric to 256 `FillsBuy`. A trade between two users' orders participates in both. No 257 attributes. 258 259 #### Logs — Orders (1) : OrderEvents (N), total on OrderEvents 260 *Added in v04, after P7.* Every event belongs to exactly one order. No 261 attributes. 262 263 #### Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles 264 Every candle summarises trades of exactly one market. No attributes. 265 266 #### Owns — Users (1) : Watchlists (N), total on Watchlists 267 Every watchlist belongs to exactly one user. No attributes. 268 269 #### Holds — Users (M) : Cryptos (N), partial on both sides, **with attributes** 270 A user's position in an asset. M:N because one user holds many assets and one 271 asset is held by many users, and partial on both sides because a user may hold 272 nothing and an asset may be held by nobody. Modeled as a relationship rather 273 than an entity set because a position has no identity of its own — it is 274 entirely described by *which user*, *which asset*, and how much. 233 #### Holdings 234 A user's position in one crypto asset: how much of it the user owns, how much 235 of that is already promised to open sell orders, and at what average price it 236 was accumulated. *An entity set since v05* (until v04 it was the M:N 237 relationship `Holds`). A holding has its own identifier and its own 238 lifecycle: it is created on the first buy, updated on every later fill, and 239 the prototype reads and locks it as a unit (`SELECT … FOR UPDATE` on the sell 240 path). It is linked to its owner through `Holds` and to its asset through 241 `PositionIn`. 242 243 **Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** a user 244 has at most one holding per crypto, so the user who `Holds` it together with 245 the crypto it is a `PositionIn` also identifies a holding. P2 enforces this as 246 `UNIQUE(user_id, crypto_id)`. 275 247 276 248 `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: … … 284 256 | Attribute | Type | Constraints | 285 257 |---|---|---| 258 | `id` | UUID | PK, required | 286 259 | `quantity` | numeric(20,4) | required, ≥ 0 — total amount owned | 287 260 | `reserved_quantity` | numeric(20,4) | required, default 0, `0 ≤ reserved_quantity ≤ quantity` — committed to the user's own open sell orders, not yet removed from the position | 288 | `avg_price` | numeric(18,6) | required, ≥ 0, **derived** (dashed ellipse) — the weighted average of the prices at which the position was accumulated; derivable from the buy history, stored anyway so unrealised P/L can be shown without replaying the whole ledger |261 | `avg_price` | numeric(18,6) | required, default 0, ≥ 0, **derived** (dashed ellipse) — the weighted average of the prices at which the position was accumulated; derivable from the buy history, stored anyway so unrealised P/L can be shown without replaying the whole ledger | 289 262 | `created_at` | timestamptz | required, defaults to now | 290 263 | `updated_at` | timestamptz | optional | 291 264 292 #### Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute** 293 Which assets are on which watchlist. M:N: a list holds many assets, an asset 294 appears on many lists. Partial on both sides — an empty list is valid and an 295 asset need not be on any list. 296 297 | Attribute | Type | Constraints | 298 |---|---|---| 265 #### WatchlistItems 266 One asset placed on one watchlist. *An entity set since v05* (until v04 it was 267 the M:N relationship `Contains`). It has its own identifier, and it is linked 268 to its list through `Contains` and to its asset through `Lists`. 269 270 **Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** an asset 271 appears at most once on a given list, so the watchlist that `Contains` an item 272 together with the crypto it `Lists` also identifies the item. P2 enforces this 273 as `UNIQUE(watchlist_id, crypto_id)`. 274 275 | Attribute | Type | Constraints | 276 |---|---|---| 277 | `id` | UUID | PK, required | 299 278 | `added_at` | timestamptz | required, defaults to now — recorded so a list can be shown in the order the user built it | 279 280 ### Relationships 281 282 #### QuotedOn — Cryptos (1) : Markets (N), total on Markets 283 Ties a market to the asset it trades. One asset can be quoted in many markets; 284 every market must have exactly one asset, hence total participation on the 285 `Markets` side. No attributes. 286 287 #### PlacedOn — Markets (1) : Orders (N), total on Orders 288 Records which market an order was placed on. Every order must name a market; 289 a market may have no orders yet. No attributes. 290 291 #### Places — Users (1) : Orders (N), total on Orders 292 Records who placed an order. Every order belongs to exactly one user; a new 293 user has no orders. No attributes. 294 295 #### Records — Users (1) : Transactions (N), total on Transactions 296 Attributes each ledger entry to a user. Every entry belongs to exactly one 297 user. No attributes. 298 299 #### Settles — Orders (1) : Transactions (N), partial on both sides 300 Links a ledger entry to the order that caused it. Partial on the 301 `Transactions` side because deposits have no originating order, and partial on 302 the `Orders` side because an order that never executes never produces a 303 ledger entry — which is why the corresponding column is nullable in P2. No 304 attributes. 305 306 #### Fills — Markets (1) : MarketTrades (N), total on MarketTrades 307 Every executed trade happened on exactly one market. No attributes. 308 309 #### FillsBuy — Orders (1) : MarketTrades (N), partial on both sides 310 *Added in v04, after P7.* The buy order a trade filled. An order can be 311 filled by many trades (partial fills); a trade fills at most one buy order, 312 and none when the simulated market was the buyer. The role of `Orders` in this 313 relationship is *the buy order* of the trade. No attributes. 314 315 #### FillsSell — Orders (1) : MarketTrades (N), partial on both sides 316 *Added in v04, after P7.* The sell order a trade filled, symmetric to 317 `FillsBuy`; the role of `Orders` here is *the sell order* of the trade. A 318 trade between two users' orders participates in both. No attributes. 319 320 #### Logs — Orders (1) : OrderEvents (N), total on OrderEvents 321 *Added in v04, after P7.* Every event belongs to exactly one order. No 322 attributes. 323 324 #### Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles 325 Every candle summarises trades of exactly one market. No attributes. 326 327 #### Owns — Users (1) : Watchlists (N), total on Watchlists 328 Every watchlist belongs to exactly one user. No attributes. 329 330 #### Holds — Users (1) : Holdings (N), total on Holdings 331 *1:N since v05.* Every holding belongs to exactly one user. A user may hold 332 nothing yet, so participation is partial on the `Users` side. No attributes. 333 334 #### PositionIn — Cryptos (1) : Holdings (N), total on Holdings 335 *Added in v05.* Every holding is a position in exactly one crypto asset. An 336 asset may be held by nobody. No attributes. 337 338 Together, `Holds` and `PositionIn` still say what the old M:N `Holds` said: 339 a user can hold many assets and an asset can be held by many users. The 340 difference is that the position is now a thing with its own identity, not 341 just a pair. The rule "at most one holding per user and crypto" is stated 342 under [Holdings](#holdings). 343 344 #### Contains — Watchlists (1) : WatchlistItems (N), total on WatchlistItems 345 *1:N since v05.* Every watchlist item is on exactly one list. An empty list is 346 valid, so participation is partial on the `Watchlists` side. No attributes. 347 348 #### Lists — Cryptos (1) : WatchlistItems (N), total on WatchlistItems 349 *Added in v05.* Every watchlist item names exactly one crypto asset. An asset 350 need not be on any list. No attributes. 300 351 301 352 ## Entity-Relationship Model History … … 341 392 Nothing existing was removed or changed. See 342 393 [AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md). 343 The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`; earlier versions 394 The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`. 395 - **v05 — correction after review.** The review of P2 found that two parts of 396 the model were implemented differently in the database: 397 - `Contains` was an M:N relationship in the model, but `watchlist_items` 398 has its own `id` primary key; 399 - `Holds` was an M:N relationship in the model, but `holdings` has its own 400 `id` primary key. 401 402 An M:N relationship has no identifier of its own; its table's key is the pair 403 of participating keys. A table with its own `id` is the implementation of an 404 entity set. Every phase after P2 (the prototype, the reports and the P7 405 logic) already uses the database as it is. So the **model** was corrected to 406 match P2, not the other way round: 407 - `Holds` (M:N, with attributes) became the entity set `Holdings` (its former 408 attributes plus `id`) with two 1:N relationships, `Holds` (Users → Holdings) 409 and `PositionIn` (Cryptos → Holdings), both total on the `Holdings` side; 410 - `Contains` (M:N, with `added_at`) became the entity set `WatchlistItems` 411 (`id`, `added_at`) with `Contains` (Watchlists → WatchlistItems) and 412 `Lists` (Cryptos → WatchlistItems), both total on the `WatchlistItems` 413 side; 414 - the former keys of the two relationships are kept as uniqueness rules 415 ("one holding per user and crypto", "an asset at most once per list"); 416 - the key descriptions of `Markets`, `MarketTrades`, `MarketCandles` and 417 `Watchlists` no longer name foreign-key columns (`crypto_id`, 418 `market_id`, `user_id`), which do not exist in an ER model; 419 - the diagram was redrawn on a grid with no overlapping attributes. In v04, 420 `Watchlists.id` was hidden behind `added_at`, and several attributes of 421 `Orders`, `Transactions`, `MarketTrades` and `MarketCandles` overlapped. 422 The grid also makes it easier to compare the diagram with the P2 423 relational diagram. 424 425 The diagram files are `ERModel_v05.xml` / `ERModel_v05.png`; earlier versions 344 426 are kept. 345 427
Note:
See TracChangeset
for help on using the changeset viewer.
