c# - 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).