Database recording of information about changes with n-m relationship

Question

I have a (SQL Server) database where I'm tracking changes on a table; the requirement from the customer is to be able to see the "picture" of any point back in time, specifying the requested date. What I have done so far is to implement a HistoryTable, following the idea of Log trigger, with database triggers to store historical data.

Now, the requirement changed, and I need to add a many-to-many (n-m) relationship to the original table. Of course, the linked data set (n-m relationship) should be "versioned" as well, changing over time as the original table.

My first idea is to refer the n-m relation with a "fake ID" in the main table, that is: a normal (int) field, and not the primary key (which is not changing over time). In this way, I could change that ID over time, for instance:

Version 1:

Version 1
ID  RelID  Data
5   5      Version 1

Version 2
ID  RelID  Data
5   5      Version 2

Version 3
ID  RelID  Data
5   6      Version 3

In this example, the n-m relation did not change between version 1 and 2, but changed in version 3.

This is fine, but my questions are:

• Whenever the n-m relation version changes, how can I get a new RelID that is unique in the table?
• Another option: can I refer the n-m relation to both the ID and the RelID (that is unique for sure)?
• Is there any smarter solutions than mine?

Thank you in advance for any help

Answer 1

This scenario sounds like it could easily fit the data warehousing concept of a slowly changing dimension. Generally the type-2 slowly changing dimension are considered the most useful. These record a start and end date for each version of the row and the current row simply stores NULL for the end date.

Basically you would add to additional columns to your bridging table to record the start and end date of that row's version. Where the EndDate is NULL you know that is the current row version.

So your table would end up looking like:

 ID | RelID | StartDate  | EndDate
----+-------+------------+------------
 05 |   06  | 2014-10-01 | NULL
 05 |   05  | 2014-09-10 | 2014-10-01
 05 |   03  | 2014-09-10 | NULL

From this data you can tell that ID 05 was related to RelID 05 until 2014-10-01 and it then it was related to RelID 06 and it is still related to RelID 03.

Then when you need the current relationship data you can simply say

SELECT *
FROM Table
WHERE EndDate IS NULL

and if you're looking back historically you can say

SELECT *
FROM Table
WHERE @selectionDate BETWEEN StartDate AND COALESCE(EndDate,GETDATE())

Without knowing more about your data structure I can't tell if this is a good fit for the problem you're trying to solve. But usually a many-to-many relationship needs a bridging table between the two tables of actual records to create a proper relationship and the Type-2 slowly changing dimension is a tried and true method of versioning rows in a data warehouse and that same concept can be applied anywhere you need historical versioning of records.