An alternative lookup table approach needed to make C# models more generic

Question

I currently have the following Models in my EF Code First MVC project (edited for brevity):

public class Car
{
    public int Id { get; set; }
    public string Descrip { get; set; }

    // Navigation Property.
    public virtual CarColour CarColour { get; set; }
    ... + numerous other navigation properties.
}

public class CarColour
{
    public int Id { get; set; }
    public string ColourName { get; set; }
}

The CarColour table in the DB contains many rows.

In my project, I have about 10 of these sorts of tables, which are essentially lookup tables.

Rather than have 10 lookup tables (and 10 corresponding 'hard' types in code), I was tasked with implementing a more re-usable approach, instead of having loads of lookup tables, specific to Car (in this example), along the lines of having a couple of tables, one of which may hold the item types (colour, fuel-type etc.) and one which contains the various values for each of the types. The idea being that our model will be able to be re-used by many other projects - some of which will have potentially hundreds of different attributes, and as such, we won't want to create a new Class/Type in code and generate a new lookup table for each.

I am having difficulty in understanding the c# implementation of this sort of approach and hope someone may be able to give me an example of how this can be achieved in code, more specifically, how the above models would need to change, and what additional classes would be required to accomplish this?

Letting persistence dictate business logic is a great way to find yourself in a position where your data doesn't make sense. Exacerbating that by allowing other code to directly access your persistence is just asking for trouble. — Mgetz, Aug 09 '13 at 12:34
Would seem to me that you'd have colors that get added and removed, so you would have historical records of cars that have colors no longer listed in the database. That would suggest to me that denormalization would be the route to go. Your colors table is just used as a lookup for current colors, but when a color is assigned to a vehicle it travels with the vehicle record and not as a relation. So I'd suggest denormalization. — , Aug 09 '13 at 12:35
Cars and colours were just an example to try to get my requirement across. I ultimately need to an 'Item' that can have x different attributes/values associated with it and this needs to be as generic as possible, so the same table schemas and generic classes can be reused in other applications that target different items (e.g. boats, guns etc.) — marcusstarnes, Aug 09 '13 at 12:42
I have to question the general approach here. Why can you not do the simplest thing that works, I.e., Creating unique tables for unique objects? Can you elaborate on your use case more? — DavidN, Aug 09 '13 at 12:46

score 0 · Answer 1 · answered Aug 09 '13 at 13:02

your base entity must implement INotifyPropertyChanged and make it generic:

public virtual CarColour CarColour {
 Get { return this.carColour; }
 Set {
     this.Carcolour; = value
     OnPropertyChanged("CarColour");
     }
}

For more info see : patterns & practices: Prism in CodePlex. http://compositewpf.codeplex.com/wikipage?title=Model%20View%20ViewModel%20(MVVM)

Greetings Bassam

score 0 · Answer 2 · answered Aug 09 '13 at 14:47

This is not necessarily specific to EF but I've been down this road and didn't really enjoy it.

I wanted to use a single table to represent 'generic' information and while I thought it was smart, it soon showed it's limitations. One of them being the complexity you need to introduce when writing queries to extract this data if you're performing more than just 'get colours for this car'.

I'd say, if your data is simple key/value and the value type is always going to be the same then go for it, it might even be worth having this a mere 'meta-data' for an object:

public class Car
{
    public int Id { get; set; }
    public string Descrip { get; set; }
    public MetaData CarColours { get; set; }
}

public MetaData : Dictionary<int, string>
{
    public MetaData(int group){}
}

Hypothetical table:

TableMetaData(int metaGroup, int metaId, string metaValue)

If you're hoping to store different types as your value and may need to perform joining on this data - avoid it and be a bit more specific.

An alternative lookup table approach needed to make C# models more generic

2 Answers2