The answer is: yes, there is a benefit to doing it. Reports on on operational database will use a lot of resources and will interfere with the performance of the operational system. Remember that database performance is subject to mechanical constraints (disk heads moving back and forth and rotational latency as we wait for the right sector to make its appearance under the head). You have two broad options for a reporting strategy:
- Replicate your database onto another server and move the reporting sprocs onto it. Reports are run off the replicated server. This is the least effort and can re-use your existing reports and stored procedures.
- Build a Data Warehouse that consolidates the data from your production systems and transforms it into a form that is much friendlier for reporting. If you have a lot of ad-hoc statistical reporting that could be done acceptably from a snapshot as of 'close of business yesterday' a data warehouse might be the better approach.
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.
Best Answer
Take a look at the way SQL Server handles this for SQL Agent job schedules. Specifically, read through the columns prefixed freq_* as these determine the frequency of execution.
You could use the same or similar table schema to store frequency information about your reports. You could even display 'next scheduled date' information quite easily if you also stored the creation date of the schedule for a particular report.
There is a good article on SQLServerCentral that dives into this and should help you develop a solution that would suit.