I’m working on a financial data app where I want to track different types of securities (stocks, options, futures, bonds, etc.) and corresponding daily quotes. Currently I have a separate table for each type of security, e.g. for stocks the table looks like:
CREATE TABLE stock (
ticker VARCHAR PRIMARY KEY,
name VARCHAR NOT NULL,
exchange VARCHAR
);
The thing is, some types of securities are dependent on other ones. For example, an option is based on an underlying stock, future, bond, or even another option; similarly with futures.
Here is what I have now, with a link table that attempts to bridge these relationships:
CREATE TABLE option_map (
id INTEGER PRIMARY KEY,
underlying_type SecurityType NOT NULL,
stock_ticker VARCHAR REFERENCES stock(ticker),
future_id INTEGER REFERENCES future(id),
bond_cusip VARCHAR REFERENCES bond(cusip)
CHECK ((stock_ticker NOT NULL)::INTEGER + (future_id NOT NULL)::INTEGER
+ (bond_cusip NOT NULL)::INTEGER = 1)
);
CREATE TABLE option (
id INTEGER PRIMARY KEY,
underlying INTEGER REFERENCES option_map(id) NOT NULL,
option_type OptionType NOT NULL,
strike_price DECIMAL NOT NULL,
strike_date DATE NOT NULL
);
This solution, however, wouldn’t allow an option on an option, i.e. a column option_id INTEGER REFERENCES option(id) in table option_map since that would create a cyclic reference that StackOverflow participants seem to dislike.
What I would like (hopefully this is possible in duckdb) is a master table that contains every tracked security that can be used both for the mapping problem described above as well as for relating quotes (a separate table) to the underlying security:
CREATE TABLE security (
id INTEGER PRIMARY KEY,
underlying_type SecurityType NOT NULL,
stock_ticker VARCHAR REFERENCES stock(ticker),
option_id INTEGER REFERENCES option(id),
future_id INTEGER REFERENCES future(id),
bond_cusip VARCHAR REFERENCES bond(cusip)
CHECK ((stock_ticker NOT NULL)::INTEGER + (option_id NOT NULL)::INTEGER
+(future_id NOT NULL)::INTEGER + (bond_cusip NOT NULL)::INTEGER = 1)
);
I am relatively new to database design; any tips on how to proceed would be greatly appreciated, thanks in advance.
0
I would try to split each financial instrument into a separate table and establish relationships between the tables.
Conceptually, it would look like this.
CREATE TABLE stocks
(
id INTEGER PRIMARY KEY
/* other attributes */
);
CREATE TABLE stock_options
(
id INTEGER PRIMARY KEY,
stock_id INTEGER NOT NULL REFERENCES stocks (id),
kind OptionKind NOT NULL,
strike_price DECIMAL NOT NULL,
strike_date DATE NOT NULL,
/* other attributes */
UNIQUE (stock_id, kind, strike_price, strike_date)
);
CREATE TABLE options_on_stock_options
(
id INTEGER PRIMARY KEY,
stock_option_id INTEGER NOT NULL REFERENCES stock_options (id),
kind OptionKind NOT NULL,
strike_price DECIMAL NOT NULL,
strike_date DATE NOT NULL,
/* other attributes */
UNIQUE (stock_option_id, kind, strike_price, strike_date)
);
/* By analogy for bonds, bond options, options on bond options, etc. */
P.S. I am not an expert in the field of financial instruments, so it is probably the first time I hear about such financial instruments as options on options. But using the approach described above, you can quite implement them.