0

So lets say we have 2 tables on SQL Server database:

CREATE TABLE VersionedData (
    Id INT NOT NULL,
    Version INT NOT NULL

    CONSTRAINT [PK_VerData]
      PRIMARY KEY (Id, Version)
)

And:

CREATE TABLE PointingTable (
    Id INT PRIMARY KEY,
    RefId INT NOT NULL

    CONSTRAINT [FK_Pnt_Ver] FOREIGN KEY (RefId)
      REFERENCES VersionedData (Id)
)

My idea of this relationship is when joining these 2 particular tables I only care about latest version of VersionedData table (meaning I will ALWAYS match with maximum version for specific ID value giving me exactly 1 row that I need).

Of course the code above causes error that I must reference both PK columns from PointingTable. I could add Version column for PointingTable - however I'd be stuck to that specific version even when newer one will be inserted for VersionedTable.

I'm struggling to figure out how to redesign this relationship, I've thought of computed column for PointingTable that would utilize scalar function for getting max version, but I think that would be overkill, there must be a better way.

  • The definition of the 1st table is invalid.. If you had tried it, it would have given an error. You cannot have 2 primary keys in a table. – ypercubeᵀᴹ Mar 21 at 13:13
  • 1
    Did you mean a 2-column primary key? – ypercubeᵀᴹ Mar 21 at 13:15
  • Sounds like a use case for temporal tables? Automatically keeps all the history in a secondary table, and only the current version of a row in the primary table. – Aaron Bertrand Mar 21 at 15:50
  • @ypercubeᵀᴹ Yes, I meant composite primary key. My bad, edited the post now. – Julius Rupšys Mar 22 at 0:07
  • @AaronBertrand, I gave temporal tables a shot and that is exactly what I need for my situation! Its very convenient that system manages versioning for me on updates. – Julius Rupšys Mar 22 at 0:10
3

The alternative is to create an additional table

CREATE TABLE CurrentVersions (
    Id INT PRIMARY KEY,
    Version INT
)

with appropriate foreign key relations to VersionedData. You can then have the foreign key FK_Pnt_Ver reference CurrentVersions instead.

You'll either need an extra JOIN to fetch the data from VersionedData (I assume it contains additional columns), or alternative store the data you need in CurrentVersions as well. In that case, you don't even need the Version column.

This situation is similar to how Stack Exchange stores posts and revisions. Their database looks very much like the public Data Explorer whose schema is documented here on Meta. The Posts table contains the current version of a post as HTML (Body) and the revisions are stored (as Markdown) in the Text column of the PostHistory table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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