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.