Current and archive table identifier scheme

Question

I have a table that i use for current semesters (currentSemester) with an id that is an identity. I have a table I archive to called pastSemester with an id that is an identity and a legacyID column that carries the key to currentSemester when I archive.

The problem is that I have to report on both current and past records simultaneously and I've done that with a view Unioning the two tables. However, the currentSemester table now has some duplicate values as pastSemester - its identity was reset somewhere.

Am I better going with a GUID column on both tables, or should I stick with the identity field?

Answer 1

I would question the need for the identity column on the archive table. I would have suggested you just use the primary key (ID) from the current semester table as the primary key in the archive table and remove the identity property from archive table.