SQL, two tables, one shared primary key

Question

I want to have two tables so I can move old records into a second table so I keep the main table small but still want to be able to link to both tables using the one int Primary Key.

eg: (I have simplified this example, from what is in my real tables) I am keeping trace of sessions and want to move expired sessions into an Expired_Sessions table but I am also linking sessions to log tables like a Login_Attempts table and I don't want to break the links to those tables.

Sessions
--------------------
PKey       | int
Session_ID | varchar


Expired_Sessions
--------------------
PKey       | int
Session_ID | varchar

Login_Attempts
--------------------
Session_FKey | int
Count        | int

I did think of two ways to do this that would work but I thought there should be a better way to do this?

One: storing a primary key counter in my sys_constants table and incrementing it every time I add a record. or

Two: adding another table that links the Sessions table primary key and the Expired sessions primary key to a third primary key and then using the third primary key to link to my log tables.

Answer 1

Have you thought about using table partitioning? I don't know enough about you requirements but it might be worth investigating.

When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections. This paper focuses on horizontal partitioning, in which large groups of rows will be stored in multiple separate partitions. The definition of the partitioned set is customized, defined, and managed by your needs. Microsoft SQL Server 2005 allows you to partition your tables based on specific data usage patterns using defined ranges or lists. SQL Server 2005 also offers numerous options for the long-term management of partitioned tables and indexes by the addition of features designed around the new table and index structure.

http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx

Answer 2

Why don't you just keep separate primary keys for each table, but add another column that serves as a unique identifier for the sessions, whichever table it is in? I see you have the Session_ID... if you index it, you can even use that for lookups.