PSET7/C$50Finance/sell

Question

My portfolio table has fields user_id,Name,Symbol,Shares,Price,Total,Datetime showing who bought what at what price & when aka a record of all users buying their stocks. Now, if there is a user say Andi. Andi buys 3 shares of NFLX and then buys another 4 shares of NFLX my portfolio shows two rows for Andi with her user_id showing 3 shares for NFLX with dattime and second row for 4 shares of NFLX with datetime. Now, if she wants to sell 2 shares of NFLX should I make another table showing all shares that she wants to sell with datettime? Whereas if I update portfolio table by changing in first row the shares from 3 to 1 or in second row changing shares from 4 to 2 either seems absurd to me because we are changing the original purchase data of the user.Whereas in the walkthrough Zamyla says to go for removing the stock from user's portfolio via two ways : 1.DELETE FROM or 2.log scale as a negative quantity.

Plse excuse for such a long post but I am stuck over this question for long! Any suggestions will be appreciated!

Answer 1

A first solution, that will not change your current implementation of the database, is to log the sell as a new transaction. For example you could use the same exact code you have for buy, but instead use a negative number for the number of shares sold. Then, when you want to display the total sum of shares a user has of a stock, you can sum the rows with this user_id and this symbol.

But even though you save on space with this solution, since you don't need a separate table, you have the disadvantage that you have to compute the sums of all stocks every time you want to find the total amount of stocks for each symbol for a user_id.

What you could do instead, is create a different table altogether, which will have as primary key the tuple (user_id, symbol) and will hold the total number of shares for this user_id, for this symbol in an extra column. This will save you the time needed for the computations, but will add the cost in storage size.

You are the one who will chose between the two and what better suits your user's needs.