Financial-Data

Relational database schema design for equity portfolio management, covering conceptual ER modeling, physical DDL scripts, and complex analytical queries for financial reporting.

The repository is built incrementally — one design slice per commit — so each stage is reviewable on its own instead of arriving as a finished dump.

Status

Stage	State
Conceptual ER model (entities, cardinality, integrity rules)	done
Physical DDL: core tables	done
Seed: reference data (venues, issuers, instruments, portfolios, FX)	done
Seed: market data, trades and positions	next
Corporate actions (dividends, splits)	planned
Analytical queries: holdings, P&L, NAV, attribution	planned
Performance: partitioning, indexing strategy, query plans	planned

Repository structure

Financial-Data/
├── README.md                     # this file
├── docs/
│   └── er_model.md               # conceptual model: entities, relationships, trade-offs
└── sql/
    ├── ddl/
    │   └── 001_core_tables.sql   # PostgreSQL DDL for the core tables
    └── seed/
        └── 001_reference_data.sql # venues, issuers, instruments, portfolios, FX

Schema at a glance

Table	Grain	Notes
exchange	one venue	keyed by ISO 10383 MIC
issuer	one legal entity	carries ISO 17442 LEI
instrument	one security on one exchange	EQUITY / ETF / PREFERRED / ADR / INDEX
price_daily	one instrument × one day	raw OHLCV plus adj_close_price
portfolio	one managed pool	holds base currency and benchmark
trade	one executed trade	quantity always positive, direction in side
position_daily	one portfolio × instrument × day	snapshot, negative quantity = short
fx_rate	one currency pair × one day	1 base = rate quote

Full reasoning — why positions are snapshotted rather than derived, why money is NUMERIC and not float, what was deliberately left out — is in docs/er_model.md.

Getting started

Requires PostgreSQL 10 or later (the DDL uses GENERATED ALWAYS AS IDENTITY).

createdb portfolio_db
psql -d portfolio_db -f sql/ddl/001_core_tables.sql
psql -d portfolio_db -f sql/seed/001_reference_data.sql

The script runs inside a single transaction, so a failed apply leaves the database untouched.

Conventions

• Money and quantities are NUMERIC, never float. Binary floating point drifts, and P&L that does not tie out is worse than P&L that is slow.
• Direction lives in side, not in the sign of quantity. A positive quantity can therefore never contradict the side it belongs to.
• Fact tables use composite natural primary keys (instrument + date, portfolio + instrument + date), which makes a repeated load a no-op rather than a silent duplicate.
• snake_case identifiers, singular table names, ix_ / uq_ / ck_ prefixes on indexes and constraints.

Tech stack

PostgreSQL · SQL (DDL) · Mermaid (diagrams)

---

Schema design only. No real positions, prices or client data are included.