Realtional Database / Field Updating Question

[BeachDave]

The application I've designed is a contract system for a travel company. There are 6 tables in the database intrer-related by various IDs.

The problem is that all contracts get updated whenever I update anything in any of the tables.

For example, if the tax rate on a property was 5% last year, and then changes to 6% this year, when I make that update all new contract work great, but the old contracts reflect this change and don 't match what the customers were sent.

So... is there anyway in which to stop old records from calculating. The way I have it now, the calcs can't be stored because they are based on related fields. I know I can use lookup fields but that just seems counter intuative in a relational database - but maybe not.

Any help would be appreciated. Thanks.

[comment]

Tax rate is probably the best example of when to use lookup (or auto-enter).

Another option, a bit more "relational", would be to have a table of rates and their effective dates - then you can relate to the appropriate rate, based on the relevant date. But I don't think this is an appropriate case for such scheme.

[BeachDave]

I just want to make sure if I understand what you said ...

I should create a new field in the first table (tax rate?) that is simply a lookup of the tax rate in the property table? That way my calcs can reference local fields and be stored?

[comment]

I haven't seen your structure, so I don't know where is what, and which is your first table. Is it possible for 2 properties to have diferent tax rates at the same time?

[BeachDave]

each record in the property table is a assigned a tax rate - they can be the same and many will be the same.

each record in the reservation table has a property id that relates it to the property table.

i was going to add a new field in the reservation table (Tax rate) and use it to look up the value from the property table.

thanks

[comment]

That sounds about right - if the reservation table is the one with the contracts you mentioned earlier. You haven't anwered my question: does the current tax rate apply to ALL properties, or are there properties with their own rates?

[BeachDave]

Sorry. No there is not a global tax rate. Each property has its own tax rate and many other attributes that I need to use as part of calculations in the reservations table.

[comment]

OK. This is not much different from a standard invoicing solution. You lookup or auto-enter items that you want to freeze in time (such as the price of a product, or the tax rate in your case), and use related values for other items.

[BeachDave]

Thanks!