Relationship graph - can't work it out.

[Greg58]

I want to design a database to record the purchase and sale of shares including dividends.  I can't even get past stage 1!!!  In my mind I would have a company table, a holdings table, and a transactions table.  But I cannot figure out how to wire this up??  I have attached my attempt.  Can anyone please give me a kick start??

 

Thanks.

Trades 210223 Copy.fmp12

[comment]

I think it depends on what exactly you want to track and for what purposes. 

Generally, I would imagine there would be a table of Companies, a table of Shareholders and a join table of Transactions with fields for:

• CompanyID
• StockType
• Amount
• SellerID
• BuyerID

This way, the current holdings of each shareholder are the sum of their purchases minus the sum of their sales of each stock. This could get slow after a while, so you may consider denormalizing and scripting the transaction to produce a stored balance.

Note also that unless you split each transaction into separate Buy and Sell records, you won't be able to produce a report showing the current holdings of more than one shareholder (again, not without denormalizing).

P.S. I believe that historically shares were numbered and each transaction was concerned with specific, individually identifiable shares. Today, this is true in theory only - but if you're dealing with private trading, this model could still be applicable.

 

 

 

[Greg58]

Thanks for the reply.  The DB is for my use only and not for a number of shareholders.  I started by thinking that I needed a join table between companies and transactions because each company can be bought and sold many times, and a transaction can apply to many companies.

companies >> join << transactions

Would this be right??

Thanks.

[comment]

If this is for tracking your own portfolio, then I believe you only need two tables: Stocks and Transactions.

But perhaps I am misunderstanding the situation (again), because:

2 hours ago, Greg58 said:

a transaction can apply to many companies

I don't see how that's possible.

Edited May 11, 2021 by comment

[Greg58]

Sorry for my poor explanation.  I buy and sell shares in various companies.  Sometimes I will buy and sell the same company several times over a period.  What I meant by "a transaction can apply to many companies" was that I might buy 5 companies (5 x buy orders).

I often have trouble working out my relationship graph.  Perhaps I over think it.  Here I was thinking that a company can have many orders, and an order can apply to many companies.  Therefore  TRANSACTION >> join << COMPANIES.

[comment]

10 minutes ago, Greg58 said:

I might buy 5 companies (5 x buy orders)

What do these 5 buy orders have in common, other than the date? IOW, what fields - other than the date - would be in the TRANSACTION table?

The way I see it, the fact that you issue several buy/sell orders together is an insignificant coincidence - therefore each such order is an individual transaction. And BTW, a transaction does not relate to a company (at least not directly) but to a stock - or more precisely, a security. A company can have several types of securities.