Hi Adam,
Ask 100 people and you'll get 100 different answers! And while the theory of relational database design MIGHT be a good place to start, there are also practical reasons why you might end up doing things differently.
To give a brief response solely to your diagram, I would make the following comments (just my view, not saying it's right or wrong!)
1. I would remove 'invoice' table and have 'invoice_header' table (with fields for invoice number and invoice date) and 'invoice_line' table (with fields for qty). Curently, you are repeating the invoice number and invoice date - a 10 line invoice will have the invoice number and the date repeating 10 times
2. Storing Net price is redundant -it is simply the result of the calculation of invoice_line.qty*products.unit_price
3. Obviously, you'll need your joins (customer to invoice_header, invoice_header to invoice_line, invoice_line to products)
But now the fun starts. Consider these scenarios:
1. The price of the product changes - you probably don't want the new price reflected in the invoice. So you might deliberately choose to copy the unit_price from the 'products' table and then hold it on the invoice_line table (introducing data redundancy).
2. The same goes for tax_rate - that could change as well.
The theorists would probably have you create new tables for product price changes and tax rate changes with 'from' and 'to dates and relate it back to the invoice date. The pragmatists would probably suggest you initially look-up the 'products' table to get the unit_price and the tax_rate, but then actually hold unit_price and tax_rate on the 'invoice_line' table.
3. Do you need to know the value of the invoice? The theorists might have you perform invoice_line.qty * products.unit_price and then sum up the result for all of the lines in the invoice. The pragmatists might say that it's something you frequently need to know and it takes too long to perform the calculation every time. So, for the sake of a quick response time for the user, it's okay to deliberately introduce data redundancy and have a field called invoice_header.invoicetotal where the calculation is performed once and then stored.
These are just a couple of simple issues you need to consider when designing your application.
Even more important, you might have to consider whether MVD does it easily (with no script) one way, but doing it another way might involve significant complexity and a lot of script writing. I know the software SHOULDN'T determine the approach you take, but it's a fact of life!
Not sure if any of that helps and, as I said, just my view!
Derek.