Best practices with codes or lookup tables

Question

[UPDATE] Selected approach below as an answer to this question

Hello,

I searched for this topic, but I cannot find what I am looking for ...

With code tables, I mean: things like "marriage status", gender, specific legal or social conditions ... More specifically, these types have only established properties, and items will not change soon (but they can). Properties are an identifier, a name and a description.

I am wondering how to best deal with this in the following technologies:

• in the database (several tables, one table with different code keys ...?)

• creating classes (maybe something like inheriting ICode with ICode.Name and ICode.Description)

• creating a presentation / presenter for this: there should be a screen containing all of them, so a list of types (gender, maritial status ...), and then a list of values ​​for this type with a name and description for each element in the list of values.

These are the things that appear in every single project, so there should be some best practice on how to handle these ...

For the record, I don't really like using enumerations for these situations ... Any arguments in favor of using them are also welcome.

[FOLLOW UP]

Ok, I got a good answer from CodeToGlory and Ahsteele. Let's clarify this question.

Say we are not talking about gender or marriage status, the meanings of which will definitely not change, but about “materials” that have a name and description, but nothing more. For example: Social statuses, Legal statuses.

: . Listbox NameAndDescription ( ), listbox NameAndDescription, Name Description NameAndDescription Type.

? , NameAndDescription ?

: pro/cons ?

Answer 1

:

CodeKeyManager mgr = new CodeKeyManager();
CodeKey maritalStatuses = mgr.ReadByCodeName(Code.MaritalStatus);

:

• CodeKeyManager CodeKeys DB (CodeKey = MatStatus)
• - , , , Code.MaritalStatus = "maritalStatus". CodeKey > CodeKeyName
• 2 :
  • CodeKey , CodeKeyName
  • CodeValue CodeKeyId, ValueName, ValueDescription

DB:

alt text http://lh3.ggpht.com/_cNmigBr3EkA/SeZnmHcgHZI/AAAAAAAAAFU/2OTzmtMNqFw/codetables_1.JPG

:

public class Code
{
    public const string Gender = "gender";
    public const string MaritalStatus = "maritalStatus";
}

CodeKey:

public class CodeKey
{
    public Guid Id { get; set; }
    public string CodeName { get; set; }

    public IList<CodeValue> CodeValues { get; set; }
}

CodeValue:

public class CodeValue
{
    public Guid Id { get; set; }

    public CodeKey Code { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }

}

:

• ( /)
• ,
• CodeKey...
• NHibernate .

, , - GUID (nchar) -.

! - , !

Answer 2

, , . , ( ), , , ( , ) .

autonumber, . ( , ) . ( ) autonumber .

. , ( ), , , .

Answer 3

:

• , . , MatStatus, ..

• , , , , / .

. key/value -, /.

Answer 4

. , , . , . , , .

, , , ..

Answer 5

? , , , . , . , , . , , , , .

( ).

, , , "", , "MarritalStatus"! , . CodeTable - , .

, CodeTable, , , .

, , -, , .

Answer 6

. 3 , (Code, CodeKey CodeValue), , , ? - .

:

CREATE TABLE [dbo].[Code](
    [CodeType] [int] NOT NULL,
    [Code] [int] NOT NULL,
    [CodeDescription] [nvarchar](40) NOT NULL,
    [CodeAbreviation] [nvarchar](10) NULL,
    [DateEffective] [datetime] NULL,
    [DateExpired] [datetime] NULL,
CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED 
(
    [CodeType] ASC,
    [Code] ASC
)
GO

CodeType = 0, Code = 0, CodeType. CodeType CodeType = 0 Code> = 1. , :

SELECT CodeType, Code, Description FROM Code

Results:

CodeType    Code    Description
--------    ----    -----------
0           0       Type
0           1       Gender
0           2       Hair Color
1           1       Male
1           2       Female
2           1       Blonde
2           2       Brunette
2           3       Redhead

Code , , CodeType:

ALTER TABLE [dbo].[Code] WITH CHECK ADD CONSTRAINT [CK_Code_CodeType]   
CHECK (([dbo].[IsValidCodeType]([CodeType])=(1)))
GO

IsValidCodeType :

CREATE FUNCTION [dbo].[IsValidCodeType]
(
    @Code INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @Result BIT
    IF EXISTS(SELECT * FROM dbo.Code WHERE CodeType = 0 AND Code = @Code)
        SET @Result = 1
    ELSE
        SET @Result = 0
    RETURN @Result
END
GO

, , , , . .

Person, . ( Gender), Code:

CREATE TABLE [dbo].[Person](   
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](40) NULL,
    [FirstName] [nvarchar](40) NULL,
    [GenderCode] [int] NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonID] ASC)
GO

ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [CK_Person_GenderCode] 
CHECK (([dbo].[IsValidCode]('Gender',[Gendercode])=(1)))
GO

IsValidCode :

CREATE FUNCTION [dbo].[IsValidCode]
(
    @CodeTypeDescription NVARCHAR(40),
    @Code INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @CodeType INT
    DECLARE @Result BIT

    SELECT @CodeType = Code
    FROM dbo.Code
    WHERE CodeType = 0 AND CodeDescription = @CodeTypeDescription

    IF (@CodeType IS NULL)
    BEGIN
        SET @Result = 0
    END
    ELSE
    BEGiN
    IF EXISTS(SELECT * FROM dbo.Code WHERE CodeType = @CodeType AND Code = @Code)
        SET @Result = 1
    ELSE
        SET @Result = 0
    END

    RETURN @Result
END
GO

, . Person:

SELECT PersonID,
    LastName,
    FirstName,
    GetCodeDescription('Gender',GenderCode) AS Gender
FROM Person

. , .