How to delete customers?

[Shep]

This question concerns a business application which includes orders and invoices, among other things.

Invoice history must be maintained, of course.

How would one 'delete' a customer?

If you truly delete a customer, including any related records in other tables such as telephone numbers, shipping addresses, contacts and the like, invoice history would no longer be complete.

I am toying with the idea of simply marking the customer record as 'Active' (or not) with a Yes/No field if my company wants to 'delete' them, or something similar...so that if you reprint an old invoice you would still have relevant records available.

How do others handle this?

Many thanks,
Shep

 

[simongallop]

Have an active field being Yes/No. Also have another table that contains the dates that the active field for a customer is turned on / off

 

[Shep]

I appreciate the input from you both.

There are no hard and fast rules here. We have not deleted customers in the past, nor can I really envision the need to do so (except for special cases - see below).

Often, a terms customer who is not paying their bills may be placed on credit hold, but they are not deleted.

I thought about purging as well. We have so far not purged any data either, except when we switched applications about 3 years ago. Of course, existing customer data was imported into the new system along with inventory and a few other things.

What makes me even consider the issue is more insiduous. Certain users will create a customer, completely butchering the data.

For example, a representative from ABC Office Supplies comes in or calls to purchase something.

Brilliant User searches for 'abc' so that he/she can create a new order.

Brilliant User doesn't notice that they've pulled up a match and instead creates a new order with 'abc' in the customer field, which automatically creates new customer 'abc'.

Frustrated DBA must then delete all related records before deleting customer 'abc'.

This wouldn't be so bad if we could simply change the CustomerID in the Order. But when new orders are created, many related records are created, making a simple switch much more time consuming. And along with manual editing comes the strong possibility of more errors.

This is a work in progress. I may write code which allows users to assign a different customer to an order, updating all the related junk at the same time.

Job security, at the least.

Thanks again.

Note - make that insidious, not insiduous. heh heh