Best way to save type of class in database?

Question

What is a good way to denote "type" in database?

I have a base class Action which is inherited by numerous child classes. Action has members like Id, Name etc all which corresponds to equivalent columns in a table in the database called action. So action table looks like this:

id | name | type 

The type column denotes what action it is. In my code they correspond to the child classes deriving from parent Action. Now how do I save the type of class to the type field in database?

The type column in db could be of any data type.

Options:

1. Save action.GetType().ToString() as string in db. And get the action type from db back by converting the string representation of the type to its original type using reflection. But this will be problematic if class names change in future.

2. Create an enum to denote each child class and decorate it by a TypeAttribute, something like:

   public abstract class Action
   {
       public enum Kind 
       {
           [Type(typeof(ControlAction))]
           ControlAction = 1, 
   
           [Type(typeof(UpdateAction))]
           UpdateAction = 2, 
   
           etc 
       }
   
       public abstract Kind ActionType { get; }
   }
   
   public class ControlAction : Action { public override Kind ActionType { get { return Kind.ControlAction; } } }
   public class UpdateAction : Action { public override Kind ActionType { get { return Kind.UpdateAction; } } }
   //etc
   

   This looks good, except that for each class from here onwards I have to create an enum. And it feels like a little too much work to be done.

3. Build a separate static hash table of <int, Type> that ties a class to a int value. May be a little bit unreadable.

Is there a better solution to this?

Answer 1

I would go from the 3rd solution with a hash-table, as it does seem to be the cleaner design-wise. And I would delegate its management to the database!

After all, isn't this what relational databases excel at the most, creating relations between two entities (in your case, action and type)? Other advantage is you end up with a normalized schema (sure, so far, there is only one column to the type table, namely its name, but normalizing allows you to easily add additional attributes to the types should you need them in the future, which is why it is cleaner as a design).

The schema would be something like this:

Action table

action_id(PK) | name | type_id (int, FK to Type table)

Type table

type_id(PK) | type_name

Now you are safe if the name of a class changes in the future (concern from your first proposition with string type). Indeed, all you would do is change the type_name value in the corresponding Type table row and all your Action rows would still be linked to this row by the type_id, which never changes once created (no problem here, as it does not hold any "business meaning").

And you have your hash-table from 3 (the Type table) in a readable format as it is the RDMBS's responsibility to manage the keys of the hash-table (the type_id PK).

Note that you won't have to tie your class to an int value corresponding to the type_id column, but rather fetch from the Type table the type_id by looking it up against the Class type (type_name).

Answer 2

I ended up using option 2, but with less clutter of attributes. Something like this:

public abstract class Action
{
    public enum Kind 
    {
        ControlAction = 1, 

        UpdateAction = 2, 

        etc 
    }

    public abstract Kind ActionType { get; }
}

public class ControlAction : Action { public override Kind ActionType { get { return Kind.ControlAction; } } }
public class UpdateAction : Action { public override Kind ActionType { get { return Kind.UpdateAction; } } }

The biggest advantage for this is that (even if it meant more typing), it enforces a numeric value to be associated with a class type.

Now class to int is just:

var value = (int)instance.ActionType;

Very fast.

But to convert int to class instance (or class type), I will have to create an instance of each sub action types, and compare its ActionType property to match the input int value. This is going to be slow. But I can cache somethings and make it faster. Something like:

static readonly Dictionary<Action.Kind, Type> actionTypes = 
   GetDefaultInstanceOfAllActions().ToDictionary(x => x.ActionType, x => x.GetType());
public static Action ToAction(this Action.Kind value)
{
    return (Action)Activator.CreateInstance(actionTypes[value]);
}

The GetDefaultInstanceOfAllActions does some reflection (once) to get all types of actions (I use something like this answer for that). I can even make the make the instantiation faster by going the expression route.

The benefits:

1. Less hassle when creating a new class (no attributes).

2. Enforces an int to be tied to a class type.

3. Moderately fast with adequate caching.

Answer 3

I would go with your first option and use reflection. It seems more likely that you will want to add new action types rather than change existing class names and therefore the ease of serializing the type using reflection is more useful. You could then just have a utility class for serializing actions and restoring them from their type string.