Problem with splitting private stock market packets in application

Question

I've written a small application that deals with the private stock market. I store all data in one table (to simplify problem) with following columns:

ID, TransactionType, Quantity, Numbers From, Numbers Too, Client ID, Date
1, Buy, 100, AB1000, AB1099, 5, 2009-01-30
2, Sell, 100, AB1000, AB1099, 5, 2010-01-01
3, Buy, 40, AB500, AB539, 5, 2010-01-01

And it goes like that all the way for everyone. When my user wants to get information what the client has program sums it up for him doing + on Buy and - on Sell making sure the numbers match (on public market there is no stock numbers, on private there is). To this day it's not possible in my program to be able to sell anything partially. For example i can't sell 30 stocks if i didn't bought it before. So even thou customer may have 40 stocks, i can only sell 40 but not 30 since numbers doesn't match.

I was asked today if we can change it (they changed their original request), and I wanted to get some advice if it's really the proper way to do so:

If customer had 100 stocks then tried to sell only 30 with numbers AB1030 - AB1059 I would insert something like this in my table:

ID, TransactionType, Quantity, Numbers From, Numbers Too, Client ID
4, Split-, 100, AB1000, AB1099, 5
5, Split+, 30, AB1000, AB1029, 5
6, Split+, 30, AB1030, AB1059, 5
7, Split+, 40, AB1060, AB1099, 5
8, Sell, 30, AB1030, AB1059, 5

I could even not show this Split to user to not confuse him/her too much. Just as an addition I don't keep real time data, I keep only history in terms that I always have to recalculate everything if i want to get current information. I implemented it this way because they often use old data in terms checking current state of customer on older dates etc.

What would be your suggestion? Is this approach good one or should I totally rewrite it. Keep in mind that the system is already live so i would have to somehow migrate it over. And also it shouldn't be a total rework as i won't get payed for it most likely so don't have 6 months to fix it.

Answer 1

Can we see a little code what you have in your Business logic and what kind restrictions you have on buying/selling stock? I guess you have to rethink your way of saying STOP to you costumers when selling different amounts. If your business need to have a restriction on selling things you haven't bought, well maybe you can't sell it (yet)? Or you can sell it only if specific rules apply.

Answer 2

It sounds to me like you should factor out the current validation logic with something that is more flexible. That is, instead of requiring the numbers to match up it might be possible to only require that the balance is 0 or positive (i.e. so you cannot sell what you do not own). To implement this I'd refactor your data storage so that you can check the owned amount as a single value rather than matching it against past transactions.

This could easily be implemented with a single query along these lines:

public void SellStock( int clientID, string stockSymbol, int quantityToSell )
{
     using( var scope = new TransactionScope() )
     {
          // pseudo-sql for reducing client portfolio by quantity
          update ClientStockPortfolio 
          set Quantity = Quantity - quantityToSell 
          where ID = clientID 
          and StockSymbol = stockSymbol
          and Quantity >= quantityToSell 

          // log transaction history and update other tables as needed
     }
}