I have a project that contains dozens of tables that contain nothing more than simple descriptors and basic reference data for keeping main tables smaller. Some examples would be "User_Type", "Shipping_Status", "Order_Status", etc. Having a lot of these types of tables can become bothersome to deal with (even when creating them from templates), because ultimately you must create some sort of object that handles the CRUDS (create, update, delete, select, etc.) unless you are using dynamic SQL in your data objects.

Here's a handy little method I've been using for dealing with lots of reference data. It may not be the absolute perfect way, but I haven't found any drawbacks yet and these simple joins don't seem to be making much of an impact on my data retrieva. Most of the results are ultimately cached in the application as well.

Here's the rReference table which will hold all our reference data:

CREATE TABLE [dbo].[rReference] ( [id] [int] IDENTITY (1, 1) NOT NULL , [date_created] [datetime] NOT NULL , [createdby_id] [bigint] NOT NULL , [ref_key] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ref_display] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ref_display_desc] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [is_active] [bit] NOT NULL , [is_deleted] [bit] NOT NULL , [date_updated] [datetime] NOT NULL , [updatedby_id] [bigint] NOT NULL , [date_deleted] [datetime] NOT NULL , [deletedby_id] [bigint] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[rReference] WITH NOCHECK ADD CONSTRAINT [PK_rReference] PRIMARY KEY CLUSTERED ( [id] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO ALTER TABLE [dbo].[rReference] ADD CONSTRAINT [DF_rReference_date_created] DEFAULT (GETDATE()) FOR [date_created], CONSTRAINT [DF_rReference_createdby_id] DEFAULT (1) FOR [createdby_id], CONSTRAINT [DF_rReference_is_active] DEFAULT (1) FOR [is_active], CONSTRAINT [DF_rReference_is_deleted] DEFAULT (0) FOR [is_deleted], CONSTRAINT [DF_rReference_date_updated] DEFAULT (1 / 1 / 1900) FOR [date_updated], CONSTRAINT [DF_rReference_updatedby_id] DEFAULT (0) FOR [updatedby_id], CONSTRAINT [DF_rReference_date_deleted] DEFAULT (1 / 1 / 1900) FOR [date_deleted], CONSTRAINT [DF_rReference_deletedby_id] DEFAULT (0) FOR [deletedby_id] GO

Basically what we are doing here is assigning a key (Column: ref_key) which will describe the reference data. The table's primary key (id) will be the actual value to join on. The value that will be presented after the join, and ultimately throughout the application's UI will be the "ref_display" column. So an example populated reference data row would look like this: "'1', '5/25/2005', '5', 'User Type', 'Registered Member', 'This is just a standard registered member of the site', '1', '0', '1/1/1900', '0', '1/1/1900', '0'"

An example join on this reference data table would look like:

SELECT tUser.Name_LastFirst, tUser.status_id, A.ref_display AS status_name, tUser.type_id, B.ref_display AS type_name FROM tUser INNER JOIN rReference A ON tUser.status_id = A.id INNER JOIN rReference B ON tUser.type_id = B.id WHERE tUser.id = '1' GO

Next, you'd want to create some simple procedures for dealing with the rReference table. First, you'd need to display the various types of reference data that the user can manage. Well, that is very easy. All you'd need to do here is perform a distinct select on the ref_key which describes the reference data to store, and then pull your reference data rows by the associated key.

A couple of example stored procedures for managine the reference data:

-- HERE'S WHERE YOU DECIDE WHAT IS AVAILABLE TO LIST CREATE PROCEDURE [rReference_sel_ListKeys] AS BEGIN SELECT DISTINCT(ref_key) FROM rReference WHERE rReference.is_deleted = 0 GO -- AND HERE'S YOUR MAIN LIST PROCEDURE CREATE PROCEDURE [rReference_sel_ListByKey] ( @ref_key VARCHAR(100), @full BIT, @is_active BIT ) AS BEGIN -- DETERMINE WHETHER TO PULL ENTIRE ROW IF @full > 0 BEGIN -- DETERMINE WHETHER TO PULL ACTIVE ROWS ONLY, OR ALL IF @is_active > 0 BEGIN SELECT rReference.id, rReference.date_created, rReference.createdby_id, A.name_full AS createdby_name, rReference.ref_key, rReference.ref_display, rReference.ref_display_desc, rReference.is_active, rReference.is_deleted, rReference.date_updated, rReference.updatedby_id, B.name_full AS updatedby_name, rReference.date_deleted, rReference.deletedby_id, C.name_full AS deletedby_name FROM rReference INNER JOIN tUser A ON rReference.createdby_id = A.id LEFT OUTER JOIN tUser B ON rReference.createdby_id = B.id LEFT OUTER JOIN tUser C ON rReference.createdby_id = C.id WHERE rReference.ref_key = @ref_key AND rReference.is_active = 1 AND rReference.is_deleted = 0 ORDER BY rReference.ref_display ASC END ELSE BEGIN SELECT rReference.id, rReference.date_created, rReference.createdby_id, A.name_full AS createdby_name, rReference.ref_key, rReference.ref_display, rReference.ref_display_desc, rReference.is_active, rReference.is_deleted, rReference.date_updated, rReference.updatedby_id, B.name_full AS updatedby_name, rReference.date_deleted, rReference.deletedby_id, C.name_full AS deletedby_name FROM rReference INNER JOIN tUser A ON rReference.createdby_id = A.id LEFT OUTER JOIN tUser B ON rReference.createdby_id = B.id LEFT OUTER JOIN tUser C ON rReference.createdby_id = C.id WHERE rReference.ref_key = @ref_key ORDER BY rReference.ref_display ASC END END -- WE ARE JUST PULLING LISTBOX DISPLAY ROWS ELSE BEGIN IF @is_active > 0 BEGIN SELECT rReference.id, rReference.ref_display FROM rReference WHERE rReference.ref_key = @ref_key AND rReference.is_active = 1 AND rReference.is_deleted = 0 ORDER BY rReference.ref_display ASC END ELSE BEGIN SELECT rReference.id, rReference.ref_display FROM rReference WHERE rReference.ref_key = @ref_key ORDER BY rReference.ref_display ASC END END END