Database design: same products in inventory but with different cost

Question

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.

Answer 1

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.

Answer 2

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.

Update:

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.