best practices with code or lookup tables

Question

[UPDATE] Chosen approach is below, as a response to this question

Hi,

I' ve been looking around in this subject but I can't really find what I'm looking for...

With Code tables I mean: stuff like 'maritial status', gender, specific legal or social states... More specifically, these types have only set properties and the items are not about to change soon (but could). Properties being an Id, a name and a description.

I'm wondering how to handle these best in the following technologies:

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

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

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

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

For the record, I'm not really fond of using enums for these situations... Any arguments on using them here are welcome too.

[FOLLOW UP]

Ok, I've gotten a nice answer by CodeToGlory and Ahsteele. Let's refine this question.

Say we're not talking about gender or maritial status, wich values will definately not change, but about "stuff" that have a Name and a Description, but nothing more. For example: Social statuses, Legal statuses.

UI: I want only one screen for this. Listbox with possibe NameAndDescription Types (I'll just call them that), listbox with possible values for the selected NameAndDescription Type, and then a Name and Description field for the selected NameAndDescription Type Item.

How could this be handled in View & Presenters? I find the difficulty here that the NameAndDescription Types would then need to be extracted from the Class Name?

DB: What are pro/cons for multiple vs single lookup tables?

Answer 1

Couple of things here:

1. Use Enumerations that are explicitly clear and will not change. For example, MaritalStatus, Gender etc.

2. Use lookup tables for items that are not fixed as above and may change, increase/decrease over time.

It is very typical to have lookup tables in the database. Define a key/value object in your business tier that can work with your view/presentation.

Answer 2

I lean towards using a table representation for this type of data. Ultimately if you have a need to capture the data you'll have a need to store it. For reporting purposes it is better to have a place you can draw that data from via a key. For normalization purposes I find single purpose lookup tables to be easier than a multi-purpose lookup tables.

That said enumerations work pretty well for things that will not change like gender etc.

Answer 3

Using database driven code tables can very useful. You can do things like define the life of the data (using begin and end dates), add data to the table in real time so you don't have to deploy code, and you can allow users (with the right privileges of course) add data through admin screens.

I would recommend always using an autonumber primary key rather than the code or description. This allows for you to use multiple codes (of the same name but different descriptions) over different periods of time. Plus most DBAs (in my experience) rather use the autonumber over text based primary keys.

I would use a single table per coded list. You can put multiple codes all into one table that don't relate (using a matrix of sorts) but that gets messy and I have only found a couple situations where it was even useful.