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
price_net. And then optionally stuff like comments, discounts, etc, etc.
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.