Store objects with common base class in database

Question

Let's say i have a common base class/interface

  interface ICommand
  {
    void Execute();
  }

Then there are a few commands inheriting from this interface.

  class CommandA : ICommand
  {
    int x;
    int y;
    public CommandA(int x, int y)
    {  ...  }
    public void Execute () { ... }
  }
  class CommandB : ICommand
  {
    string name;
    public CommandB(string name)
    {  ...  }
    public void Execute () { ... }
  }

Now i want to store these commands in a database, with a common method, and then later load all of them from the DB into a List<ICommand> and execute the Execute-method of them.

Right now I just have one table in the DB called commands and here i store a string serialization of the object. Basically the columns in the table are: id|commandType|commaSeparatedListOfParameters. While this is very easy and works good for loading all commands, I can't query the commands easily without using substring and other obscure methods. I would like to have an easy way of SELECT id,x,y FROM commandA_commands WHERE x=... and at the same time have a generic way of loading all commands from the commands-table (i guess this would be some kind of UNION/JOIN of commandA_commands, commandB_commands, etc).

It is important that not much manual fiddling in the DB, or manual creation of serialize/parse-methods, is required to add a new command. I have tons of them and new ones are added and removed all the time. I don't mind creating a command+table+query generation tool though if this would be required for the best solution.

The best i can think of myself is a common table like id|commandType|param1|param2|param3|etc.. which isn't much better (actually worse?) than my current solution as many commands are going to need null parameters and the datatype will vary so i have to resort to common string conversion again and size each field big enough for the largest command.

The database is SQL Server 2008

Edit: Found similar question here Designing SQL database to represent OO class hierarchy

Answer 1

You can use an ORM to map the command inheritance to database. For example you can use "Table per Hierarchy" technique provided by the ORMs (eg: Entity Framework, nHibernate, etc). ORM will instantiate the correct subclass when you retrieve them.

Here's an example of doing it in Entity Framework Code first

abstract class Command : ICommand
{
   public int Id {get;set;}

   public abstract void Execute();
}

class CommandA : Command
{
   public int X {get;set;}
   public int Y {get;set;}

   public override void Execute () { ... }
}
class CommandB : Command
{
   public string Name {get;set;}

   public override void Execute () { ... }
}

Refere EF 4.1 Code First Walkthrough to configure this model with EF.

If your commands takes drastically different set of parameter you can consider using "Table per Type" inheritance modeling. Here you will have pay some significant performance penalty because of lot of Unions and table joins involved in this.

Alternative approach would be to store the Command parameters as a XML configuration where you (de)serialize manually. This way you can keep all your commands in a single table without sacrificing performance. Again this has a drawback where you can not filter using the command parameters.

Each approach has its Pros and Cons. You can choose the strategy which suits your requirements.

Answer 2

This problem is pretty common, and I haven't seen a solution that is without drawbacks. The only option to exactly store a hierarchy of objects in a database is to use some NoSQL database.

However, if a relational database is mandated, I usually go with this approach:

• one table for the base class/interface, that stores the common data for all types
• one table per descending class, that uses the exact same primary key from the base table (this is a nice use case for the SQL Server 2011 sequences, btw)
• one table that holds the types that will be stored
• a view that joins all those tables together, to enable easy loading/querying of the objects

In your case, I would create:

• Table CommandBase
  • ID (int or guid) - the ID of the command
  • TypeID (int) - ID of the type of command
• Table CommandA
  • ID (int or guid) - the same ID from the CommandBase table
  • X (int)
  • Y (int)
• Table CommandB
  • ID (int or guid) - the same ID from the CommandBase table
  • Name (nvarchar)
• Table CommandTypes
  • ID (int) - ID of the command type
  • Name (nvarchar) - Name of the command type ("CommandA", "CommandB",...)
  • TableName (nvarchar) - Name of the table that stores the type - usefull if some dynamic sql is needed - otherwise optional

• View Commands, something along the lines of:

  select cb.ID, a.X, a.Y, b.Name 
   from CommandBase cb
     left outer join CommandA a on a.ID = cb.ID
     left outer join CommandB b on b.ID = cb.ID
  

The upside of this approach is that it mirrors your class structure. It's easy to understand and use.

The downside is that is gets more and more cumbersome as you add new classes, it's hard to model more then one level of hierarchy, and the view can get a mile long if there are lots of descendants.

Personally, I would use this approach if I know that the number of subclasses is relatively small and relatively fixed, as it requires creating (and maintaining) a new table for each new type. However, the model is quite simple, so it's possible to create a tool/script that could do the creating and maintaining for you.

Answer 3

We are using XML in SQL more and more for our soft data. It might be slightly painful to query (using XPath), but it allows us to store metadata per row, validated against a schema, etc.

XML can then be parsed by code and parameters can be matched via reflection to parameters in the de-serialised object.

Effectively this replicates the ORM functionality, but with a flat database structure, simplifies queries, and the parameters are even queryable through XPath.

And remember kids, views are evil.