Correct approach to model Lookup fields with small number of codes/values?

Oct 15, 2012 at 10:59 PM
Edited Oct 16, 2012 at 1:14 AM

Hi,

I have been struggling with this modelling issue. I have a table  with 10 fields with enum type values ie "Marital Status", "Gender" etc. It is tempting to make a detail table called something like "PersonLookupValues", but that feels wrong and there will always be 10 fields. So does that mean I have 10 foreign key constraints off the "Person" table to the "StdLookup" table? This would create 10 dropdowns in NO I guess, because of the definite relationship, but it seems quite over the top.

I understand it is also common practice to just put a code value into a field ie 10 for "Male", and not have a link. However the relationship is then absent and any logic would need adding in code.

Any thoughts on this would be hugely appreciated.

Many thanks,

Ed

EDIT: One could create an ENUM type in the Model layer and then just store ENUM values in the DB.  I have seen this in other ORM designers such as Devart Entity Developer, but then the meaning is seperated from the DB. I beliece VS2012/EF5 does this. So my instinct is still to have a "StdEnum" and "StdEnumValue" table in the DB, but I do not think these should be hard wired by FKs, but just using meaningful code values ie MR,MRS,SIR or F,M etc.

Editor
Oct 16, 2012 at 1:34 PM
I'd model these as enums, probably. Not that enums are very good in C#, but I agree that creating separate entities for each of these is a little over the top.

Alternatively, you could just use the ChoicesXxx() supporting method... this would make sense if the enumeration of values only applies to a single entity (ie Person).

HTH
Dan

On 15 October 2012 22:59, ESSB <notifications@codeplex.com> wrote:

From: ESSB

Hi,

I have been struggling with this modelling issue. I have a table with 10 fields with enum type values ie "Marital Status", "Gender" etc. It is tempting to make a detail table called something like "PersonLookupValues", but that feels wrong and there will always be 10 fields. So does that mean I have 10 foreign key constraints off the "Person" table to the "StdLookup" table? This would create 10 dropdowns in NO I guess, because of the definite relationship, but it seems quite over the top.

I understand it is also common practice to just put a code value into a field ie 10 for "Male", and not have a link. However the relationship is then absent and any logic would need adding in code.

Any thoughts on this would be hugely appreciated.

Many thanks,

Ed

Read the full discussion online.

To add a post to this discussion, reply to this email (nakedobjects@discussions.codeplex.com)

To start a new discussion for this project, email nakedobjects@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Oct 16, 2012 at 1:50 PM

Dan,

Really appreciate your wisdom. I think the main arguments for keeping Enums in the DB is for maintenance reasons, ie a change does not require a compilation, although I guess it would be possible to store enums in an XML file. Also for the reason of keeping data with the data. I will also look into the "Choices" idea.

Many thanks again,

Ed