Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think I would keep account information (account number, owner, interest rate, description, etc) separate from the transaction information. As such, there would be a single description for the account at any one given point in time. The description of the account does not have much to do with whether the transaction was a deposit or a withdrawal.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You are basically saying, "throw away any previous description information and, from now on, only use the latest description."

    That's okay to say, but we all have to understand that data is being thrown-away.

    A description is probably a trivial example. I am sure there is account information that is more important to maintain, and for which we do not want to throw-away prior information.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It comes down to what is the requirement for the data. You could, if necessary, have a log table that saves the old values, of course.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •