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?

When I make trading solutions I do make a transaction table that contains: transactionID, timestamp, orderID, tradingID, buyingPrice, sellingPrice, currency

Then for the trader I would make a related table containing: transactionID, timestamp, comment, and whatever the trader would like to keep track of 

To my way of thinking the task at hand should be the primary table, you should be able to do look ups to those less performance critical tables at the time of making reports; there would be no need for the actual trader to engage in social pornography such as client's name at the time of conducting the trades. In other words I believe your solution will benefit from a relational structure designed from an operational standpoint. And when the time comes you can build a new anchor-boy for querying and doing reports, but making those reports would be a highly administrative task, no? I would actually put those tables in a different database file.

I find some key factors never fail me in making the right choices when it comes to workflow management: Abraham Maslow the pyramid of needs; the pyramid will most likely help you set the priorities straight. Further to the cause I really do like the completion backwards principle; imagining what you want to end up with and build from there is always a good strategy; or at least it was to me.

Do you have to take into consideration skew and latency between locations?

Edited March 20, 20169 yr by ggt667

On 3/16/2016 at 4:18 PM, UltraNIX said:

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.

 

What is in the STOCK table?

Just from your description and thinking about the entities I would go for:

STOCKS - the stock itself

possibly STOCK DATA: the price of any given stock at a given time

TRADERS (Accounts?) - the people that make trades

TRADES (the buying and selling of stocks) - could have its own line items table for the trade details if multiple items belong to one trade

 

 

Note that this is an XPost of a thread on FileMaker Community:

https://community.filemaker.com/message/552152#552152

On 18 March, 2016 at 3:33 PM, BruceR said:

Note that this is an XPost of a thread on FileMaker Community

How does this happen and not mentioned unless you post a link?

Not sure I understand what you are saying, Bruce posted the link.  And how does it happen?  Anybody can post to any forum, no restrictions there.  Netiquette however would proscribe that the poster uses something like "XPOST" in their title so that people would know the question is on more than one forum.