Storing revisions in SQL Server (simply much like TFS)

Question

So, what I've got so far:

Table: ChangeSets

Columns: Id | Comment | Committer

Table: Customers

Columns: Id | Name

Table: CustomerChangeSets

Columns: Id | CustomerId (FK) | ChangeSetId (FK)

So now I can have multiple customer changes in one changeset (again very much like TFS). Every changeset in a customer object now results in a new customer object being created (and a new Id assigned due to an identity column).

My problem now is, that I don't know how to represent the mapping from and to my customers. I've got several tables that reference to Customers.Id, but that'd mean I would have a reference to an old version of a customer (the initial one, without any changes).

What's the best way?

• Don't create a new customer on each checkin? But how would I get my required logic then?

• Update all the foreign keys to my new Customer.Id? That'd take quite a long time.

Answer 1

Obviously when you check in a changeset, you know who the customer is (or was) according to what you describe about your process. Even TFS requires something to be the same, so that it can maintain history.

In any case, in multiple versions of the same object, what is constant?

Is the "Name" constant? (Probably not.) If the Customers table were that simple I do not see what you are tracking other than a name change. But likely the table is much more complex.

But as long as you know this is the same Customer, you should carry a constant value in the new Customer rows. Something like:

 Table: Customers
 Columns: Id | CustomerNumber| Name

That way "Id" is just a row surrogate key, but CustomerNumber would be assigned once and would be a datum included in the versions of the data you are tracking.

I suppose that the CustomerNumber could be the first Id assigned to this Customer, but I would suggest an independent numbering scheme, perhaps check-digited for a degree of protection from entry errors, should that ever be needed publicly.

Answer 2

I would do this:

Table: Customers

Columns: CustomerId

Table: ChangeSets

Columns: ChangeSetId | Comment | Committer

Table: CustomerVersions

Columns: CustomerId | ChangeSetId | AllOtherCustomerData

We typically need two kinds of FK references, both a more permanent one to Customers (this is the employee's laptop/iPhone etc) and a more volatile one to CustomerVersions (this is the employee's current position, location, etc.)