1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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,
equity_change_type_score NUMERIC AS (CASE WHEN equity_change_type = 'BUY' THEN 1.0 ELSE -1.0 END) STORED,
equity_change_est_price DECIMAL(14, 2) AS (equity_change_cost_basis_usd / equity_change_quantity) STORED
);
|
