Relational Design

Descriptive representation of the relational schema

Notation: bold = primary key, *italic* = foreign key.

• Users(<u>id</u>, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at)
  • Candidate keys: {id}, {username}, {email}. UNIQUE(username), UNIQUE(email).
• Crypto(<u>id</u>, symbol, name, created_at)
  • Candidate keys: {id}, {symbol}. UNIQUE(symbol).
• Markets(<u>id</u>, *crypto_id*, quote_currency, is_active, created_at)
  • Candidate keys: {id}, {crypto_id, quote_currency}. UNIQUE(crypto_id, quote_currency).
• Holdings(<u>id</u>, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at)
  • Transformation of the M:N relationship Holds. Candidate keys: {id} and {user_id, crypto_id} — the latter is the relationship's own key and is enforced with UNIQUE(user_id, crypto_id). id was chosen as PK for consistency with the other relations.
  • avg_price is NOT NULL DEFAULT 0 CHECK (avg_price >= 0).
• Orders(<u>id</u>, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
  • side ∈ {buy, sell}, type ∈ {market, limit}, status ∈ {open, executed, cancelled}.
• Transactions(<u>id</u>, *user_id*, type, amount, currency, *related_order*, created_at, description)
  • type ∈ {deposit, buy, sell, fee}.
• MarketTrades(<u>id</u>, *market_id*, executed_at, price, quantity, side, source)
• MarketCandles(<u>id</u>, *market_id*, timeframe, open, high, low, close, volume, candle_time)
  • UNIQUE(market_id, timeframe, candle_time).
• Watchlists(<u>id</u>, *user_id*, name, created_at)
• WatchlistItems(<u>id</u>, *watchlist_id*, *crypto_id*, added_at)
  • Transformation of the M:N relationship Contains. Candidate keys: {id} and {watchlist_id, crypto_id}, the latter enforced with UNIQUE(watchlist_id, crypto_id).

Transformation method used

Partial transformation. Applied as follows:

• Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping its UUID (or serial) primary key.
• Each 1:N relationship without attributes is transformed by adding the parent's primary key as a foreign-key column on the child table — the "N" side. This is where every foreign key in the schema comes from, and it is why no foreign keys appear in the ER diagram itself: QuotedOn → markets.crypto_id, PlacedOn → orders.market_id, Places → orders.user_id, Records → transactions.user_id, Settles → transactions.related_order, Fills → market_trades.market_id, Aggregates → market_candles.market_id, Owns → watchlists.user_id.
• Each M:N relationship becomes its own table holding the two foreign keys plus the relationship's own attributes: Holds → holdings, Contains → watchlist_items. The pair of foreign keys is the relationship's key and is enforced as a UNIQUE constraint in both tables.
• Total participation in the ER model becomes NOT NULL on the corresponding foreign key; partial participation stays nullable. Settles is partial on both sides, which is exactly why transactions.related_order is the one nullable foreign key in the schema — a deposit has no originating order.

Normalisation

All relations are in 3NF:

• Every attribute is atomic (no repeating groups, no composite fields).
• No partial dependency exists because every primary key is a single UUID column.
• No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, holdings.quantity depends on holdings.id, not on user_id via some intermediate.
• avg_price in Holdings is a derived value cached for performance (it is the weighted-average entry price across all buy transactions for that (user, crypto) pair) — it is drawn as a derived attribute in the ER diagram. We accept the denormalisation: it is recomputed by the database inside the same transaction as each buy, in the same statement that changes the quantity (INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE), so the stored average and the stored quantity can never disagree.
• avg_price is declared NOT NULL DEFAULT 0. This matters: it is used in the P/L arithmetic of v_portfolio, and in SQL any arithmetic involving NULL yields NULL, so a nullable average would have silently blanked the unrealised-P/L column for an existing position instead of failing loudly.

DDL script

The script that creates the entire schema is [../server/db/schema_creation.sql](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the project schema every run, so it works on an empty database and on a database that already has the schema.

The script creates:

• 10 tables with check constraints, primary keys, foreign keys and unique constraints.
• 5 performance indexes.
• 2 views: v_latest_prices (latest trade price per market) and v_portfolio (per-user holdings valuation with unrealised P/L).

DML script (sample data)

The script that loads realistic sample data is [../server/db/data_load.sql](../../server/db/data_load.sql). It is idempotent: it truncates all tables with CASCADE then re-inserts. Loaded:

• 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets.
• 3 sample users (alice, bob, charlie) with password test123 (sha256 hex).
• 18 recent market trades across all markets so v_latest_prices is populated.
• 10 one-hour candles (BTC and ETH).
• One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly.
• Two watchlists with five watchlist items.

Relational diagram


Generated in Pgadmin from the live project schema, in crow's-foot notation — not drawn by hand, so it is evidence that the deployed database actually matches the design described above. Each box is a table with its columns and declared types; key icons mark primary keys and the arrowed lines are the 12 declared foreign keys.

How to regenerate it

With pgAdmin 4, if DBeaver is unavailable — it reads the live schema the same way, so the result is equivalent in substance:

1. Connect to the project database.
2. Right-click the database → ERD For Database (or open a blank ERD and drag the project tables in).
3. Arrange the tables to mirror ERModel_v01.png.
4. Download image → PNG, then convert: convert relational_schema.png relational_schema.jpg

AI use

Reasoning for the AI-assisted part of this phase, and the full interaction log, are on ​https://github.com/StefanTrsunov/bp/blob/main/docs/P2-RelationalDesign/RelationalDesignAIUsage.md