Changeset 1549dae for docs/P1-ConceptualModel/wiki/ERModel.md
- Timestamp:
- 09/29/26 20:55:13 (6 hours ago)
- Branches:
- main
- Parents:
- 0cee8ec
- File:
-
- 1 edited
-
docs/P1-ConceptualModel/wiki/ERModel.md (modified) (14 diffs)
Legend:
- Unmodified
- Added
- Removed
-
docs/P1-ConceptualModel/wiki/ERModel.md
r0cee8ec r1549dae 1 = Entity-Relationship Model v.0 4=1 = Entity-Relationship Model v.05 = 2 2 3 3 == Diagram == 4 4 5 [[Image(ERModel_v0 4.png, 800px)]]5 [[Image(ERModel_v05.png, 800px)]] 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 expressed as relationships, per the notation. Foreign-key columns appear only in the relational model in [wiki:RelationalDesign]. 16 * '''`Holds` and `Contains` are relationships, not entity sets.''' Both are M:N and both carry their own attributes, which is exactly what a Chen relationship is for. They become tables (`holdings`, `watchlist_items`) only in P2. 16 * '''A position and a watchlist entry are entity sets, not M:N relationships.''' `Holdings` (a user's position in an asset) and `WatchlistItems` (an asset on a watchlist) each have their own identifier `id`, and each is connected by two 1:N relationships: `Holds` and `PositionIn` for a holding, `Contains` and `Lists` for a watchlist item. Until v04 they were drawn as the M:N relationships `Holds` and `Contains`, but the database has always given `holdings` and `watchlist_items` their own `id` primary key. That is how an entity set is implemented, not an M:N relationship, whose key would be the pair of participating keys. v05 corrects the model to match; see history. 17 * '''Key and uniqueness rules are stated with entity and relationship names, never with foreign-key columns.''' For example: "a crypto is quoted at most once per currency", not "`{crypto_id, quote_currency}` is unique". 17 18 18 19 == Data requirements == … … 41 42 || `id` || UUID || PK, required || 42 43 || `username` || text(50) || required, unique || 43 || `email` || text(255) || required, unique, contains `@` ||44 || `email` || text(255) || required, unique, contains `@` (checked by the application at registration, not by a database constraint) || 44 45 || `full_name` || text(200) || optional || 45 46 || `password_hash` || text(255) || required — never the password itself; the prototype stores a SHA-256 hex digest || … … 73 74 trades, candles and orders all reference the pair, not the asset. 74 75 75 '''Keys:''' candidates `{id}`, `{crypto_id, quote_currency}` — that pair is 76 unique by definition, since a given asset can only be quoted once per 77 currency; primary key '''`id`''', so that the many entity sets referencing a 78 market carry one narrow column instead of a composite key. 76 '''Keys:''' candidate `{id}`; primary key '''`id`''', so that the many entity sets 77 related to a market need one narrow identifier instead of a composite one. 78 '''Uniqueness rule:''' a crypto is quoted at most once per currency, so the crypto 79 a market is `QuotedOn` together with its `quote_currency` identifies the market 80 as well. Chen notation cannot draw this, because half of it comes through a 81 relationship. P2 enforces it as `UNIQUE(crypto_id, quote_currency)`. 79 82 80 83 ||= Attribute =||= Type =||= Constraints =|| … … 91 94 92 95 Placing an order is what triggers a '''reservation''' of whatever it commits: 93 the crypto being sold (`Hold s.reserved_quantity`, below) on a sell, and the96 the crypto being sold (`Holdings.reserved_quantity`, below) on a sell, and the 94 97 cash (`Users.reserved_balance`) on a buy. Since v04 (after P7) an order can 95 98 wait in the order book and be filled in parts, so `status` is a real … … 113 116 || `quantity` || numeric(20,4) || required, > 0 || 114 117 || `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) || 115 || `price` || numeric(18,6) || the limit price; for a market order, the market price when it was placed ||118 || `price` || numeric(18,6) || optional — the limit price; for a market order, the market price when it was placed || 116 119 || `placed_at` || timestamptz || required, defaults to now || 117 120 || `executed_at` || timestamptz || optional, set when the order settles || … … 139 142 writes directly. 140 143 141 '''Keys:''' candidate `{id}` — `{market_id, executed_at}`looks unique in142 principle, but two trades can share a timestamp, so it is not a safe key;144 '''Keys:''' candidate `{id}` — "market plus `executed_at`" looks unique in 145 principle, but two trades on a market can share a timestamp, so it is not a safe key; 143 146 primary key '''`id`''' (a plain auto-incrementing integer here rather than a 144 147 UUID, because this is the highest-volume entity set and it is only ever read … … 146 149 147 150 ||= Attribute =||= Type =||= Constraints =|| 148 || `id` || integer || PK, required, auto-generated||151 || `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || 149 152 || `executed_at` || timestamptz || required || 150 153 || `price` || numeric(18,6) || required, > 0 || … … 166 169 167 170 ||= Attribute =||= Type =||= Constraints =|| 168 || `id` || integer || PK, required, auto-generated||171 || `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || 169 172 || `event_type` || text || required, `placed`, `partially_filled`, `filled` or `cancelled` || 170 173 || `quantity` || numeric(20,4) || required — the ordered quantity for `placed`, the filled amount for a fill, the unfilled rest for `cancelled` || 171 174 || `price` || numeric(18,6) || optional — the order price, or the trade price for a fill || 172 175 || `status_after` || text || required, the order's status after the event || 173 || `created_at` || timestamptz || required, defaults to now||176 || `created_at` || timestamptz || required, set automatically when the event is recorded (`clock_timestamp()`, so events inside one transaction keep their real order) || 174 177 175 178 ==== !MarketCandles ==== … … 179 182 screen refresh does not scale. 180 183 181 '''Keys:''' candidates `{id}`, `{market_id, timeframe, candle_time}` — a market 182 has exactly one candle per timeframe per time bucket; primary key '''`id`''', the 183 composite is enforced as a uniqueness rule because it is the real-world 184 constraint and it is what prevents duplicate candles. 185 186 ||= Attribute =||= Type =||= Constraints =|| 187 || `id` || integer || PK, required, auto-generated || 184 '''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a market 185 has exactly one candle per timeframe per time bucket, so the market a candle 186 `Aggregates` together with `timeframe` and `candle_time` also identifies it. 187 This is the real-world constraint that prevents duplicate candles. P2 enforces 188 it as `UNIQUE(market_id, timeframe, candle_time)`. 189 190 ||= Attribute =||= Type =||= Constraints =|| 191 || `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || 188 192 || `timeframe` || text || required, `1m`, `5m`, `1h` or `1d` || 189 193 || `open`, `high`, `low`, `close` || numeric(18,6) || all required || … … 196 200 several lists ("long term", "watching today") and each needs its own name. 197 201 198 '''Keys:''' candidate `{id}` — `{user_id, name}` would also work if list names199 were required to be unique per user, which the model does not impose, so it is200 not listed as a candidate key; primary key '''`id`'''.202 '''Keys:''' candidate `{id}`; primary key '''`id`'''. "Owner plus `name`" would 203 also identify a list if names had to be unique per user, but the model does 204 not require that, so there is no uniqueness rule here. 201 205 202 206 ||= Attribute =||= Type =||= Constraints =|| … … 205 209 || `created_at` || timestamptz || required, defaults to now || 206 210 207 === Relationships === 208 209 ==== !QuotedOn — Cryptos (1) : Markets (N), total on Markets ==== 210 Ties a market to the asset it trades. One asset can be quoted in many markets; 211 every market must have exactly one asset, hence total participation on the 212 `Markets` side. No attributes. 213 214 ==== !PlacedOn — Markets (1) : Orders (N), total on Orders ==== 215 Records which market an order was placed on. Every order must name a market; 216 a market may have no orders yet. No attributes. 217 218 ==== Places — Users (1) : Orders (N), total on Orders ==== 219 Records who placed an order. Every order belongs to exactly one user; a new 220 user has no orders. No attributes. 221 222 ==== Records — Users (1) : Transactions (N), total on Transactions ==== 223 Attributes each ledger entry to a user. Every entry belongs to exactly one 224 user. No attributes. 225 226 ==== Settles — Orders (1) : Transactions (N), partial on both sides ==== 227 Links a ledger entry to the order that caused it. Partial on the 228 `Transactions` side because deposits have no originating order, and partial on 229 the `Orders` side because an order that never executes never produces a 230 ledger entry — which is why the corresponding column is nullable in P2. No 231 attributes. 232 233 ==== Fills — Markets (1) : !MarketTrades (N), total on !MarketTrades ==== 234 Every executed trade happened on exactly one market. No attributes. 235 236 ==== !FillsBuy — Orders (1) : !MarketTrades (N), partial on both sides ==== 237 ''Added in v04, after P7.'' The buy order a trade filled. An order can be 238 filled by many trades (partial fills); a trade fills at most one buy order, 239 and none when the simulated market was the buyer. No attributes. 240 241 ==== !FillsSell — Orders (1) : !MarketTrades (N), partial on both sides ==== 242 ''Added in v04, after P7.'' The sell order a trade filled, symmetric to 243 `FillsBuy`. A trade between two users' orders participates in both. No 244 attributes. 245 246 ==== Logs — Orders (1) : !OrderEvents (N), total on !OrderEvents ==== 247 ''Added in v04, after P7.'' Every event belongs to exactly one order. No 248 attributes. 249 250 ==== Aggregates — Markets (1) : !MarketCandles (N), total on !MarketCandles ==== 251 Every candle summarises trades of exactly one market. No attributes. 252 253 ==== Owns — Users (1) : Watchlists (N), total on Watchlists ==== 254 Every watchlist belongs to exactly one user. No attributes. 255 256 ==== Holds — Users (M) : Cryptos (N), partial on both sides, '''with attributes''' ==== 257 A user's position in an asset. M:N because one user holds many assets and one 258 asset is held by many users, and partial on both sides because a user may hold 259 nothing and an asset may be held by nobody. Modeled as a relationship rather 260 than an entity set because a position has no identity of its own — it is 261 entirely described by ''which user'', ''which asset'', and how much. 211 ==== Holdings ==== 212 A user's position in one crypto asset: how much of it the user owns, how much 213 of that is already promised to open sell orders, and at what average price it 214 was accumulated. ''An entity set since v05'' (until v04 it was the M:N 215 relationship `Holds`). A holding has its own identifier and its own 216 lifecycle: it is created on the first buy, updated on every later fill, and 217 the prototype reads and locks it as a unit (`SELECT … FOR UPDATE` on the sell 218 path). It is linked to its owner through `Holds` and to its asset through 219 `PositionIn`. 220 221 '''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a user 222 has at most one holding per crypto, so the user who `Holds` it together with 223 the crypto it is a `PositionIn` also identifies a holding. P2 enforces this as 224 `UNIQUE(user_id, crypto_id)`. 262 225 263 226 `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: … … 270 233 271 234 ||= Attribute =||= Type =||= Constraints =|| 235 || `id` || UUID || PK, required || 272 236 || `quantity` || numeric(20,4) || required, ≥ 0 — total amount owned || 273 237 || `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 || 274 || `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 ||238 || `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 || 275 239 || `created_at` || timestamptz || required, defaults to now || 276 240 || `updated_at` || timestamptz || optional || 277 241 278 ==== Contains — Watchlists (M) : Cryptos (N), partial on both sides, '''with attribute''' ==== 279 Which assets are on which watchlist. M:N: a list holds many assets, an asset 280 appears on many lists. Partial on both sides — an empty list is valid and an 281 asset need not be on any list. 282 283 ||= Attribute =||= Type =||= Constraints =|| 242 ==== !WatchlistItems ==== 243 One asset placed on one watchlist. ''An entity set since v05'' (until v04 it was 244 the M:N relationship `Contains`). It has its own identifier, and it is linked 245 to its list through `Contains` and to its asset through `Lists`. 246 247 '''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' an asset 248 appears at most once on a given list, so the watchlist that `Contains` an item 249 together with the crypto it `Lists` also identifies the item. P2 enforces this 250 as `UNIQUE(watchlist_id, crypto_id)`. 251 252 ||= Attribute =||= Type =||= Constraints =|| 253 || `id` || UUID || PK, required || 284 254 || `added_at` || timestamptz || required, defaults to now — recorded so a list can be shown in the order the user built it || 255 256 === Relationships === 257 258 ==== !QuotedOn — Cryptos (1) : Markets (N), total on Markets ==== 259 Ties a market to the asset it trades. One asset can be quoted in many markets; 260 every market must have exactly one asset, hence total participation on the 261 `Markets` side. No attributes. 262 263 ==== !PlacedOn — Markets (1) : Orders (N), total on Orders ==== 264 Records which market an order was placed on. Every order must name a market; 265 a market may have no orders yet. No attributes. 266 267 ==== Places — Users (1) : Orders (N), total on Orders ==== 268 Records who placed an order. Every order belongs to exactly one user; a new 269 user has no orders. No attributes. 270 271 ==== Records — Users (1) : Transactions (N), total on Transactions ==== 272 Attributes each ledger entry to a user. Every entry belongs to exactly one 273 user. No attributes. 274 275 ==== Settles — Orders (1) : Transactions (N), partial on both sides ==== 276 Links a ledger entry to the order that caused it. Partial on the 277 `Transactions` side because deposits have no originating order, and partial on 278 the `Orders` side because an order that never executes never produces a 279 ledger entry — which is why the corresponding column is nullable in P2. No 280 attributes. 281 282 ==== Fills — Markets (1) : !MarketTrades (N), total on !MarketTrades ==== 283 Every executed trade happened on exactly one market. No attributes. 284 285 ==== !FillsBuy — Orders (1) : !MarketTrades (N), partial on both sides ==== 286 ''Added in v04, after P7.'' The buy order a trade filled. An order can be 287 filled by many trades (partial fills); a trade fills at most one buy order, 288 and none when the simulated market was the buyer. The role of `Orders` in this 289 relationship is ''the buy order'' of the trade. No attributes. 290 291 ==== !FillsSell — Orders (1) : !MarketTrades (N), partial on both sides ==== 292 ''Added in v04, after P7.'' The sell order a trade filled, symmetric to 293 `FillsBuy`; the role of `Orders` here is ''the sell order'' of the trade. A 294 trade between two users' orders participates in both. No attributes. 295 296 ==== Logs — Orders (1) : !OrderEvents (N), total on !OrderEvents ==== 297 ''Added in v04, after P7.'' Every event belongs to exactly one order. No 298 attributes. 299 300 ==== Aggregates — Markets (1) : !MarketCandles (N), total on !MarketCandles ==== 301 Every candle summarises trades of exactly one market. No attributes. 302 303 ==== Owns — Users (1) : Watchlists (N), total on Watchlists ==== 304 Every watchlist belongs to exactly one user. No attributes. 305 306 ==== Holds — Users (1) : Holdings (N), total on Holdings ==== 307 ''1:N since v05.'' Every holding belongs to exactly one user. A user may hold 308 nothing yet, so participation is partial on the `Users` side. No attributes. 309 310 ==== !PositionIn — Cryptos (1) : Holdings (N), total on Holdings ==== 311 ''Added in v05.'' Every holding is a position in exactly one crypto asset. An 312 asset may be held by nobody. No attributes. 313 314 Together, `Holds` and `PositionIn` still say what the old M:N `Holds` said: 315 a user can hold many assets and an asset can be held by many users. The 316 difference is that the position is now a thing with its own identity, not 317 just a pair. The rule "at most one holding per user and crypto" is stated 318 under Holdings. 319 320 ==== Contains — Watchlists (1) : !WatchlistItems (N), total on !WatchlistItems ==== 321 ''1:N since v05.'' Every watchlist item is on exactly one list. An empty list is 322 valid, so participation is partial on the `Watchlists` side. No attributes. 323 324 ==== Lists — Cryptos (1) : !WatchlistItems (N), total on !WatchlistItems ==== 325 ''Added in v05.'' Every watchlist item names exactly one crypto asset. An asset 326 need not be on any list. No attributes. 285 327 286 328 == Entity-Relationship Model History == … … 300 342 Nothing existing was removed or changed. See 301 343 [wiki:AdvancedDatabaseDevelopment]. 302 The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`; earlier versions 344 The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`. 345 * '''v05 — correction after review.''' The review of P2 found that two parts of the model were implemented differently in the database: 346 * `Contains` was an M:N relationship in the model, but `watchlist_items` has its own `id` primary key; 347 * `Holds` was an M:N relationship in the model, but `holdings` has its own `id` primary key. 348 349 An M:N relationship has no identifier of its own; its table's key is the pair 350 of participating keys. A table with its own `id` is the implementation of an 351 entity set. Every phase after P2 (the prototype, the reports and the P7 352 logic) already uses the database as it is. So the '''model''' was corrected to 353 match P2, not the other way round: 354 * `Holds` (M:N, with attributes) became the entity set `Holdings` (its former attributes plus `id`) with two 1:N relationships, `Holds` (Users → Holdings) and `PositionIn` (Cryptos → Holdings), both total on the `Holdings` side; 355 * `Contains` (M:N, with `added_at`) became the entity set `WatchlistItems` (`id`, `added_at`) with `Contains` (Watchlists → !WatchlistItems) and `Lists` (Cryptos → !WatchlistItems), both total on the `WatchlistItems` side; 356 * the former keys of the two relationships are kept as uniqueness rules ("one holding per user and crypto", "an asset at most once per list"); 357 * the key descriptions of `Markets`, `MarketTrades`, `MarketCandles` and `Watchlists` no longer name foreign-key columns (`crypto_id`, `market_id`, `user_id`), which do not exist in an ER model; 358 * the diagram was redrawn on a grid with no overlapping attributes. In v04, `Watchlists.id` was hidden behind `added_at`, and several attributes of `Orders`, `Transactions`, `MarketTrades` and `MarketCandles` overlapped. The grid also makes it easier to compare the diagram with the P2 relational diagram. 359 360 The diagram files are `ERModel_v05.xml` / `ERModel_v05.png`; earlier versions 303 361 are kept. 304 362
Note:
See TracChangeset
for help on using the changeset viewer.
