Changeset 9577c79 for docs/P1-ConceptualModel/ERModel.md
- Timestamp:
- 09/16/26 23:37:15 (13 days ago)
- Branches:
- main
- Children:
- 8b447ef
- Parents:
- df05838
- File:
-
- 1 edited
-
docs/P1-ConceptualModel/ERModel.md (modified) (15 diffs)
Legend:
- Unmodified
- Added
- Removed
-
docs/P1-ConceptualModel/ERModel.md
rdf05838 r9577c79 1 # Entity-Relationship Model v.0 21 # Entity-Relationship Model v.03 2 2 3 3 ## Diagram 4 4 5  6 5  7 6 8 7 Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses … … 23 22 ## Data requirements 24 23 24 Each entity set is given as a short rationale for why it exists as its own set, 25 its keys, and its attributes as a table. Each relationship is given as its 26 cardinality and participation, a short rationale, and — where it carries data — 27 an attribute table. 28 25 29 ### Entity sets 26 30 … … 28 32 Registered participants of the platform. Every action in the simulation is 29 33 attributed to a user, and the two balance attributes are what makes the 30 simulation work: cash that is free to trade is tracked separately from cash that 31 is currently committed to open positions, so the platform can refuse a purchase 32 without having to recompute the whole portfolio first. 33 34 - **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**. 35 A surrogate UUID was chosen because it is opaque and stable — `username` and 36 `email` are both things a user may legitimately want to change later, and 37 every relationship in the diagram points at `Users`, so a mutable key would 38 propagate changes across the whole database. 39 - **Attributes:** 40 - `id` — UUID, required, primary key. 41 - `username` — text, max 50, required, unique. 42 - `email` — text, max 255, required, unique, must contain `@`. 43 - `full_name` — text, max 200, optional. 44 - `password_hash` — text, max 255, required. Never the password itself; the 45 prototype stores a SHA-256 hex digest. 46 - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0. 47 - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0. 48 - `created_at` — timestamp with time zone, required, defaults to now. 49 - `updated_at` — timestamp with time zone, optional (null until first change). 34 simulation work: cash that is free to trade is tracked separately from cash 35 that is currently committed to open positions, so the platform can refuse a 36 purchase without having to recompute the whole portfolio first. 37 38 **Keys:** candidates `{id}`, `{username}`, `{email}`; primary key **`id`**. A 39 surrogate UUID was chosen because it is opaque and stable — `username` and 40 `email` are both things a user may legitimately want to change later, and 41 every relationship in the diagram points at `Users`, so a mutable key would 42 propagate changes across the whole database. 43 44 | Attribute | Type | Constraints | 45 |---|---|---| 46 | `id` | UUID | PK, required | 47 | `username` | text(50) | required, unique | 48 | `email` | text(255) | required, unique, contains `@` | 49 | `full_name` | text(200) | optional | 50 | `password_hash` | text(255) | required — never the password itself; the prototype stores a SHA-256 hex digest | 51 | `available_balance` | numeric(18,4) | required, default 0, ≥ 0 | 52 | `invested_balance` | numeric(18,4) | required, default 0, ≥ 0 | 53 | `created_at` | timestamptz | required, defaults to now | 54 | `updated_at` | timestamptz | optional (null until first change) | 50 55 51 56 #### Cryptos … … 55 60 in the *asset*, not in a particular pair. 56 61 57 - **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same 58 reason as in `Users`; `symbol` is kept as a unique natural key because that is 59 what users type and see. 60 - **Attributes:** 61 - `id` — UUID, required, primary key. 62 - `symbol` — text, max 20, required, unique (e.g. `BTC`). 63 - `name` — text, max 255, required (e.g. `Bitcoin`). 64 - `created_at` — timestamptz, required, defaults to now. 62 **Keys:** candidates `{id}`, `{symbol}`; primary key **`id`**, for the same 63 reason as in `Users`. `symbol` is kept as a unique natural key because that is 64 what users type and see. 65 66 | Attribute | Type | Constraints | 67 |---|---|---| 68 | `id` | UUID | PK, required | 69 | `symbol` | text(20) | required, unique (e.g. `BTC`) | 70 | `name` | text(255) | required (e.g. `Bitcoin`) | 71 | `created_at` | timestamptz | required, defaults to now | 65 72 66 73 #### Markets … … 71 78 trades, candles and orders all reference the pair, not the asset. 72 79 73 - **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is 74 unique by definition, since a given asset can only be quoted once per 75 currency. Primary key: **`id`**, so that the many entity sets referencing a 76 market carry one narrow column instead of a composite key. 77 - **Attributes:** 78 - `id` — UUID, required, primary key. 79 - `quote_currency` — text, exactly 3 characters, required, default `USD`. 80 - `is_active` — boolean, required, default true. Inactive markets are hidden 81 from the trading menus but keep their history. 82 - `created_at` — timestamptz, required, defaults to now. 80 **Keys:** candidates `{id}`, `{crypto_id, quote_currency}` — that pair is 81 unique by definition, since a given asset can only be quoted once per 82 currency; primary key **`id`**, so that the many entity sets referencing a 83 market carry one narrow column instead of a composite key. 84 85 | Attribute | Type | Constraints | 86 |---|---|---| 87 | `id` | UUID | PK, required | 88 | `quote_currency` | text(3) | required, default `USD` | 89 | `is_active` | boolean | required, default true — inactive markets are hidden from the trading menus but keep their history | 90 | `created_at` | timestamptz | required, defaults to now | 83 91 84 92 #### Orders … … 88 96 makes the ledger auditable. 89 97 90 - **Candidate keys:** `{id}` only. There is no natural key — the same user can 91 place two identical orders on the same market in the same second, and both are 92 legitimately distinct. Primary key: **`id`**. 93 - **Attributes:** 94 - `id` — UUID, required, primary key. 95 - `side` — text, required, restricted to `buy` or `sell`. 96 - `type` — text, required, restricted to `market` or `limit`. The prototype 97 executes only `market` orders; `limit` exists so the model does not have to 98 change when limit orders are implemented. 99 - `status` — text, required, restricted to `open`, `executed`, `cancelled`. 100 - `quantity` — numeric(20,4), required, must be > 0. 101 - `price` — numeric(18,6), optional — null for a market order until it fills, 102 then the fill price. 103 - `placed_at` — timestamptz, required, defaults to now. 104 - `executed_at` — timestamptz, optional, set when the order fills. 98 Placing an order is what triggers a **reservation** of whatever it commits — 99 the crypto being sold (`Holds.reserved_quantity`, below) on a sell, cash 100 already handled the same way on a buy via `available_balance` / 101 `invested_balance`. `status` therefore has real meaning as a lifecycle, not 102 just a label: `open` means reserved but not yet settled, `executed` means 103 settled, `cancelled` would release the reservation without settling (not yet 104 exercised by any use case, since only market orders — which settle 105 immediately — are implemented). See 106 [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for the reserve-then-settle 107 sequence. 108 109 **Keys:** candidate `{id}` only — there is no natural key, since the same user 110 can place two identical orders on the same market in the same second, and both 111 are legitimately distinct; primary key **`id`**. 112 113 | Attribute | Type | Constraints | 114 |---|---|---| 115 | `id` | UUID | PK, required | 116 | `side` | text | required, `buy` or `sell` | 117 | `type` | text | required, `market` or `limit` — the prototype executes only `market`; `limit` exists so the model does not have to change when limit orders are implemented | 118 | `status` | text | required, `open`, `executed` or `cancelled` | 119 | `quantity` | numeric(20,4) | required, > 0 | 120 | `price` | numeric(18,6) | optional — null until the order settles, then the fill price | 121 | `placed_at` | timestamptz | required, defaults to now | 122 | `executed_at` | timestamptz | optional, set when the order settles | 105 123 106 124 #### Transactions … … 110 128 the project needs. 111 129 112 - **Candidate keys:** `{id}` only. Primary key: **`id`**. 113 - **Attributes:** 114 - `id` — UUID, required, primary key. 115 - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`. 116 - `amount` — numeric(18,4), required. Signed: negative for money leaving the 117 cash balance, positive for money arriving. 118 - `currency` — text, exactly 3 characters, required, default `USD`. 119 - `created_at` — timestamptz, required, defaults to now. 120 - `description` — text, optional, free-form human-readable explanation. 130 **Keys:** candidate `{id}` only; primary key **`id`**. 131 132 | Attribute | Type | Constraints | 133 |---|---|---| 134 | `id` | UUID | PK, required | 135 | `type` | text | required, `deposit`, `buy`, `sell` or `fee` | 136 | `amount` | numeric(18,4) | required, signed — negative for money leaving the cash balance, positive for money arriving | 137 | `currency` | text(3) | required, default `USD` | 138 | `created_at` | timestamptz | required, defaults to now | 139 | `description` | text | optional, free-form | 121 140 122 141 #### MarketTrades … … 126 145 writes directly. 127 146 128 - **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks 129 unique, but two trades can share a timestamp, so it is not a safe key. 130 Primary key: **`id`** (a plain auto-incrementing integer here rather than a 131 UUID, because this is the highest-volume entity set and it is only ever read 132 in timestamp order, never referenced by anything else). 133 - **Attributes:** 134 - `id` — integer, required, primary key, auto-generated. 135 - `executed_at` — timestamptz, required. 136 - `price` — numeric(18,6), required, must be > 0. 137 - `quantity` — numeric(20,6), required, must be > 0. 138 - `side` — text, optional, `buy` or `sell`. 139 - `source` — text, max 50, required, default `simulation`. Distinguishes a 140 simulated trade from a user's own fill (`user`). 147 **Keys:** candidate `{id}` — `{market_id, executed_at}` looks unique in 148 principle, but two trades can share a timestamp, so it is not a safe key; 149 primary key **`id`** (a plain auto-incrementing integer here rather than a 150 UUID, because this is the highest-volume entity set and it is only ever read 151 in timestamp order, never referenced by anything else). 152 153 | Attribute | Type | Constraints | 154 |---|---|---| 155 | `id` | integer | PK, required, auto-generated | 156 | `executed_at` | timestamptz | required | 157 | `price` | numeric(18,6) | required, > 0 | 158 | `quantity` | numeric(20,6) | required, > 0 | 159 | `side` | text | optional, `buy` or `sell` | 160 | `source` | text(50) | required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) | 141 161 142 162 #### MarketCandles … … 146 166 screen refresh does not scale. 147 167 148 - **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a 149 market has exactly one candle per timeframe per time bucket. Primary key: 150 **`id`**; the composite is enforced as a uniqueness rule because it is the 151 real-world constraint and it is what prevents duplicate candles. 152 - **Attributes:** 153 - `id` — integer, required, primary key, auto-generated. 154 - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`. 155 - `open`, `high`, `low`, `close` — numeric(18,6), all required. 156 - `volume` — numeric(20,6), required. 157 - `candle_time` — timestamptz, required — the start of the bucket. 168 **Keys:** candidates `{id}`, `{market_id, timeframe, candle_time}` — a market 169 has exactly one candle per timeframe per time bucket; primary key **`id`**, the 170 composite is enforced as a uniqueness rule because it is the real-world 171 constraint and it is what prevents duplicate candles. 172 173 | Attribute | Type | Constraints | 174 |---|---|---| 175 | `id` | integer | PK, required, auto-generated | 176 | `timeframe` | text | required, `1m`, `5m`, `1h` or `1d` | 177 | `open`, `high`, `low`, `close` | numeric(18,6) | all required | 178 | `volume` | numeric(20,6) | required | 179 | `candle_time` | timestamptz | required — the start of the bucket | 158 180 159 181 #### Watchlists … … 162 184 several lists ("long term", "watching today") and each needs its own name. 163 185 164 - **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names 165 are required to be unique per user; the model does not impose that, so it is 166 not listed as a candidate key. Primary key: **`id`**. 167 - **Attributes:** 168 - `id` — UUID, required, primary key. 169 - `name` — text, max 100, required. 170 - `created_at` — timestamptz, required, defaults to now. 186 **Keys:** candidate `{id}` — `{user_id, name}` would also work if list names 187 were required to be unique per user, which the model does not impose, so it is 188 not listed as a candidate key; primary key **`id`**. 189 190 | Attribute | Type | Constraints | 191 |---|---|---| 192 | `id` | UUID | PK, required | 193 | `name` | text(100) | required | 194 | `created_at` | timestamptz | required, defaults to now | 171 195 172 196 ### Relationships … … 175 199 Ties a market to the asset it trades. One asset can be quoted in many markets; 176 200 every market must have exactly one asset, hence total participation on the 177 `Markets` side. No attributes of its own.201 `Markets` side. No attributes. 178 202 179 203 #### PlacedOn — Markets (1) : Orders (N), total on Orders … … 182 206 183 207 #### Places — Users (1) : Orders (N), total on Orders 184 Records who placed an order. Every order belongs to exactly one user; a new user185 has no orders. No attributes.208 Records who placed an order. Every order belongs to exactly one user; a new 209 user has no orders. No attributes. 186 210 187 211 #### Records — Users (1) : Transactions (N), total on Transactions 188 Attributes each ledger entry to a user. Every entry belongs to exactly one user.189 No attributes.212 Attributes each ledger entry to a user. Every entry belongs to exactly one 213 user. No attributes. 190 214 191 215 #### Settles — Orders (1) : Transactions (N), partial on both sides 192 Links a ledger entry to the order that caused it. Partial on the `Transactions` 193 side because deposits have no originating order, and partial on the `Orders` side 194 because an order that never executes never produces a ledger entry. This is why 195 the corresponding column is nullable in P2. No attributes. 216 Links a ledger entry to the order that caused it. Partial on the 217 `Transactions` side because deposits have no originating order, and partial on 218 the `Orders` side because an order that never executes never produces a 219 ledger entry — which is why the corresponding column is nullable in P2. No 220 attributes. 196 221 197 222 #### Fills — Markets (1) : MarketTrades (N), total on MarketTrades … … 211 236 entirely described by *which user*, *which asset*, and how much. 212 237 213 - **Attributes:** 214 - `quantity` — numeric(20,4), required, must be ≥ 0. 215 - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse): 216 the weighted average of the prices at which the position was accumulated. 217 It is derivable from the buy history, and is stored anyway so that 218 unrealised P/L can be shown without replaying the whole ledger. 219 - `created_at` — timestamptz, required, defaults to now. 220 - `updated_at` — timestamptz, optional. 238 `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: 239 two independently updated stored numbers, with the amount actually free to use 240 computed on demand rather than stored (`quantity − reserved_quantity` here, 241 `available_balance` alone on the cash side). Without it, nothing stopped a 242 user from placing a second sell order against crypto already promised to a 243 first one — `quantity` alone cannot tell "owned" apart from "owned, but 244 already committed elsewhere." See [history](#entity-relationship-model-history), v03. 245 246 | Attribute | Type | Constraints | 247 |---|---|---| 248 | `quantity` | numeric(20,4) | required, ≥ 0 — total amount owned | 249 | `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 | 250 | `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 | 251 | `created_at` | timestamptz | required, defaults to now | 252 | `updated_at` | timestamptz | optional | 221 253 222 254 #### Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute** … … 225 257 asset need not be on any list. 226 258 227 - **Attributes:** 228 - `added_at` — timestamptz, required, defaults to now. Recorded so a list can 229 be shown in the order the user built it. 259 | Attribute | Type | Constraints | 260 |---|---|---| 261 | `added_at` | timestamptz | required, defaults to now — recorded so a list can be shown in the order the user built it | 230 262 231 263 ## Entity-Relationship Model History … … 243 275 3. `avg_price` was marked as a derived attribute rather than a plain one, to 244 276 make the denormalisation explicit rather than hidden. 277 - **v02** — Student review pass over the AI-generated v01 in the TerraER GUI. 278 - **v03** — Added `reserved_quantity` to `Holds`, and reworded `Orders.status` 279 to state its reserve → settle → (cancel) lifecycle explicitly, instead of 280 leaving `open`/`cancelled` as unused enum values. Triggered by a design 281 review that pointed out the model had no way to stop a user from placing a 282 second sell order against crypto already promised to a first, unsettled one 283 — `quantity` alone cannot distinguish "owned" from "owned, but already 284 committed." Also redrawn more compactly: every entity and relationship (with 285 its own attributes moved along with it) was pulled proportionally toward the 286 diagram's centroid, shrinking the canvas by roughly 45% with the same 287 topology and no new overlaps. See [ERModelAIUsage](ERModelAIUsage.md) for 288 the reasoning and how the diagram file itself was produced, and 289 [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) and 290 [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for how the new attribute 291 is enforced. 245 292 246 293 Reasoning for the AI-assisted part of this phase, and the full interaction log, 247 294 are on [ERModelAIUsage](ERModelAIUsage.md). 295 296 > **Student action required.** Open `ERModel_v03.xml` in TerraER, read the 297 > whole diagram — not just the new `reserved_quantity` ellipse — and change 298 > anything you disagree with, including the compaction. The phase rules 299 > require the model to be yours; this is a generated revision to review and 300 > take over, not an answer to submit unread.
Note:
See TracChangeset
for help on using the changeset viewer.
