I am trying to design a database for a Stock and Sale System and I have the following issue.

Suppose there is an item in stock that costs $ 100 (product cost, not the selling price), but there are only 3 pieces to sell, then the customer decides to buy another batch of the same product, but this time the lot comes to a different price, $ 76 for example.

The purchase cost is very important, because on it the percentage of profit is calculated, so if the cost is different, the selling price will be different too.

I wonder how to make the seller sell each piece to its corresponding price.

I am a novice in designing databases and I have the following tables:

PRODUCT -> Record all products, whether or not available in stock.

  • product_id (PK)

PURCHASE -> Record all purchase of new products to sell.

  • purchase_id(PK)
  • product_id(FK)

SALE -> Record each sale operation

  • sale_id (PK)

SALE_ITEM -> Records which product was sold in a sell operation.

  • sale_item_id(PK)
  • product_id(FK)

The SALE_ITEM table is related to the PRODUCT table through an attribute product_id.

So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

Or, should I force the client to sell the old lot before selling the new ones?

How you would handle this situation?

Thanks, and sorry for my english, I am not a native.


2 Answers 2


So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

I think it is better choice to have of purchase_id as FK in SALE_ITEM table in oppose to product_id, because sell is something related to PURCHASE and PRODUCT both and this option offer you this relation, you can get the corresponding product_id and information related to PRODUCT by making a join in sql query.

Or, should I force the client to sell the old lot before selling the new ones?

Don't add restrictions with your design, let them give flexibility to choose the way they want to. Even this part can be handled with sql query incase needed.

  • Don't give accounting advice - you are (it seems) not an accountant, and you are most definitely not aware of the accounting practices of the organization for which OP works. You cannot provide accurate advice on point #2 of your response without, at a minimum, having read their financial statements. Commented Sep 28, 2014 at 4:24
  • 1
    @PieterGeerkens, Thanks!, neither I am an accountant nor I have given any accounting advice.
    – vijayp
    Commented Sep 28, 2014 at 4:28
  • I interpret this ("Even this part can be handled with sql query incase needed.") as accounting advice, as suggesting that a straightforward JOIN could calculate whatever inventory policy needs to be suggested. That may not be true. Suggesting that OP could solve thi issue without a discussion with appropriate corporate officers is most definitely accounting advice, wrong, and in consequence unprofessional. I am an accountant, and thus know that OP must talk to his Controller. Commented Sep 28, 2014 at 4:31
  • Thanks @vijayp. The first answer is what I was thinking. Commented Sep 30, 2014 at 1:56

First, you must ask the Controller (or CFO) what the appropriate costing model is for inventory of this sort - LIFO, FIFO, Average, or some less common pattern. This is very important as if you use a different calculation from that published in Financial Reports of the corporation, you will be engaged in fraud of the shareholders and be in violation of Sorbanes-Oxley.

If the financial records are mailed to the shareholders, you might be in violation of RICO also.

All of the methods I listed above are appropriate in various industries, at various times - you cannot make an a priori decision on one being better than the others.

One absolute principle of GAAP is that when any change is made to the traditional accounting policies of a corporation, a number of prior years financial statements must be restated according to the new calculation in the current year's financial statements. This can only be done as authorized by the Controller and/or CFO, and possibly the corporate auditors as well.

Never, ever program an accounting calculation of any sort without verifying the technique with an appropriate corporate authority. There are many aspects of GAAP (Generally Accepted Accounting Principles) that are non-intuitive to those without an accounting background.


Note that even when inventory items have serial numbers, and thus the actual cost may be known, it is not necessarily appropriate accounting practice to use the actual cost instead of a calculated cost based on all equivalent items in inventory. That is to say: The cost of selling a unit of product XYZ is a calculated accounting value that may not be the actual purchase price of the particular unit sold, even when that is known.

I repeat: You must discuss this with your Controller and/or CFO.

  • 1
    Thanks for your answer @Pieter Geerkens, but the system I'm doing is a personal project, so I do not have anyone to consult which accounting rules to apply. The point is that I trying to figure out how to keep track of all the sales for products that have different cost in a period of time. Commented Sep 30, 2014 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.