Database Structure

This page covers the Postgres schema, as well as relationships between tables.

Accounts

CREATE TABLE Account (
    ID              int,
    username        varchar(15) NOT NULL,
    password        varchar(20) NOT NULL,
    register_time   TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY(ID)
);

Pretty straightforward. Although, we're completely missing funds_available or something like that.

Orders

CREATE TABLE Orders (
    order_ID        int,
    symbol          varchar(10) NOT NULL,
    action          varchar(4) NOT NULL,
    quantity        int,
    filled          int,
    price           float8,
    user_ID         int,
    status          varchar(9) NOT NULL,
    time_placed     TIMESTAMP WITH TIME ZONE,
    time_updated    TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY(order_ID),
    FOREIGN KEY(user_ID)
        REFERENCES Account(ID)
);

Realistically, the NOT NULL is unnecessary, since we ensure the data is inserted/updated correctly in the application. However, if one were to directly access it via CLI or a postgres client, they might make a mistake, ergo NOT NULL.

• order_ID: The primary key, since each order would have a unique ID.
• symbol: market ticker, like $MSFT
• action: BUY or SELL
• user_ID: the user_ID of the user who placed the order, linked to the Account table's ID field.
• status: One of PENDING, COMPLETE, CANCELLED.

Quick note on Updating Orders

-- This allows us to do HOT updates, critical for fast updates!
-- HOT (Heap only Tuple) updates do not force re-indexing + can
-- allow deletion of stale records outside of AUTOVACUUM Calls.
ALTER TABLE Orders SET (fillfactor = 70);

Pending Orders

CREATE TABLE PendingOrders (
    order_ID        int,
    PRIMARY KEY(order_ID),
    FOREIGN KEY(order_ID)
        REFERENCES Orders(order_ID)
);

This is really just around to help decrease the search space when we fill markets on program start-up. Searching through the Orders table for rows where status='PENDING' would be a lot slower than just maintaining a separate table of relevant records. Note that we keep the table as small as possible by only storing the order_ID, which is linked to the primary key of the Orders table.

Trades

CREATE TABLE ExecutedTrades (
    symbol          varchar(10) NOT NULL,
    action          varchar(4) NOT NULL,
    price           float8,
    filled_OID      int,
    filled_UID      int,
    filler_OID      int,
    filler_UID      int,
    exchanged       int,
    execution_time  TIMESTAMP WITH TIME ZONE,
    -- Will never have 2+ trades with the same
    -- (filled, filler) order id pair
    PRIMARY KEY(filled_OID, filler_OID),
    FOREIGN KEY(filled_OID)
        REFERENCES Orders(order_ID),
    FOREIGN KEY(filler_OID)
        REFERENCES Orders(order_ID),
    FOREIGN KEY(filled_UID)
        REFERENCES Account(ID),
    FOREIGN KEY(filler_UID)
        REFERENCES Account(ID)
);

This is the most intricate table in our schema, entirely due to the number of references to fields of other tables. Trades consist of 2 orders, one that acts as thefiller, and the other as the filled. The action is set by the filled order, however, since the users who participated in a trade placed different types of orders (one BUY, the other a SELL), we interchange the action in the application depending on who is viewing the Trade.

Markets

CREATE TABLE Markets (
    symbol          varchar(10) NOT NULL,
    name            varchar(300) NOT NULL,
    total_buys      int,
    total_sells     int,
    filled_buys     int,
    filled_sells    int,
    latest_price    float8,
    PRIMARY KEY(symbol)
);

This table is the most likely candidate for modification in the future. I'm not convinced that filled_buys/sells matters (at all), and I think we should probably store some type of volumetric data (although that may be better achieved in another table, say 24H_VOL, or even via Redis).

Maintaining Sequential Order IDs

-- We have to store the count in a table because postgresql
-- doesn't store row count as metadata, getting row count
-- with SELECT count(*) from Orders; would be prohibitively expensive
CREATE TABLE ExchangeStats (
    key             int,
    total_orders    int,
    PRIMARY KEY (key)
);

This table stores a single row: 1, total_orders. We store a key only so that we can do the following query: INSERT INTO ExchangeStats VALUES (1, new_val) ON CONFLICT DO UPDATE SET total_orders=new_val;.

If not for the key, we would not be able to upsert, which would be frustrating because of the single case where the exchange is empty.