A relational database organises data into tables of rows and named columns, with keys and constraints that can express how records belong together. In quant work, this lets instrument metadata, positions and market observations remain separate while queries combine the right records for research or valuation. PostgreSQL calls the tables relations in its introductory explanation.
The database management system stores and manages the data. SQL is the language commonly used to define tables and query them. A database can contain just one table; the word relational does not mean that every table must connect to another.
Separate facts, connect by identity
Suppose a fictional instrument
ABC has currency USD. One position holds 2 units at an assumed unit price of USD 100. Keep the instrument's identity and metadata in instruments, and the position facts in positions.- A primary key identifies each stored record:
instrument_idin the instrument table andposition_idin the position table. - An enforced foreign key requires a position's instrument reference to exist in the instrument table. Here that reference is also declared
NOT NULL. - A database join pairs the records using an explicit condition. Defining a foreign key does not automatically add a join to a query.
Separate instrument metadata and position facts meet through an explicit key. The displayed USD 200 is quantity 2 multiplied by unit price USD 100.
This separation avoids copying the same instrument metadata into every position row. It also makes the intended relationship explicit: many positions may refer to one instrument, while the instrument key is unique.
A complete, local example
This uses Python's standard-library
sqlite3 module and an in-memory database. The example was checked with Python 3.14.4 and SQLite 3.53.0. The whole-dollar integer inputs make this arithmetic exact; they are a teaching choice rather than a general market-price representation.import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("PRAGMA foreign_keys = ON")
assert connection.execute("PRAGMA foreign_keys").fetchone() == (1,)
connection.executescript("""
CREATE TABLE instruments (
instrument_id TEXT NOT NULL PRIMARY KEY,
currency TEXT NOT NULL
);
CREATE TABLE positions (
position_id INTEGER PRIMARY KEY,
instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
quantity INTEGER NOT NULL,
unit_price_usd INTEGER NOT NULL
);
""")
connection.execute("INSERT INTO instruments VALUES (?, ?)", ("ABC", "USD"))
connection.execute("INSERT INTO positions VALUES (?, ?, ?, ?)", (1, "ABC", 2, 100))
connection.commit()
rows = connection.execute("""
SELECT i.instrument_id, i.currency, p.quantity,
p.quantity * p.unit_price_usd AS value_usd
FROM positions AS p
JOIN instruments AS i ON p.instrument_id = i.instrument_id
ORDER BY p.position_id
""").fetchall()
print(rows)
# [('ABC', 'USD', 2, 200)]
connection.close()The result contains the instrument identifier, currency, quantity and calculated value in that column order. The
ON condition supplies the match; ORDER BY specifies result ordering. Python values are passed through ? placeholders rather than inserted into SQL text, following the sqlite3 binding guidance.For SQLite, foreign-key enforcement is explicitly enabled and checked before any transaction begins. Merely writing
REFERENCES is insufficient when enforcement is off. See SQLite's enforcement requirements.What the schema does—and what it leaves to you
SQL tables are not automatically unique collections of business records. Without suitable constraints, duplicate rows can be stored;
SELECT also retains duplicate result rows unless instructed otherwise. Rows have no guaranteed retrieval order without an appropriate ORDER BY. A primary key supplies identity, not a default chronological ordering. These distinctions are part of SQLite's query rules.Constraint details depend on the database. The text key above explicitly uses
NOT NULL because ordinary SQLite text primary keys have a legacy nullability exception. SQLite also has flexible typing unless stricter rules are chosen; a declared type alone is not a complete validation policy. These behaviours are documented under CREATE TABLE.Finally, referential integrity does not establish financial correctness. A price can refer to a valid instrument and still be stale, in the wrong units or unavailable at the time of a backtest. This single-snapshot example assumes a USD price per unit. A real design needs position and observation times, currency and unit conventions, source and revision rules, and a policy for selecting the eligible mark. Changes spanning several tables may also need a database transaction.
For a longer implementation, SQL and Persistent Data builds a keyed price table and retrieves a bounded panel. Its public preview is available; the full lesson requires membership.
Sources
- PostgreSQL 18: Concepts — tables, rows, columns and relational terminology.
- SQLite: CREATE TABLE — column declarations, primary keys, nullability and constraints.
- SQLite: Foreign Key Support — reference integrity and connection-level enforcement.
- SQLite: SELECT — explicit joins, duplicate results and ordering.
- Python 3.14: sqlite3 — local database access and parameter binding.