As metioned, this can easily be achieved by adding a few columns.
If you want historical information, you might consider something along the following lines.
Information about an Employee
CREATE TABLE [Employee] (
[EmployeeID] [int] NOT NULL ,
[Initial] [varchar] (10) ,
[Forename] [varchar] (50),
[Surname] [varchar] (50) ,
[PreferredName] [varchar] (50) ,
[DOB] [smalldatetime] NOT NULL ,
[DateJoinedCompany] [smalldatetime] NULL ,
[ContinuousServiceDate] [smalldatetime] NULL ,
[LeaveDate] [smalldatetime] NULL ,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Information about employment history.
This could hold information such as hours contracted, date started job + anything else you might need here.
CREATE TABLE [JobDetails] (
[EmployeeID] [int] NOT NULL ,
[ContractedHours] [decimal](5, 2) NULL ,
[JobCategoryID] [smallint] NULL ,
[JobDate] [smalldatetime] NULL,
CONSTRAINT [FK_JobDetails_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employee] (
[EmployeeID]
)
) ON [PRIMARY]
Information about pay.
Every time the employees rate of pay changes, you'd have a record here
CREATE TABLE [EmployeePayRates] (
[EmployeeID] [int] NOT NULL ,
[PayDate] [datetime] NOT NULL ,
[PayRate] [money] NULL,
CONSTRAINT [FK_EmployeePayRates_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employee] (
[EmployeeID]
)
) ON [PRIMARY]
So for example, if an employee is promoted you wouldn't change the Employee table - you just add records to JobDetails (new job) and EmployeePayRates (new rate of pay)
I'd recommend keeping JobDetails and EmployeePayRates in different tables, since an employee could still be within their current role but may get a pay rise.
Submit an article or tip