diff options
Diffstat (limited to 'src/main')
| -rw-r--r-- | src/main/resources/db/20251114-0-init-schema.sql | 53 |
1 files changed, 53 insertions, 0 deletions
diff --git a/src/main/resources/db/20251114-0-init-schema.sql b/src/main/resources/db/20251114-0-init-schema.sql new file mode 100644 index 0000000..b9b921f --- /dev/null +++ b/src/main/resources/db/20251114-0-init-schema.sql @@ -0,0 +1,53 @@ +-- Equity tracker schema +-- SQLite does not control types so directly; but it makes it more portable to a different SQL server with specificity + +-- enum as table - there may be other equity types, does not affect views directly +create table equity_type +( + equity_type_name VARCHAR(20) PRIMARY KEY NOT NULL +); + +INSERT INTO equity_type (equity_type_name) VALUES + ('STOCK'), + ('ETF'), + ('CRYPTO'); + +-- deletion of equity_type does not cascade - equity_type is required for equity_symbol, which is required for equity_change_event, which is required for fiduciary_account. +-- equity_symbol, equity_type would never be deleted, but they could be cleaned up if they are orphaned records. +create table equity_symbol +( + equity_symbol_id SMALLINT PRIMARY KEY NOT NULL, + equity_symbol_name VARCHAR(16) NOT NULL, + equity_symbol_type VARCHAR(20) NOT NULL + REFERENCES equity_type(equity_type_name), + equity_symbol_managing_company VARCHAR(96) NOT NULL, + equity_symbol_created_timestamp TIMESTAMP NOT NULL +); + +create table fiduciary_account +( + fiduciary_account_id TINYINT PRIMARY KEY NOT NULL, + fiduciary_account_number UNSIGNED BIG INT NOT NULL, + fiduciary_account_name NVARCHAR(96) NOT NULL, + fiduciary_account_description NVARCHAR(512), + fiduciary_account_created_timestamp TIMESTAMP NOT NULL +); + +-- deletion of fiduciary_account row cascades - an account precludes change events. +-- deletion of equity_symbol row does not cascade - all events should be recorded for an account, and all events must have symbols +create table equity_change_event +( + equity_change_event_id UNSIGNED BIG INT PRIMARY KEY NOT NULL, + fiduciary_account_id TINYINT NOT NULL + REFERENCES fiduciary_account(fiduciary_account_id) + ON DELETE CASCADE, + equity_symbol_id SMALLINT NOT NULL + REFERENCES equity_symbol(equity_symbol_id), + equity_change_quantity DECIMAL(20, 8) NOT NULL, + equity_change_cost_basis_usd DECIMAL(14, 2) NOT NULL, + equity_change_type VARCHAR(20) NOT NULL + CHECK (equity_change_type IN ('BUY', 'SELL')), -- This is essentially '+' or '-' + equity_change_timestamp TIMESTAMP NOT NULL +); + +--TODO construct views on event collation? heavy calculation requirements....
\ No newline at end of file |