Unanswered: Another DB design question . . .
How do you deal with the following issue when conceptualizing a database?
Suppose you are going to track transaction on accounts.
Accounts have an account number and an account description.
How does one handle, when the account description changes?
You may have 100 transactions under an old description, and 100 under the new description.
Assuming you are storing the multiple descriptions in different records in your account lookup table, or assuming that your transaction table preserves the description used at the time of the record creation . . . when you write a report to list the current balance on all accounts, that one account is going to come-up twice on the report, both lines with the same account number, but each line with its own description and sum of transactions associated with the account description. Although it is technically correct, users are not going to want to see multiple lines for one account.
For every field that is going to potentially change, do you have to write all your report queries with MAX functions all over the place in order to return single lines of data for each account? That will get you a single line on a report, but it distorts the historic value of the data (not to mention, what seems like, inefficient SELECT statements).
Or, do you only store one value and when the user changes something like a description, it changes for all records for all history (losing actual historic information)?
Is this just a conundrum that has no one solution . . . that it is up to the database designer, working with the client, to understand all of the pros and cons of each solution and settle on the best fit for that particular application?
Thanks ahead of time for the insights.
Maverick Software Design
(847) 864-3600 x2