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