Why use "date from" and "date to" over last rec to find current record

[nelsonm]

hi all,

in studying database design and replies to my posts, it appears that most designs use date_from and date_to fields to determine whether a record is current of archive.

My database design accommodates customer and address record archiving. Meaning that work orders point to customer/address records that where current at the time the work order was created but may not be current now, due to some customer data change, thus insuring archival integrity between the work orders and customer/addresses they are attached to. However, i rely on the last customer_address table record to be the current customer record as apposed to using date_from and date_to fields to determine which customer_address table record is the current record.

To maintain workorder/customer archival integrity, a typical schema has three tables to handle the many to many relationship between the customer and address tables and provide for archival integrity:

1. the customer table
- customer id
- first name
- last name
- phone
- email address

2. the address table
- address id
- street
- city
- state id
- zipcode

3. the customer_address table
- customer id
- address id
- date_from
- date_to
- address type code


My questions are:

1. What is the advantage of using the date_from and date_to concept over relying on the last record in the customer_address table to determine a current customer & address table records?
2. Why does the customer_orders table customer_id point to the customers table as apposed to the customer_address table?
3. Wouldn't the customer_orders table customer_id pointing to the customer_address table accomplish the same goal of the customer_orders record pointing to the current customer_address record at the time the customer order was created?

Your advise is appreciated.

[rocknbil]

Seems like the hard way to do it. Why not eliminate the third table and do something like this?


2. the address table
- address id
- street
- city
- state id
- zipcode
- is_current

... where is_current is a boolean true/false or tinyint 1/0. Then there's no duplication of data (same address info in tables 2 and 3) and in your forms allows the customers (or administrators) to choose which is the current address.

over relying on the last record in the customer_address table

Because the last record, or even the one with the highest unique identifying number, may not **be** the "current address." This is also the reasoning for the above idea, any time you rely on "some condition(s)" to determine an outcome, there's always the possibility you can't forsee all possible conditions that may arise - or they may change. You could even use the is_current as an array value you'd populate:

$addresses = array(
1 => 'current',
2 => 'home',
3 => 'business',
4 => alternate address
);

[nelsonm]

thanks, i will look it over.