3 Replies Latest reply on Mar 17, 2016 9:53 AM by TonyWhite

    Table Relationships advice needed


      I need your advice on table relationships setup.


      I am trying to create stock trading simulator, and I started with STOCK table, where all the stocks are placed. then I created DATA table, where all the stock data is stored (date, symbol, open, high, low, close prices and volume). and those tables are related: STOCK::symbol = DATA::symbol.


      But as for simulator, I need 3 additional tables:
      - ACCOUNTS [one account can have many orders]
      - ORDERS [one order can have many line items]
      - LINE_ITEMS


      So, basically, you start with account (let's say, record is 'Virtual account'), you then enter order (let's say, order is 'Covered call'), and that order in line_items table has 2 records (one for stock position, one for option).


      At first, I thought to relate ACCOUNTS to ORDERS and then ORDERS to LINE_ITEMS, but I need to 'squeeze in' STOCKS somewhere, because some information in Orders (and consequently, in LINE_ITEMS) should be retrieved from STOCK and it's related table DATA)


      To my way of thinking, ACCOUNTS is the primary/parent table, and I'm quite messed up whether to link STOCK to ACCOUNTS or to ORDERS or construct relationships in any other way.


      Could you please help me?

        • 1. Re: Table Relationships advice needed

          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
          • Exchange
          • TimeStamp
          • Spread at the time
          • Tick +, -, 0-
          • Etc.

          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.


          Tony White

          • 2. Re: Table Relationships advice needed

            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.

            • 3. Re: Table Relationships advice needed

              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?
              • Etc.


              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.


              Tony White