One way to consider a database like this, is to work on the basis of final output.
In this case, the final output is an invoice therefore roughly map out what you want it to look like on the basis of a maximum number of lines, whether quantities are involved, different delivery addresses for each customer, multiple currencies and so on. Then you can start thinking about how to minimise duplication of information.
For example, although you can store the customer's address in each invoice, logically it's best to put address details in a separate table and then link them to an invoice using a field value that's unique to each customer (- often a key field such as Account Number which then links to a related field in the invoice table). The same goes for products and minimising the need to store descriptions of items. If you give each item a code then you're able to select items in your invoice by the code rather than needing to type each one in again - though this method does need due consideration if you are going to be storing prices with product descriptions, as you don't want existing invoices to be affected when prices change. How this works, is all in the set-up as you can copy field values from one to another so it no longer is dependent on the original products list table.
Also, if you don't want to fix the number of lines in an invoice, then you'll also need to consider headers and footers in the final report as this then allows invoices to take up more than one page.
Robert
Last edited by rwniel; 25-10-2004 at 16:09.
|