I have designed a db for Drug store which will sell medicine both wholesale and retail. I store each strip, capsule and vial of medicine in a separate fields for retail selling. Is there any better approach for this? As a programmer, I can't think of whether it is necessary to store amount paid by each customer in the order table. And is it necessary to have in and out inventory in separate table? Here is my DB:
Could there be any issue with this db design? Please suggest a better approach.
Best Answer
You might want to consider abstracting product into a SKU for the order detail. Instead of having order details show which specific pills were sold, show more generically which medicine was sold. The reason for this is that you may sell, for example, 100 pills of a certain type. Perhaps you only have 40 left of one batch and so you fill the order with those plus 60 from another batch. That would show as two order details on a sales receipt, which would be less than ideal, since the customer doesn't want or need to know this detail.
Another advantage of abstracting the product list from the actual product batches is that you can get a simple list of products without repetition. This is just good database normalization. The name of the product and the batch number of the product have different functional dependencies, so they don't belong in the same table under Third Normal Form (3NF).
You can and should have inventory movement records that record the specific stock used to fill each order.
You should store the amount actually paid with the order details. This is because the MSRP price can change over time so failing to store the price actually paid could lose information. Similarly, you may decide to sell for a promotional price, which would be lost information if you only store the regular price (or MSRP).
I recommend against having inventory ins and outs in separate tables. All inventory movements should be in one table with either two quantity columns (in/out) as per old-style accounting methods or preferably, a single quantity column with positive and negative numbers for receipts and sales, as per newer computerized accounting methods. Keeping inventory in a single table reduces your code complexity considerably.