>Hi All,
>
>Can someone help me with the CORRECT design for "date driven" data?
>
>Example:
>
>Billing Code: Multiple codes per customer. Indicates who the Inmate is being housed for.
>
>Inmates: 1 record per inmate/person, like your bank account at the bank.
>
>Inmates are housed for entities (customers), such as Mecklenburg County, Catawba County, NC Dept. of Corrections, Immigration & Naturalization Service, etc.
>
>Inmate Joe Schmo can be housed for Mecklenburg County today and go to court and become sentenced in which case he becomes a Dept. of Corrections inmate, etc. Joe's billing code was MECK and then changed to DOC.
>
>These customers are billed/invoiced, usually once per month (but each customer could have a different billing cycle), $20.00 per day (variable), for each day any inmate had a billing code applicable to that customer (for each day the inmate was housed for that customer).
>
>Currently my app ONLY contains "current billing code" per Inmate. The guid of the current billing code is stored in the Inmates record. In order to accomplish this task, I must make the billing code "date driven" and store the history of all billing codes per Inmate.
>
>Once the user selects a timeframe and a customer, I need to produce a report listing each Inmate and the number of days billed (number of days the inmate was housed for this customer, for this timeframe). ONLY Inmates where the number of days being billed for is > 0, would be included.
>
>I am unsure if the new "Billing Code History" table should have both a begin and end date or just an end date. Although I have tacked this issue in years past, I am unsure of the PROPER design.
>
>Thanks,
>John

John,
A US resident would probably understand better what you mean. As I take it it looks like:

Name: John Doe

Time      Which city is he at now (cannot be at 2 different places at a time):
--------  --------------------------------------------------------------------
1/1/2000  NewYork
3/1/2000  Seattle
6/1/2000  Dallas
...

right? If so my suggestion is not to store start-end but just start dates. Dealing with vertical data is easier then controlling horizontal data IMHO. ie: 3/1/2000 as end date for 1st row and start date for 2nd row is redundancy. If you ever update one you need to be sure you did the other too etc.
Cetin