NoSQL (DynamoDB) database design

Question

I am developing a small project which allows to track stock portfolios. Users can have an unlimited number of portfolios which consist of an unlimited number of stocks including information about the number of stock units and the purchase price.

What I have in mind is the following but I would love to hear your thoughts:

Portfolios Table

{ 
  userid, (primary partition key, Cognito)
  portfolioid, (primary sort key, String)
  name, (String)
  stocks[] {
    stockid, (String)
    units, (Number)
    purchase_price, (Number)
    purchase_cost, (Number)
  }
}

Stocks Table

{
  stockid, (primary partition key, String),
  name, (String)
  exchange, (String)
  last_updated, (Date)
  prices[] {
    date, (Date)
    price, (Number)
  }
}

Does this make sense?

2 key queries which I will run on the portfolio:

1. Show all stocks of a portfolio with profit/loss (latest price - purchase_price)
2. Show all portfolios incl combined profit/loss (sum of profit/loss of each stock in the portfolio)

So ideally for those queries I would have the stock price information directly in the portfolios table but then I would need to update every day (or imagine I change to realtime data) every portfolio, while with the above tables I would only update the stocks table. How do I get this data most efficiently?

Looking forward to your feedback. Thanks in advance!

Answer 1

the table structure above looks good except the following changes in stocks table:

1. I would suggest having exchange to be a partition-key, because mostly stocks are classified by exchanges and their price can be different for different exchanges.
2. I would prefer to have historical price data in a separate table (say, stocks_archive) for a stock which will make queries on stocks table more efficient for profit/loss calculations at any given instance, so your stocks table should then look like:

    {   
      stockid, (primary partition key, String), // consider renaming it to 'scrip_code', ref: https://www.investopedia.com/terms/s/scrip.asp
      name, (String)  
      exchange, (String)  
      last_updated, (Date)  
      price (Number)  
    }

All the best!