model classes in a database with my own enum types

Question

Hi,

I have an application that I need to query lifetables (for insurance calculation). I was thinking about using XML to store the data, but thought it was a little big, but maybe a little small for using a full-fledged database. So I chose to use SQLite.

In my application, I have enums defining a few different things. For example, GENDER.Male, GENDER.Female. and JOBTYPE.BlueCollar, JOBTYPE.WhiteCollar. etc etc.

I have some methods that look like this: (example)

FindLifeExpectancy(int age, GENDER gender);
FindDeathRate(int age, JOBTYPE jobType);

So my question is: How do you model enums in a database? I don't think it is best practice to use 0 or 1 in the database to store JOBTYPE because that would be meaningless to anyone looking at it. But if you used nvarchar, to store "BlueCollar", there would be a lot of duplicate data.

I don't think GENDER or JOBTYPE should have an entire class, or be apart of the entity model because of the little information they provide.

How is this normally done?

Thanks.

Answer 1

While it's unlikely that you will be adding a new gender, I wouldn't be so sure about the jobtype enum. I'd have used a separate table for both, and have foreign keys to this table every where I need to reference them. The schema will be extensible, the database will automatically check that only possible values are saved in the referencing tables.

Answer 2

The SQL equivalent of 'enums' are lookup tables. These are tables with two (sometimes more) columns:

• a code, typically short, numeric or character (ex: 'R', 'S', 'M'...)
• a text definition (ex: 'Retired', 'Student', 'Military'...)
• extra columns can be used to store definitions, or alternate versions of the text for example a short abbreviation for columnar reports)

The short code is the type of value stored in the database, avoiding the replication you mentioned. For relatively established categories (say Male/Female), you may just use a code, without 'documenting' it in a lookup table.

If you have very many different codes, it may be preferable to keep their lookup in a single SQL table, rather than having a proliferation of dozen of tables. You can simply add a column that is the "category", which itself is a code, designating the nature of the group of codes defined in this category ("marital status", "employment", "education"...)

The info from the lookup tables can be used to populate drop downs and such, in the UI, wherey the end-user sees the clear text but the application can use the code to query the database. It is also used in the reverse direction, to produce the clear text for codes found in the database, for displaying results list and such.

A JOIN construct at the level of SQL is a convenient way to relate the lookup table and the main table. For example:

SELECT Name, Dob, M.MaritalStatus
FROM tblCustomers C
LEFT OUTER JOIN tblMaritalLkup M ON C.MStatus = M.Code
WHERE ...

Answer 3

I prefer to statically map my enums in my program to a lookup table in my database. I rarely actually use the lookup table to do a join. As an example I might have the following tables:

Gender
GenderID  Name
1         Male
2         Female

Accounts
AccountID  GenderID  FirstName  LastName
1          1         Andrew     Siemer
2          2         Jessica    Siemer

And in code I would then have my enum defined with the appropriate mapping

public enum Gender
{
    Male = 1,
    Female = 2
}

Then I can use my enum in code and when I need to use the enum in a LINQ to SQL query I just get its physical value like this

int genderValue = (int)Enum.Parse(typeof(Gender), Gender.Male));

This method may make some folks out there a bit queezy though given that you have just coupled your code to values in your database! But this method makes working with your code and the data that backs that code much easier. Generally, if someone swaps out the ID of a lookup table, you are gonna be hosed in some way or another given that it is mapped across your database any how! I prefer the readability and ubiquitous nature of this design though.