Handling missing data

Question

Say I have a simple helpdesk application which logs calls made by users.

I would typically have such fields in a table relating to the call e.g. CallID, Description, CustomerID etc.

I Would also have a table of customers including CustomerID, Username, Password, FullName etc.

Now when a user is deleted from the customers table then the inner join between the calls table and the users table to find out historically which user logged a call would produce no results.

How do people usually deal with this?

• Have seperate customer and useraccount tables
• Just disable the accounts so the data is still available
• Record the customers name in the calls table as a seperate field.

or any other methods / suggestions?

Answer 1

Deleting records is rarely a good idea. Normally you'd want to include an isActive flag, explicit validity dates or something along those lines.

Answer 2

I'd recommend using one of these tools to 'soft delete' user records: http://www.ruby-toolbox.com/categories/activerecord_soft_delete.html

Or just come up with your own solution that provides the same functionality by 'disabling' the accounts or something.

Answer 3

assuming youve normalised the data correctly you shouldnt be able to delete the customer record as youll have 1customer makes many calls, with the customer pk being referenced by the call fk, this is what data integrity is all about. I reckon a effectiveto date in customer is the only way to go without deleting the call aswell. Denormalising data is definitely not the way to go.

Answer 4

As pointed out by the other answers, the common solution is to just mark the customer record as "deleted", "expired" or similar using a flag.

Be aware that you might be required to actually remove personal information (name, address, ...) because of privacy reasons / privacy laws. Then you would need to blank some fields in the record, replacing them with placeholder values.

Another approach would be to outright delete the customer record, and have one "dummy" customer record for deleted customers. Then you could reassign all the customer's dependent records to the dummmy. This of course loses information, but is the only way if all personal information must be deleted - if you keep the customer record, you can see which dependent data belonged to which (deleted) customer, which sometimes might be enough to guess the identity of the deleted customer.

You'll need to decide based on requirements and regulations.