I'm creating a small personal application regarding my trade of shares of numerous companies.

Those things could be selling shares of the company or purchasing. Therefore, the particulars to become saved in the two cases could be:

  1. Quantity of Shares
  2. Average Cost

Will it be easier to use separate tables for "buy" and "sell" or simply play one table for "trade" and a area that demarcates "buy" from "sell"?

Certainly the second situation Body table, simple one area (boolean) determining be it selling or purchasing. You need to define tables by organizations, not by actions adopted them.

This really is really a difficult one. The table you are speaking about is essentially a trade table, detailing all of your buys and sells.

For the reason that sense, what you know already it might seem sensible to possess both buys and sells in one table.

However, in lots of areas, there's extra information for any sell order. That bit of details are which buy to offset it against (for capital gains or profit reasons). Although this is not essential inside a strict first-bought, first-offered (FBFS) atmosphere, that's in no way the only real possibility.

For instance, under Australian law, you are able to really offset a purchase against your newest purchase, as lengthy as you've the explanation written lower in obvious language before-hands. Despite the fact that my opportunity follow FBFS, I'm permitted to get bonus issues or supplemental shares that we may then sell immediately. They are offset against the newest shares bought, not ones I have held for X period of time (this really is frequently handy to minimise taxes due).

Should you consume a strict FBFS, then you do not need that extra information as well as your trades are shaped. Even where they are not, I have implemented it in a single table using the extra information, useless for buy orders obviously. That appeared the simplest approach to take.

You could get it done as two asymmetrical tables but which makes queries a little more problematic because you frequently have to pull data from both tables. Make an effort to to stay having a single table using the extra information as needed.

I'd also never keep average cost. I favor the amount, the cost per share and also the brokerage costs. Almost every other figure could be calculated from individuals three, for instance:

AvgPrice = (Brokerage + SharePrice * ShareQuant) / ShareQuant

but it is sometimes impossible to operate backwards from only the average cost, since you do not know exactly what the brokerage was.

And That I wouldn't possess a boolean for buy/sell, it is simply as simple to use negative amounts for that sell orders also it makes balance-sheet-type information a great deal simpler because you just sum values regardless of an order type rather than requiring to negate a number of them based on that order type.

Update: If, while you appear to point, you are only likely to store aggregate information for every company, I'd go for an additional:

    CompanyId primary key
    CompanyCode indexed

then you definitely update the person posts based on maybe it's a purchase or sell. You do not need another row for that buy/sell stuff. When the organization is first added, both amounts and costs are going to .

Your entity has become the organization which means this will work better. One factor you might want to consider would be to keep aggregate values of shares bought and offered as opposed to the average purchase and sell prices. Which will simplify your update information and you will still easily obtain the earnings by dividing the aggregate through the quantity.

So, the next table:

    CompanyId primary key
    CompanyCode indexed
  • When adding a business, set all quanities and values to ,
  • When purchasing M shares at N dollars each, add M to CompanyBuyQuant and N * M to CompanyBuyValue.
  • When selling M shares at N dollars each, add M to CompanySellQuant and N * M to CompanySellValue.
  • Get average buy cost as CompanyBuyValue / CompanyBuyQuant.
  • Get average sell cost as CompanySellValue / CompanySellQuant.