How should I log changes to an object when the datatype of the changed property can vary?

Question

I am trying to create a log-table for storing events to a company-object, and I am afraid I might be taking the wrong route. I've arrived at the conclusion that I should log different data-types in the same column, and it doesn't feel right. I'll explain the basic use-case with 2 tables; company and user.

company
- id int
- name nvarchar
- description

user
- id int
- name nvarchar
- company int (references company.id)

Now, say I want to log different types of events on the company-object. I'd make a log-table called company_log and company_log_type and store events there. These tables would look like this:

company_log
- id int
- old_value nvarchar
- new_value nvarchar
- log_type int (references company_log_type.id)

company_log_type
- id int
- name

Examples of interesting events are

• Company-name was changed
• Company-description was changed
• A user was added to the company
• A user was removed from the company

For the two former events, I'd store the type of event, the old value and the new value. This works fine as long as I stop here. However, if I want to log the 2 former-events, I would need to be able to store different data-types in the same column. The changes would look something like this:

old_value     new_value     log_type
----------------------------------------------
Acme          PiedPiper     CompanyNameChanged
NULL          132           UserAdded
97            NULL          UserRemoved

At this point, I would need to store the textual old/new value of the company name, as well as the id of the recently added user.You might see that I'm already headed off in the wrong direction, and this is where I ask for help. I have 2 questions:

Should I just use sql_variant-datatype, or if this is considered poor design then what would be a sensible way to store these log-events?

Thanks in advance.

Answer 1

There are restrictions and difficulties with sql_variant type. See MSDN for details: SQL Variant

As you already "feel" you might also trap into problems with casting and converting lateron. For samples see 10 reasons to explicitly convert SQL Server data types.

I would try to avoid variant in this and any other case if possible.

But your basic problem is not the variant type but more your table design. Even if you had only integer values in your log tables, I would still think about changing it to some more normalization.

However after thinking about your problem I came to another approach that maybe worth a try: Why don't design your log table the same as the original table.

For example:

company_log
- id int
- name nvarchar
- description
- log_stamp datetime
- Log_type

That way you could add a new row each time the original row was changed and add the previous row into the log table. This will give you a complete history of the rows without any datatype issues.

You could also limit the logging to changes of specific columns. I think you will need triggers to realize it.

It will give you multiple log tables but if you keep them similar regarding log_type and log_stamp you could still query the entire history of any changes (of tracked tables) by UNION them.

I have found another question where this and other possible ways to solve it are explained pretty detailed: How to store historical records in a history table in SQL Server