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]
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?