Entity Deletion Strategy

Question

Say you have a ServiceCall database table that records down all the service calls made to you. Each of this record contains a many to one relationship to Customer record, where it stores which customer made the Service Call.

Ok, suppose the Customer has stop doing business with you and you do not need the Customer's record in your database. No longer need the Customer's name to appear in the dropdown list when you create a new ServiceCall record.

What do you do? Do you allow the user to delete the Customer's record from the database?

Do you set a special column IsDeleted to true for that Customer's record, then make sure all dropdown list will not load all records that has IsDeleted set to true? Although this keeps the old records from breaking at innerjoins, it also prevents user from adding a new record with the same name as the old Customer, won't it?

Do you disallow deletion at all? Just allow to 'disable' it?

Any other strategies you used? I am guessing everyone have their way, I just need to see your opinions :)

Of course the above is quite simplified, usually a ServiceCall record will link to many other entity tables. All of which will face the same problem when they are required to be deleted.

Many thanks in advance to you :)

Answer 1

I prefer to set an IsDeleted flag, one of the benefits is you can still report on historical information (all teh data is still there).

As to the issue of not being able to insert another customer with the same name, this isn't a problem if you use an ID column (eg CustomerId) which is generally auto populated.

Answer 2

I agree with @Tetraneutron's answer.

Additionally, you can create a VIEW that lists only the active customers, to make it more convenient to populate drop-down lists and such.