Jump to content

Relationship graph - can't work it out.


Greg58

This topic is 1051 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This topic is 1051 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.