Creating a database for pharmacy

Hey!! Greetings. I am trying to create a database for a pharmacy. While doing this, i am in a confusion now about invoice. What will be the invoice table so that a customer with a customer_id can buy different types of drugs of several quantity(with drug_id, drug_name) ? How will the cost be added up? Right now my database is in a format that a customer can buy only one drug(several quantity).

The costs are added in your code, not in the database.

thanks. What about the first question?

Well, I don’t know your exact requirements, but generally what you’d have is invoice_line_id, drug_id, drug_name, quantity, price_gross, vat, price_net. And then optionally stuff like comments, discounts, etc, etc.

drug_name and selling_price should be copied to the invoice line because if they change in the actual drug record, the invoice should not change with it, since it’s not allowed to retro-actively change invoices.

Good database design is not easy. You can get help from Industry Data Models. Look for Pharmaceuticals and look for invoices.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.