Consolidating Entity Lookup Types

Often times database entities reference lookup type tables e.g. (Status, Product, Occupation, ect...)

While this design has its benefits such as being able to apply a constraint such as a foreign key, one downside is that it tends to pollute the list of database objects and application classes which I find can decrease entity relationship comprehension.

In pursuing efficiency and design consolidation, I will demonstrate a design that addresses the common schema of lookup types and detail an implementation which vertically consolidates the common need for non-mecurial values. For each distinct lookup type table, we'll instead implement a dictionary with each main key representing a lookup type list of values.

Most lookup type tables have the same schema (ID, Name, Code, Description). To consolidate all these potential tables that bring noise to the database table list, we'll create a table called Lookup that introduces the Type field, which will represent what was previously a lookup type table name:

An example of the values for such a table might look like this:

Let's create and populate a typical entity table to represent a Person:

The OccupationID and GenderID fields are populated with identity values of the Lookup table. **Note that the first major downside then is that we immediately lose the ability to enforce a referential constraint (foreign key) in a meaningful way, because Lookup identitiy values for GenderID can be used in the OccupationID field and vice versa. If this can be overlooked for the sake of clarity then let's continue!

The code example is a simple console application using some bad (pretty bad) practices just for the sake of keeping it simple.

The LookupHelper static class defines a static Type property of type Dictionary<string, Dictionary<string, Lookup>> which will serve to store the values of the Lookup table for access within the application.

The first part "Dictionary<string" defines a key which represents the lookup type, such as "Occupation" or "Gender". The second part "Dictionary<string, Lookup>>" defines the set of lookup records that are grouped within the key, so for the Occupation group, one of the set keys would be "Programmer" or "Biologist", and the value would then be an instance of the Lookup type (**See below for Lookup type definition).

    public static class LookupHelper
    {
        public static Dictionary<string, Dictionary<string, Lookup>> Type;

        static LookupHelper()
        {
            var lookups = new List<Lookup>();

            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LookupExample"].ConnectionString))
            {
                var cmd = new SqlCommand("SELECT * FROM Lookup", conn);

                conn.Open();

                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        lookups.Add(new Lookup(
                            dr.GetInt32(0),
                            dr.GetString(1),
                            dr.GetString(2),
                            dr.GetStringOrEmpty(3),
                            dr.GetStringOrEmpty(4)
                        ));
                    }
                }
            }

            Type = lookups.GroupBy(lookup => lookup.Type).ToDictionary(typeGroup => typeGroup.Key, typeGroup => typeGroup.ToDictionary(nameGroup => nameGroup.Name));
        }
    }

GetStringOrEmpty is a simple extension method for catching nulls.

The Lookup class is an exact representation of the Lookup table schema:

    public class Lookup
    {
        public Lookup(int id, string type, string name, string code, string description)
        {
            ID = id;
            Type = type;
            Name = name;
            Code = code;
            Description = description;
        }
        public int ID { get; }

        public string Type { get; }

        public string Name { get; }

        public string Code { get; }

        public string Description { get; }
    }

The LookupHelper class' constructor executes once as the first time the Type field is accessed. It populates the Type field with the appropriate values from the Lookup table.

Let's create a Person class to represent a record for a person in the Person table:

    public class Person
    {
        public Person(int id, string firstName, string lastName, Lookup occupation, Lookup gender)
        {
            ID = id;
            FirstName = firstName;
            LastName = lastName;
            Occupation = occupation;
            Gender = gender;
        }
        public int ID { get; }

        public string FirstName { get; }

        public string LastName { get; }

        public Lookup Occupation { get; }

        public Lookup Gender { get; }
    }

Note that the types for the Occupation and Gender properties are of type Lookup.

Now let's see the code for the Program itself and how it all comes together:

    class Program
    {
        static void Main(string[] args)
        {
            var persons = new List<Person>();

            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LookupExample"].ConnectionString))
            {
                var cmd = new SqlCommand(@"SELECT p.ID, p.FirstName, p.LastName, o.*, g.*
                                           FROM Person p
                                           CROSS APPLY
                                           (
                                             SELECT [Name] AS Occupation
                                             FROM Lookup l
                                             WHERE p.OccupationID = l.ID
                                           ) o
                                           CROSS APPLY
                                           (
                                             SELECT [Name] AS Gender
                                             FROM Lookup l
                                             WHERE p.GenderID = l.ID
                                           ) g", conn);

                conn.Open();

                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        persons.Add(new Person(
                                dr.GetInt32(0),
                                dr.GetString(1),
                                dr.GetString(2),
                                LookupHelper.Type["Occupation"][dr.GetString(3)],
                                LookupHelper.Type["Gender"][dr.GetString(4)]
                            ));
                    }
                }
            }
        }

The SQL command returns fields joined to the result (Occupation, Gender) which contain the name of the lookup item which matches the OccupationID and GenderID values for each Person. Knowing the lookup Type field value ("Occupation", "Gender") and the name of the item in the corresponding lookup set allows us to "query" the LookupHelper.Type dictionary which yields the correct Lookup type instance which holds the ID, Type, Name, Code, and Description of the full Lookup table record.

There are numerous design challenges with this pattern such as Entity Framework Code First implementations, the loss of inherent enum support, and some SQL joins can get funky, however I have been able to address each concern that has come up, and overall I appreciate the efficiency and elegance.

Each developer must weigh all the considerations in the balance because this is not an easy design to undo.