I used to work on wall street as a “white hat” trader...
Disclaimer: This is not investment advice. Also not medical, legal or any other kind of advice ;-)
and http://www.filemaker-fanatics.com/e001-technical-debt.php ...start at 9:17 in for the wall street disclaimer.
You have some of the pieces right...and some missing.
Here is how I would start
Accounts -< Orders -< Order_Items (vs. Line_Items)
I would say that you would want a table for Stock_Trades
Example of a Stock_Trade ... Let say that you are buying 1,000 shares of Apple (AAPL) “at the market”. You might get a report back that you bought 400 @ 105.96 and and 600 @ 105.98
Therefore Order_Items -< Stock_Trades (2 trades, 1 for 400 and 1 for 600 shares)
So now we have: Accounts -< Orders -< Order_Items -< Stock_Trades
I have not heard the phase “Primary Table” in common use. All tables have a role to play in an entity–relationship diagram.
Stock_Trade swould likely be your most granular table and could have lots of attributes:
- Counter party
- Spread at the time
- Tick +, -, 0-
Coming from another direction you would have:
ListedCompanies -< StockTickerSymbols (because a company can change its stock ticker symbol over time, and still be the same company)
You would also have a table for StockTickerSymbol_Day with most (but not all) of the attributes that you mentioned:
date, symbol, open, high, low, close prices and volume.
Now if you wanted to make your simulation more real world, perhaps in a 2.0 release...
You might have a table for the order book separate from stock_trades as bids and offers and associate quantities change over time up and down the book and across exchanges. That way you could track if there was any quote stuffing going on and what the High Frequency Traders were doing and how much latency arbitrage is going on.
You could set up a table to keep track of the speed of execution between exchanges so that you know how to break up your order so that you minimize being front run by HFT traders working with faster connections.
Using the example above, you might want to route the order for 400 to the exchange that it is offered on (assuming you can with the broker that you are using) and put the order for 600 in separately.
Final piece of non-advice. Buy stocks that go up. If they do not go up, then do not buy them ;-)
Hope that helps.
Thank you Tony, great insights!
But how do I link Accounts -< Orders -< Order_Items -< Stock_Trades to ListedCompanies -< StockTickerSymbols?
The reason I need this, is because I use a complex back-trade order confirmation formula, which, if all variables that are required are not present, will not allow to enter the order into the database (not into the broker or any other system). So, I basically need Orders and Order_Items to take information from StockTickerSymbols_Day.
I would need to know more to address your question with any certainty. I will guess that you could link on a natural key (symbol) based on a feed and import of stock_trade confirmations from the broker.
So much depends on the details...
Do you have a pre-existing solution, or are you starting from a clean piece of paper?
Do you have written specification?
- Bullet point feaures
- Entity–relationship diagram?
- Flow charts?
- Data sources
- Network diagram?
A lot will depend on how robust you need to build, ranging from a single person hobby, to a system used by an investment club, to a system used at an investment firm, etc.