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.

2 Answers 2


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.

  • So, then I'd have like two identity columns, one auto-incrementing for each version and the other one constant throughout a single customer? And this constant value is going to be used as a reference then?
    – SeToY
    Sep 24, 2013 at 14:46
  • The second value would not use the IDENTITY property, since you want to have a constant value. As I mentioned, you could choose the first IDENTITY value for that customer as the constant, or you could generate some other key that is in a different number range. Up to you, but you do NEED a constant for reasonable processing time.
    – RLF
    Sep 24, 2013 at 15:58

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.