Traditional Model:

tEmployees (EmployeeID int,StatusID int)

tEmployeesStatus (StatusID int,StatusDescription (varchar(20))

Proposed Model:

tEmployees (EmployeeID int,StatusDescription varchar(20))

tEmployeesStatus (StatusDescription varchar(20))

Create a UDF that validates the data being entered into the StatusDescription field:

Create FUNCTION [dbo].[fnEmployeeStatusList](@EmployeeStatus varchar(20))

RETURNS char(1)

AS

BEGIN

Declare @Exists char(1)

SELECT @Exists = [StatusDescription ] from tEmployeeStatus Where [StatusDescription ] = @EmployeeStatus

IF @Exists > '' BEGIN SET @Exists = 'Y' END

IF ISNULL(@Exists,'') = '' BEGIN SET @Exists = 'N' END

-- Return the result of the function

RETURN @Exists

END

ALTER TABLE [dbo].[tEmployees] WITH CHECK ADD CONSTRAINT [CK_tEmployees_Status]CHECK (([dbo].[fnEmployeeStatusList]([EmployeeStatus])='Y'))

GO

ALTER TABLE [dbo].[tEmployees CHECK CONSTRAINT [CK_tEmployees_Status]

GO