¶ Summary
A data model representing class relationships, including a significant number of subclasses, needs to be stored in a relational database. The model must support efficient querying across hierarchical relationships.
The objective is to implement the schema in a way that preserves the richness of the class hierarchy while enabling queries to be expressed in a concise, elegant, and maintainable form.
A relational database is chosen that supports table inheritance to represent subclass relationships effectively. Indexing strategies and SQL constructs are applied to optimize both readability and performance.
The resulting database schema enables compact, expressive queries for navigating class hierarchies, balancing relational integrity with usability. Query complexity is minimized, supporting both maintainers and end users in retrieving hierarchical data efficiently.
¶ Implementation example
¶ financial products modeling on an object-relational database
- the financial products found in all the major cross-asset risk-management applications can be modeled with an inheritance pattern: basic products have evolved into more sophisticated, "exotic" products.
- an object-relational database like Postgresql can help with the definition of such products and a better support for consistency.
- for example: the dates attribute of the legs of a swap should all have the same value.
- in a traditional model, the swaps are in one table and their legs are in other tables; the consistency of the dates has to be guaranteed by the application.
- by adopting this model, the same is guaranteed by design.
¶ example of class diagram
glossary:
IRD = interest-rate derivative
IRS = interest-rate swap, a type of IRD
¶ database image
image: "postgres:9.6.20"
¶ SQL script
drop table if exists IRS_fixed_legs;
drop table if exists IRS_floating_legs;
drop table if exists IRS_legs;
drop table if exists IRD_swaps;
drop table if exists IRD_rates_derivative_trades;
drop table if exists trades;
CREATE TYPE buysell_ AS ENUM ('B', 'S');
CREATE TYPE upfront_inarrears_ AS ENUM ('UF', 'AR');
CREATE TABLE trades (
id SERIAL PRIMARY KEY, -- integer + AUTO_INCREMENT
trader text,
buysell buysell_,
portfolio text,
counterpart text,
insert_datetime timestamp ,
trade_date date,
expiry_date date,
state text
);
CREATE TABLE IRD_rates_derivative_trades (
ccy char(3),
notional money -- two-digit precision
) INHERITS (trades);
CREATE TABLE IRD_swaps (
id SERIAL PRIMARY KEY
-- this PK override will allow the the proper FK in table IRS_legs
) INHERITS (IRD_rates_derivative_trades);
CREATE TABLE IRS_legs (
id SERIAL PRIMARY KEY,
trade integer REFERENCES IRD_swaps(id),
startdate date,
enddate date,
upfront_inarrears upfront_inarrears_
);
CREATE TABLE IRS_fixed_legs (
fixed_rate real -- 6 decimal digits precision
) INHERITS (IRS_legs);
CREATE TABLE IRS_floating_legs (
floating_index text,
floating_index_spread real -- 6 decimal digits precision
) INHERITS (IRS_legs);
selecting entries - example:
SELECT * FROM IRD_swaps;
SELECT * FROM ONLY trades; -- i.e. not IRD_swaps
ref.
https://www.postgresql.org/docs/current/tutorial-inheritance.html
The inheritance hierarchy is actually a directed acyclic graph
https://www.postgresql.org/docs/current/datatype.html
¶
back to Portfolio