How do I design a class containing properties from lookup tables?

Question

Some context: This pertains to my budding desire to build multilayer web applications with:

1. C# ASP.NET web forms
2. C# POCO business objects
3. Some kind of DAL... SQL (or maybe EF4 if I can figure it out)

I wouldn't really want a response which has my presentation layer talking directly to EF entities, for instance.

I've been doing my own web development with C# ASP.NET & SQL for 10 years but I am still a total rookie when it comes to formal OOAD. I've been pursuing this skill with a passion lately, but I'm still new at it and there is something I can't quite wrap my head around. I'm hoping someone can explain it in a way that brings an epiphany:

Let's say I create a web application that manages People in some way, and my Person object must possess a properties such as FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince, etc. I'm using SQL Server for persistence... so common sense would dictate that the respective fields in the People table are all foreign keys:

FirstName varchar(50)
LastName varchar(50)
HairColor tinyint
EyeColor tinyint
Ethnicity tinyint
StateOrProvince tinyint

Clearly this means that I have corresponding lookup tables for each of those fields (i.e. HairColors table, EyeColors table, Ethnicities table, etc.) and each of these lookup tables has an ID and a name. Of course the Name field in these lookup tables will be JOINed with my People data whenever I want to display anything useful about a Person.

Some key features of the site would be:

1.) Enumerate People in a Gridview (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

2.) Show an individual Person's details on a read-only page (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

3.) Allow a user to update an individual Person's data on an update page (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

Case #1 If I was enumerating a collection of Person objects in a gridview... each Person instance would need to display its HairColor, EyeColor, Ethnicity, StateOrProvince properties as strings to be meaningful (i.e. the Name field from the SQL lookup table, not its ID). Clearly my SQL sproc would have some JOINs to give me the appropriate string data I need to fill these text properties in each Person instance.

Case #2 Again my sproc would have a JOIN to bring back the human readable property names as strings, and I would display them in read only Label controls using something like myPerson.HairColor, myPerson.EyeColor, etc.

Case #3 Here I'd be showing a page with dropdown lists for each of these properties (i.e. value=HairColorId, Text=HairColorName). My immediate instinct here would be to use the IDs of each property (something like myPerson.HairColorId) in order to loop through the DDL items and select a value which represents the hair color that the People table currently holds for this Person. If the user selected something different in any of the property DDLs, I'd need to pass the appropriate SelectedId values to an UPDATE sproc, and modify the values in the People table for this particular Person.

So that leads me to the ultimate question:

How do I best design a Person object such that it contains both the ID and Name for HairColor, EyeColor, Ethnicity, StateOrProvince so I can subsequently use the Name when displaying information, but the ID for initializing the update DDL controls... and ultimately processing updates?

As I've reflected on this... I have come to the conclusion that I need to create classes to represent the HairColor, EyeColor, Ethnicity, StateOrProvince properties.

Then my Person class, instead of being something like this:

public class Person
{
    string FirstName { get; set; }
    string LastName { get; set; }

    int HairColorId { get; set; }
    string HairColorName { get; set; }

    int EyeColorId { get; set; }
    string EyeColorName { get; set; }

    int StateOrProvinceId { get; set; }
    string StateOrProvinceName { get; set; }
    string StateOrProvinceCode { get; set; }
}

Would instead be expanded into something like this:

public class HairColor
{
    int Id { get; set; }
    string Name { get; set; }
}

public class EyeColor
{
    int Id { get; set; }
    string Name { get; set; }
}

public class StateOrProvince
{
    int Id { get; set; }
    string Name { get; set; }
    string Code { get; set; }
}

public class Person
{
    HairColor HairColor { get; set; }
    EyeColor EyeColor { get; set; }
    StateOrProvince StateOrProvince { get; set; }

    public Person()
    {
        // how do I initialize a Person from a SQL data row?

    }
}

But then if my Person class does look like the above... how on earth do I best initialize it (whether individually or in a collection) from a given row of data I get back from a SQL query? I seem to recall that I shouldn't be newing things up in a constructor (i.e. this.HairColor = new HairColor(dr["HairColorId"), dr["HairColorName"];)... so I'm wondering how a call to

public static IEnumerable<Person> GetPeople()
{
    ...
}

in my BLL might fill each user up with its data before it is added to the collection?

Really hoping someone can give me an "a ha" moment here...

Answer 1

I think you have the right approach, creating classes for those supporting entities (although I'd put StateOrProvince in a separate Address entity, and maybe all those traits in a separate PersonTraits entity).

There're many ways to solve this. Without an ORM, take a look at Data Mappers (also at Dependent Mapping), which could be used to map from a database query to a Person instance. This is an outline of the mapper code:

var row = ... // query database
var person = new Person(row["FirstName"], row["LastName"]);
person.EyeColor = new EyeColor(row["EyeColorID"], row["EyeColorName"]);
...

(You could also use some kind of separate Object Builder.)

Anytime you update a person, you update all supporting information as well using the ID of the related entities.

UPDATE: an ORM like EF4 is very powerful and would help you with a lot of repetitive tasks (like the mapping I've described). The important thing is to keep your architecture flexible and have persistency as just one swappable layer. Take a look here for some guidance. Also, I found that the book "Domain Driven Design" is really important to understand this kind of separation and how to model your entities.