92 lines
1.7 KiB
SQL
92 lines
1.7 KiB
SQL
CREATE TABLE WatchList
|
|
(
|
|
watch_list_id INTEGER NOT NULL,
|
|
symbol VARCHAR(15) NOT NULL,
|
|
PRIMARY KEY (watch_list_id, symbol),
|
|
KEY (watch_list_id)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Trades
|
|
(
|
|
trade_id INTEGER NOT NULL AUTO_INCREMENT,
|
|
symbol VARCHAR(15) NOT NULL,
|
|
trade_date DATETIME NOT NULL,
|
|
shares NUMERIC(19,2),
|
|
price NUMERIC(19,2),
|
|
buysell CHAR(1),
|
|
status VARCHAR(10) NOT NULL,
|
|
account VARCHAR(25) NOT NULL,
|
|
commission NUMERIC(19,2) NOT NULL,
|
|
date_sold DATETIME,
|
|
sell_price NUMERIC(19,2),
|
|
PRIMARY KEY (trade_id),
|
|
KEY (account),
|
|
INDEX IX_Trades (symbol ASC, trade_date ASC)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Cash
|
|
(
|
|
account VARCHAR(25) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
description VARCHAR(255),
|
|
credit NUMERIC(19,2),
|
|
debit NUMERIC(19,2),
|
|
balance NUMERIC(19,2),
|
|
transaction_id INTEGER NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (account, date, transaction_id),
|
|
UNIQUE (transaction_id),
|
|
KEY (account),
|
|
INDEX IDX_DATE (date ASC)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE WatchLists
|
|
(
|
|
watch_list_id INTEGER NOT NULL AUTO_INCREMENT,
|
|
watch_list_name VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY (watch_list_id),
|
|
UNIQUE (watch_list_id)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE StopLimits
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
stop_limit NUMERIC(19,2),
|
|
shares NUMERIC(19,2),
|
|
stop_type VARCHAR(15),
|
|
active INTEGER,
|
|
PRIMARY KEY (symbol)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Accounts
|
|
(
|
|
account VARCHAR(25) NOT NULL,
|
|
PRIMARY KEY (account)
|
|
)
|
|
;
|
|
|
|
|
|
|
|
|
|
|
|
ALTER TABLE WatchList ADD CONSTRAINT FK_WatchList_WatchLists
|
|
FOREIGN KEY (watch_list_id) REFERENCES WatchLists (watch_list_id)
|
|
;
|
|
|
|
ALTER TABLE Trades ADD CONSTRAINT FK_Trades_Accounts
|
|
FOREIGN KEY (account) REFERENCES Accounts (account)
|
|
;
|
|
|
|
ALTER TABLE Cash ADD CONSTRAINT FK_Cash_Accounts
|
|
FOREIGN KEY (account) REFERENCES Accounts (account)
|
|
;
|