Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    1

    Basic Order Proceesing Design Question

    I am converting a flat database to a MSAccess relational DBMS. I have a customer table, order table, and a orderdetails table. I am preparing to create the product and payment tables. I can enter an order and generate an invoice, but what do you do when a customer calls back and changes the order? I can't find the best practice design concepts for managing Order Refunds and Cancellations. The following are my concerns:

    Customer cancels order:
    • Do you delete this order record as if nothing happened, or do you mark this record so that you can go back and see that this customer placed and then cancelled this order?
    • Do you send out a new invoice documenting the cancellation?
    • What do you do with a cancelled order in the database?
    • How do you tie this in with the inventory and payment tables?


    Customers cancels an orderdetail:
    • Do you delete this orderdetail record as if nothing happened, or do you mark it as a cancelled item?
    • On a revised invoice, how would you show this?
    • What do you do with a cancelled orderdetail in the database?
    • How do you tie this in with the inventory and payment tables?


    Refunds:
    • I'm assuming that a refund simply goes in as a negative payment in the payment table for that order.


    In summary: From an overall design perspective, how do you account for cancelled orders & orderdetails, and refunds? What's the best practice?

    Thank you for your help with this.

  2. #2
    Join Date
    Dec 2001
    Location
    Delhi/Bangalore, India
    Posts
    74

    Re: Basic Order Proceesing Design Question

    Originally posted by Petr
    Customer cancels order:
    • Do you delete this order record as if nothing happened, or do you mark this record so that you can go back and see that this customer placed and then cancelled this order?
    • Do you send out a new invoice documenting the cancellation?
    • What do you do with a cancelled order in the database?
    • How do you tie this in with the inventory and payment tables?

    • Better not to delete it; because you never know when you/your boss/a court order will need it. Either have field like "CANCELLED/REFUND" and set it to TRUE, or move this record to a new table called CANCELLED_ORDERS.
    • Should do it.
    • The cancelled order can be used later on for various purposes...including calculating total number of orders for a product (as counting in the popularity of the product...here you will also count cancelled orders). Or these might be required in some company audits.
    • The inventory table will have to be updated, so as to restore the numbers of products. Payment table -- add the amount as separate record as incoming payment.


    Customers cancels an orderdetail:
    • Do you delete this orderdetail record as if nothing happened, or do you mark it as a cancelled item?
    • On a revised invoice, how would you show this?
    • What do you do with a cancelled orderdetail in the database?
    • How do you tie this in with the inventory and payment tables?
    Same answers as for the ORDERS table.

    Refunds:
    • I'm assuming that a refund simply goes in as a negative payment in the payment table for that order.
    That should do it fine.

Posting Permissions

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