The entire structure depends on your data, what you're keeping, etc.

The way we have ours designed is by using versioning. We keep a "main" table that is narrow and contains information that does not change. Then a versioning table that is connected via Foreign Key and has the changable columns.

Using AdventureWorks Employee table, here's how it would break down:

CREATE TABLE [HumanResources].[Employee](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[NationalIDNumber] [nvarchar](15) NOT NULL,

[ContactID] [int] NOT NULL

CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

(

[EmployeeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [HumanResources].[EmployeeVersion](

[EmployeeVersionID] [int] IDENTITY(1,1) NOT NULL,

[EmployeeID] INT NOT NULL,

[LoginID] [nvarchar](256) NOT NULL,

[ManagerID] [int] NULL,

[Title] [nvarchar](50) NOT NULL,

[BirthDate] [datetime] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [datetime] NOT NULL,

[SalariedFlag] [dbo].[Flag] NOT NULL,

[VacationHours] [smallint] NOT NULL,

[SickLeaveHours] [smallint] NOT NULL,

[CurrentFlag] [dbo].[Flag] NOT NULL,

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[EffectiveDate] [datetime] NOT NULL,

CONSTRAINT [PK_Employee_EmployeeVersionID] PRIMARY KEY CLUSTERED

(

[EmployeeVersionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

The EffectiveDate in the version table gives us the date the record was altered. By using the latest EffectiveDate, one could find the current version (or last changed) record. Or one could search on EffectiveDate between a certain range to pull up older records.