Airline Reservation System: Financial Accounting

[Opcode]

I've described my class project in previous posts. Briefly, I chose to create a fictional airline. I have to include a way of relating financial info. I want to take values from various tables, perform calculations on them and use those values in other tables. In particular, I want to use the fields "BaseFare," in tblFlights with "TaxRate" and "AirportFee" in tblAirports to calculate "GrossFare" in tblFlights. I also want to use this information to generate invoices (tickets?) for customers.

I'm not sure that I do the required calculations inside tables, or if I should use queries or reports? I have a tblInvoice for invoices, but I'm not sure if I should have the table, or what I should put in it. For now, it just has InvoiceNum (Autonumber field, Primary key) and CustomerID (Foreign Key from tblCustomer). I need a way of tracking how much customers owe and how much they have paid, but I'm not sure how I should do that. I have the feeling I should not use tblInvoice to store financial transactions. Maybe I should call it something else?

I would welcome suggestions on good accounting and programming practices.

 

[CJ_London]

This link may be of help to structure your data

http://www.databaseanswers.org/data_models/airline_reservations/index.htm

Good accounting practice tends to go against normal db structure slightly in that the result of an invoice calculation is usually stored rather than calculated on the fly. This is because in accounting, once a record is 'committed' it cannot be 'undone'. Any corrections would be done with a contra entry (e.g. a credit note). A calculation for a sales value for example would be (units*price)+ (units*price*SalesTax/VAT Rate). If the SalesTax/VAT rate or price changes, this would affect the value of the invoice when next calculated which would be extremely confusing.

As a minimum I would store against each invoice line
Units,Price,SalesTaxRate plus ideally LineValue (units*price) and TaxValue (units*price*SalesTax/VAT Rate) - reason, tax rates can vary from one product/service to another

And against the invoice header I would store InvoiceValue - sum(LineValue) and InvoiceTax - sum(TaxValue).