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: enter image description here Could there be any issue with this db design? Please suggest a better approach.

enter image description here

  • 2
    (20,2)? I knew drugs were expensive, but that allows more than a trillion dollars/Euros/dinars/etc. – Rick James Aug 14 '16 at 18:35
  • 1
    "Could there be any issue"? Of course. You will find out after you start populating and querying the db. (Nothing is obvious to me at the moment.) – Rick James Aug 14 '16 at 18:37

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.

|improve this answer|||||
  • thanks for your insight. What I do not understand from your answer (probably communication gap) is 'All inventory movement should be in one table'. Please can you show me the sample. I would post my latest design. – Mawia HL Nov 18 '16 at 8:02
  • @MawiaHL - What I mean is that you should have a table called inventory_movement or something similar. In this table you will have both shipping/sales and receiving transactions. The quantity in this table will be positive or negative depending on the direction of the inventory movement. Pick a convention that makes sense to you. Often receipts are positive and sales are negative. This way when you sum over transactions grouped by SKU (product) you get the stock on hand as a positive number. – Joel Brown Nov 18 '16 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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