1) Have a separate table to hold the address data which also has a From - To date.
When they change addresses, you create a new record for the address and update the to date on the old address. Any work order etc should link to the unique id on the address & customer id.

2) Have a validfrom & validto date on the service type. When it changes in price create a new row with a validto in the future or null and with the validfrom date. Link the work item to the unique identifier of the row and not the service type code. When creating a new order, select the row where the work date is between the validfrom & validto dates.

thanks for responding...

ok, let me see if i got this straight...

Customer data
Address data is no longer included in the customer table and is now a separate table. The link between a customer and past/present address is now maintained in the customer's work order table record as - say - fields [CUSTOMERID] and [ADDRESSID]. That way, all existing/past customers work orders point to the correct address. The from and to date added to the address table allows you to find the current address.

Wouldn't you want to have a customer ID field in the address table that points all address records for a particular customer back to the customer? With out that, you'd never be able to tie/display customer records with all associated addresses or even the current address.

Work order and pricing data
I understand the idea of the from and to date. But rather then having a multiple item/price records for a particular item, wouldn't it be better to separate the service items from their pricing and instead have multiple price records with from and to date fields to indicate the current item price? A work order items table would then keep track of the [ITEMID] and [PRICEID]'s associated with the work order.

your thoughts...

I would think you'd want the customer data to remain updated in **any** case - so any references to "customer" would display the current info - pending the below conditions.

For work orders, this is fairly easy - you should always store work order/invoice info with the actual values in a table of their own, and not join those values on the products table. It may be wise to collect the customer info at that point and store them in this table too.

Take an ecommerce example. Your customer comes to the site, enters their info, purchases a widget for a price. When the order is complete, you store their email, address, and actual widget price in the completed orders table. When they change their email or address, it updates the customer table, but not this one.

For orders in progress, you may use that scheme and have an extra tinyint field containing a value representing in progress or complete. When the order is opened, you store the current widget price. Next day, you raise the price which only affects the products table, not this order.

Given that scenario, I'd still refer to the customer table for customer info - unless you have the ability to enter multiple addresses, as in, different shipping locations. Then you'd have to devise a way to assign which address is for this order - a drop down list, for example.

rocknbil

So you advocate a method that does not require a separate customer address table or a price list table that maintains archived records.

In your schema, you simply create a work order record in the work order table that links to ancillary static customer and items tables. These static tables maintain a snapshot of the customer and item/price (just the items/prices of the stuff the customer purchased) data at the time the work order is generated.

The main customer and pricelist tables do not contain archival records and are simply updated as required to reflect current customer and pricing data.

Am i correct?

It depends on what you're doing. In ecommerce it's not that common to require archives of customer ship info if it changes, but if there's any question . . .

It may be wise to collect the customer info at that point and store them in this table too.

After spending some time thinking about all that was said about archival accuracy between customer, work order and service item tables, I've come up with a design plan that's a combination of rocknbil & Dijkgraaf ideas.

I'd like your opinion on my idea...

The customer data that has the potential to change is the:
. address
. phone number
. email address
. alternate contact info

So...

Creating the customer table and address tables.

1. The customer table always contains just the customers' current:

. customerID
. addressID (the pointer to the customers' current address in the address table)
. first name
. last name
. statusID
. Notes

2. The address table stores current and past address info:

. addressID
. customerID
. street
. city
. stateID
. zipcode
. phone number
. email address
. alternate contact info

The customer table always contains and/or points to current customer data.

When a new work order record is created, the customers' customerID and addressID from the customer table is included.

The addressID stored in a customer table record should always point the the last address table record inserted for that customerID – the current address.

The addressID stored in a work order table record should always point the last address table record inserted for that customerID at the time the work order record was created – a current or past address.

If a new customer is added or an existing customer has an address change and there are no open work orders for that customer, a new address record for that customerID is inserted into the address table and the addressID in the customer table record is updated.

If an existing customer has an address change and there is an open work order for that customer, the existing address record for that customerID is updated in the address table and the addressID in the customer table record is not updated.

There would be no need to search for or calculate the current customer address because it's always pointed to by the addressID in the customer table.


Creating the items table.

The work order service items table would simply have records for that workorderID (as rocknbil had said) containing the actual values relating to all service items purchased under that work order at the time the work order was created.

1. The items table would include the actual:

. itemID
. item name
. unit price
. quantity

The price list table always contains and/or points to current item and pricing data.

When item records are created for a work order, the workorderID and the actual values from the current price list table are included in each item record for that work order.

If a new item needs to be added to an existing work order and the work order is open, a new item record for that workorderID is inserted into the items table using the actual values from the current price list table.

If an existing item needs to be updated for an existing work order and the work order is open, a item record for that workorderID is updated. The current price list table is not used.

If an existing item needs to be replaced with new item for an existing work order and the work order is open, a item record for that workorderID is updated using the actual values from the current price list table.

Closed work orders and their associated data can not be altered. I think this design insures archival integrity for open and closed work orders.

What do you all think of my design?