homepage Welcome to WebmasterWorld Guest from 23.22.29.137
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4421424
 5:58 pm on Feb 24, 2012 (gmt 0)

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




msg:4422312
 4:41 pm on Feb 27, 2012 (gmt 0)

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




msg:4422321
 5:08 pm on Feb 27, 2012 (gmt 0)

thanks, i will look it over.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved