Historical Data Tracking in SQL — Part III

This is part three of an ongoing series. In Part I we created a table, VirtualMachine, with some rows by ReportDate. In Part II we created views for the VirtualMachine table to allow for data to be ingested as often as daily and make it easier to understand what has changed between report dates. For Part III we will go on a side quest.

Data being ingested into the VirtualMachine table is drawn from a source such as a csv extract or, with luck, an API. The extract or API has a fixed set of attributes for VirtualMachine. Let’s assume the available attributes match our table structure of ServerName, CPUCount, and MemoryInGB. If you get asked to provide a list of virtual machines associated to a given customer, it will require manual collation, assuming it is possible. Ick!

You could add a CustomerName column to the VirtualMachine table, but that would require you to populate it manually with each data ingestion. No good. You could create a Customer table, and possibly normalize further, and also create a one to many CustomerVirtualMachine table to relate a customer to a virtual machine or machines. Better, but we are not building a CRM database, and there are likely to be other entities besides customer we will want to relate to virtual machines. I argue creating a way to add tags to entities is more future proof. Also, using the structure relayed in this post, you can tag existing entity records and not just those inserted going forward. It is also, as you will see, quite easy to determine what entities are missing a given tag.

Start by creating an Tag table. As tags are likely to be manipulated by humans in the future let’s include some tracking of who/what creates and updates tags. For good measure we’ll supply a default value constraint for those fields.

create table Tag(
 EntityName varchar(100) not null,
 TagName varchar(50) not null,
 TagValue varchar(500) null,
 CreatedBy varchar(50) not null,
 CreatedDate datetime not null,
 UpdatedBy varchar(50) not null,
 UpdatedDate datetime not null
)
GO

alter table Tag
add constraint PK_Tag primary key (EntityName, TagName); 
GO

alter table Tag
add constraint DF_Tag_CreatedBy default (suser_name()) for CreatedBy
GO

alter table Tag
add constraint DF_Tag_CreatedDate default (getdate()) for CreatedDate
GO

alter table Tag
add constraint DF_Tag_UpdatedBy default (suser_name()) for UpdatedBy
GO

alter table Tag
add constraint DF_Tag_UpdatedDate default (getdate()) for UpdatedDate
GO

In parts I and II we inserted several records into the VirtualMachine table and currently have the following list of distinct server names.

1. SERVERONE.somedomain.com
2. SERVERTWO.somedomain.com
3. SERVERTHREE.somedomain.com
4. SERVERFOUR.somedomain.com
5. SERVERFIVE.somedomain.com

We can add customer tags for each server name as follows. Notice that each TagName is the same, “Customer”. Also notice the TagValue can repeat or be unique. There are many improvements possible as relates to the Tag table, managing data consistency for one, but this will serve to illustrate the value of having a tag table.

insert into Tag (EntityName, TagName, TagValue)
select 'SERVERONE.somedomain.com', 'CustomerName', 'Customer A'

insert into Tag (EntityName, TagName, TagValue)
select 'SERVERTWO.somedomain.com', 'CustomerName', 'Customer B'

insert into Tag (EntityName, TagName, TagValue)
select 'SERVERTHREE.somedomain.com', 'CustomerName', 'Customer C'

insert into Tag (EntityName, TagName, TagValue)
select 'SERVERFOUR.somedomain.com', 'CustomerName', 'Customer D'

insert into Tag (EntityName, TagName, TagValue)
select 'SERVERFIVE.somedomain.com', 'CustomerName', 'Customer D'

Now that we have some tags, let’s see what can be done with them. For starters we can output the tag as an additional attribute in a select query.

select VM.ServerName,
  T.TagValue as CustomerName
from VirtualMachine VM
  inner join Tag T
    on VM.ServerName = T.EntityName
    and T.TagName = 'CustomerName'

We can also get the current servers associated with a particular customer as of our most recent report date using the VirtualMachineCurrent view created in part II.

select VMC.ServerName
from VirtualMachineCurrent VMC
  inner join Tag T
    on VMC.ServerName = T.EntityName
    and T.TagName = 'CustomerName'
where T.TagValue = 'Customer D'

There is a whole lot more we can do with the addition of the Tag table, we’ve only scratched the surface. Let me know your ideas in the comments.