I think I'm in the boat of not agreeing with this. Although I do like bleeding edge stuff being a developer AND a DBA, this just doesn't jive. I use constants in another way.

All my Lookup tables are in one table (well, two if you count the parent table that is just there to categorize the values). The parent table ReferenceLists stores the distinct list of Lookup "Lists". The ReferenceValues stores all the values for each list. Each List has a constant and each value has a constant (optional). I use the Constant for the Lists all the time (which you'll see in the View Triggers). However, the Constants for the values are only used when needed. This strategy gives complete flexibility for adding new Lookup Lists where I can even allow end-users to create their own Lookups (in the case of a CRM application where users want to add their own User-Defined Fields and one of those Fields has specific values).

[h3]Table Scripts[/h3]

CREATE TABLE [dbo].[ReferenceLists](

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

[Name] [varchar](50) NOT NULL,

[Constant] [varchar](25) NULL,

[CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_CreatedDate] DEFAULT (getdate()),

[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_ModifiedDate] DEFAULT (getdate()),

CONSTRAINT [PK_ReferenceLists] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],

CONSTRAINT [UIX_ReferenceList_Constant] UNIQUE NONCLUSTERED ([Constant] ASC) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ReferenceValues](

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

[ListID] [int] NOT NULL,

[Name] [varchar](50) NOT NULL,

[Abbreviation] [varchar](50) NULL,

[Constant] [varchar](25) NULL,

[Description] [varchar](255) NULL,

[Obsolete] [bit] NULL CONSTRAINT [DF_ReferenceValues_Obsolete] DEFAULT ((0)),

[CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_CreatedDate] DEFAULT (getdate()),

[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_ModifiedDate] DEFAULT (getdate()),

CONSTRAINT [PK_ReferenceValues] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[trg_OnUpdateReferenceValue]

ON [dbo].[ReferenceValues]

AFTER UPDATE

AS

BEGIN

Set NoCount On

-- Update the ModifiedDate on any Rows where it wasn't specified explicitly

Update dbo.ReferenceValues Set ModifiedDate = GetDate()

From dbo.ReferenceValues R

Inner Join inserted I ON (R.ID = I.ID)

Where (I.ModifiedDate Is Null)

Set NoCount Off

END

GO

ALTER TABLE [dbo].[ReferenceValues] WITH CHECK

ADD CONSTRAINT [FK_ReferenceLists_ReferenceValues] FOREIGN KEY([ListID])

REFERENCES [dbo].[ReferenceLists] ([ID])

GO

[h3]Usage[/h3]

Now, to make this easier on developers and me, I usually wrap views around these values for specific cases (although it could all be handled in code).

Let's say we want to store Address Types in the database. So we create an Address table and put AddressTypeID on it. Next we add a Foreign Key to the ReferenceValues table's ID column. We could also specify a Check Constraint that the value must only be one where the ListID = 1 ('ADDRESS_TYPE'). In order to maintain this list of values for AddressTypes, we create a nice little view for it.

Notice how the View doesn't expose the "ListID" column because it isn't necessary. This view should be used as a Table. So, the INSTEAD OF trigger looks up the ListID auto-magically (inserting it if neccessary).

[h3]"AddressTypes" View Script[/h3]

CREATE VIEW [dbo].[AddressTypes]

WITH SCHEMABINDING

AS

Select

ID, Name, Abbreviation, Constant,

Obsolete, CreatedDate, ModifiedDate

From dbo.ReferenceValues WITH(NOLOCK)

Where ListID IN(

Select ID From dbo.ReferenceLists WITH(NOLOCK) Where Constant = 'ADDRESS_TYPE'

)

GO

CREATE TRIGGER [dbo].[trg_OnUpdateAddressType]

ON [dbo].[AddressTypes]

INSTEAD OF INSERT

AS

Set NoCount On

-- Determine the ListID

Declare @ListID Int

Select @ListID = ID From dbo.ReferenceLists Where (Constant = 'ADDRESS_TYPE')

If (@ListID Is Null)

BEGIN

Insert Into dbo.ReferenceLists (Name, Constant) Values('Address Types', 'ADDRESS_TYPE')

Set @ListID = SCOPE_IDENTITY()

END

-- Insert the Values

Insert Into dbo.ReferenceValues

(ListID, Name, Abbreviation, Constant, Obsolete,

CreatedDate, ModifiedDate)

Select @ListID, Name, Abbreviation, Constant, Obsolete,

IsNull(CreatedDate, GetDate()), IsNull(ModifiedDate, GetDate())

From inserted

Set NoCount Off

GO

You can also add other columns to the ReferenceLists table (ex. SequenceNo if you want to display values in a specific order).