Missing Data Processing

Question

Let's say I have a simple help application that logs calls made by users.

I would usually have such fields in the table related to the call, for example. CallID, Description, CustomerID, etc.

I will also have a customer table, including CustomerID, Username, Password, FullName, etc.

Now, when the user is deleted from the client table, the internal connection between the call table and the user table, to historically find out which user has registered the call, will not produce any results.

How do people usually deal with this?

• They have separate tables of clients and users.
• Just turn off your accounts so that data is still available.
• Record the customer name in the call table as a separate field.

or any other methods / suggestions?

Answer 1

As indicated in other answers, a common solution is to simply mark the client record as “deleted”, “expired” or similar using the flag.

Keep in mind that you may need to actually delete personal information (name, address, ...) for privacy reasons / privacy laws. Then you will need to clear some fields in the record, replacing them with placeholder values.

Another approach would be to exclude the client entry and have one “dummy” client entry for remote clients. Then you can reassign all client-specific entries to dummmy. This, of course, loses information, but this is the only way if all personal information should be deleted - if you save the client record, you can see which dependent data belongs to whom (the remote) client, which sometimes can be enough to guess the identity of the remote client.

You will need to decide based on requirements and rules.

Answer 2

Deleting entries is rarely a good idea. Usually you want to include the isActive flag, explicit valid dates, or something on these lines.

Answer 3

I would recommend using one of these tools for custom "soft delete" entries: http://www.ruby-toolbox.com/categories/activerecord_soft_delete.html

Or just come up with your own solution that provides the same functionality by “disconnecting” accounts or something like that.

Answer 4

Assuming that you normalized the data correctly, you should not delete the client record, since you have 1 client that makes many calls, and the pk client refers to the fk call, that is data integrity. I believe that the date in the client is the only way to go without also deleting the call. Denormalizing data is definitely not the way to go.