Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '13 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 '13 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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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