Results 1 to 7 of 7

Thread: best solution??

  1. #1
    Join Date
    Feb 2003

    best solution??

    I am hoping to prepare a list of the most feasable responses to the
    following situation. If anyone can please add suggestions that I have
    not already listed it would be greatly appreciated.

    So the situation is:

    - A database that handles customers and customer invoices.
    - The invoice along with other data, includes the customers address.
    - customer_1 changes their address, and the new address is updated
    in the database.
    - customer_1 wants a copy of an invoice that just so happens to be
    from a date prior to when that customer moved.
    - the invoice MUST print the customers old address, not the new one,
    because invoice data must not change from when the customer first
    recieved the invoice.
    - the current system has no way of knowing customer_1 has changed
    thier address, because the customer table only holds the customers
    current address.

    I have to rectify this situation, I have compliled the following
    list of ways correcting for this instance. I was hoping on top of
    what I listed here, that people would be able to offer their advise/

    1) The simpilest way to make sure this does not happen, is to
    have a hard copy of the invoice. (maybe even microfishe)

    2) re-store a back-up of the database and print.

    3) adjust the data model, so their is a customer_address_history
    table, that notes customer address changes.

    4) make a customer_invoice_detailed table that hold specific information
    relating to the invoice (include customer address).

    5) ??

    6) ??


  2. #2
    Join Date
    Jan 2003
    Duncan BC Canada
    Your list looks complete to me. Item 4 seems to indicate that you might be adding a table, but the customer_invoice table should already be there (along with a customer_invoice_line table for each line of the invoice).

    I like item 4 the best, by the way.

  3. #3
    Join Date
    Feb 2003
    San Antonio, TX

    Re Option 4

    Your option 4 ["make a customer_invoice_detailed table that hold specific information relating to the invoice (include customer address)."] has a potential for duplicating a lot of information unnecessarily. Why not add a table that contains the Customer History with a Valid_As_Of_Date field. This would allow you to snag the Customer's address based upon the invoice date and would still keep the data-redundancy to a minimum.

    After all, if you have some fairly active customers and 90% of them don't move very much and 25% virtually never move, you will be needlessly duplicating the address information for 25% of your customers for _every_ invoice and 90% of your customers for most of their invoices. Do the math based upon your situation and you may find that you would be wasting a rather large chunk of data storage.
    Ralph D. Wilson II

    "Any sufficiently advanced technology is indistinguishable from magic." A.C. Clark

  4. #4
    Join Date
    Jan 2003
    Duncan BC Canada
    Add 'and time' to that (so as to read "based upon the invoice date and time") and it should work. This is so that reprinted invoices from the date of an address change come up with the correct address.

  5. #5
    Join Date
    Nov 2002

    3 and 4

    1 is not bad but very costly
    2 is a horrible idea, but clever if you need to pitch all angles to management.
    3 is good because you are still normalized, but you need to have a "date moved" field. But you need to add logic to your "print historic invoice" feature that will show the "Top 1 DateMoved < InvoiceDate" type logic.
    4 is good because there is no logic needed to print a full historic invoice.

    Good job, you have thought it out well.

  6. #6
    Join Date
    Apr 2003
    Nanjing, China
    I'm new here, and just read the thread today.

    My solution:

    Address(AddressID PK, Address);
    Customers(CustomerID PK, AddressID FK, Phone, ...);
    Invoices(InvoiceID PK, CustomerID FK, AddressID FK, Money, ...);

    It looks quite simple.

  7. #7
    Join Date
    Mar 2003
    Bucharest, Romania
    I would construct my tables like this:

    Customer(Customer ID,Name ....)
    Invoice_lines(CustomerID,InvNo,ProductId,Quantity, ....)

    When you want to print Customer address, you should "look" with an sql-query, in the table CustomerAdress table, based on CustomerId and Invoice date. If you can not do that query tell us what type of database do you intend to use and I'll post the query to you.


    Good Luck

Posting Permissions

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