Single Table Inheritance

Represent an inheritance hierarchy of classes as a single table which has fields for all the fields of the various classes

How it Works

In this inheritance mapping scheme we have one table that contains all the data for all the classes in the inheritance hierarchy. Each class stores the data that's relevant for that class into one row of the table. Any columns in the database that aren't relevant for the appropriate class are left empty.

The basic mapping behavior follows the general scheme of Inheritance Mappers.

When loading an object into memory you need to know which class to instantiate. To do this you have a field in the table that indicates which class should be used. This can be the name of the class or a code field. A code field needs to be interpreted by some code to map it to the relevant class. This code needs to be extended when a class is added to the hierarchy. If you embed the class name into the table you can just use it directly to instantiate an instance. The class name, however, will take up more space and maybe less easy to process by those using the database table structure directly, as well as more closely coupling the class structure to the database schema.

When loading data, you read the code first to figure out which subclass to instantiate. On saving the data the code needs be written out by the superclass in the hierarchy.

When to Use it

Single Table Inheritance is one of the options for mapping the fields in an inheritance hierarchy to a relational database. The alternatives are Class Table Inheritance and Concrete Table Inheritance

The strengths of Single Table Inheritance are:

A single table to worry about on the database
No joins in retrieving data
Any refactoring that pushes fields up or down the hierarchy doesn't require you to change the database.

The weaknesses of Single Table Inheritance are:

Fields are sometimes relevant and sometimes not, which can be confusing to people using the tables directly.
Columns that are only used by some subclasses lead to wasted space in the database. The degree of how much this is actually a problem depends on the specific data characteristics and how well the database compresses empty columns. Oracle, for example, is very efficient about trimming wasted space particularly if you keep your optional columns to the right hand side of the database table. Each database will have it's own tricks for this.
The single table may end up being too large with many indexes and frequent locking. This may hurt performance. You can avoid this by having separate index tables that either list keys of rows that have a certain property, or copy a subset of fields relevant to an index.
You only have a single name space for fields, so you have to be sure that you don't want to use the same name for different fields. You can do this by using compound names that use the name of the class as a prefix or suffix

It's important to remember that you don't need to use one form of inheritance mapping for you whole hierarchy. It's perfectly find to map half a dozen similar classes in a single table, but to use Concrete Table Inheritance for a couple of classes that have a lot of specific data.

Example: A single table for players (Java)

I've based this code example, like the other inheritance examples, on Inheritance Mappers

Figure 1: The generic class diagram of Inheritance Mappers

Each mapper needs to be linked to a data table in an ADO.NET data set. This link can be made generically in the mapper superclass. The gateway's data property is a data set which can be loaded by a query.

class Mapper... 
		protected DataTable table {
			get {return Gateway.Data.Tables[TableName];}
		}
		protected Gateway Gateway;
		abstract protected String TableName {get;}

Since there is only one table, this can be defined by the abstract player mapper.

class AbstractPlayerMapper... 
		protected override String TableName {
			get {return "Players";}
		}

Each class needs a type code to help the mapper code figure out what kind of player it has to deal with. The type code is defined on the superclass and implemented in the subclasses.

class AbstractPlayerMapper... 
		abstract public String TypeCode {get;}

class CricketerMapper... 
		public const String TYPE_CODE = "C";
		public override String TypeCode {
			get {return TYPE_CODE;}
		}

The player mapper has fields for each of the three concrete mapper classes

class PlayerMapper... 
		private BowlerMapper bmapper;
		private CricketerMapper cmapper;
		private FootballerMapper fmapper;
		public PlayerMapper (Gateway gateway) : base (gateway) {
			bmapper = new BowlerMapper(Gateway);
			cmapper = new CricketerMapper(Gateway);
			fmapper = new FootballerMapper(Gateway);
		}

Loading an object from the database

Each concrete mapper class has a find method to get an object from the data.

class CricketerMapper... 
		public Cricketer Find(long id) {
			return (Cricketer) AbstractFind(id);
		}

This calls generic behavior to find an object

class Mapper... 
		protected DomainObject AbstractFind(long id) {
			DataRow row = FindRow(id);
			return (row == null) ? null : Find(row);
		}
		protected DataRow FindRow(long id) {
			String filter = String.Format("id = {0}", id);
			DataRow[] results = table.Select(filter); 
			return (results.Length == 0) ? null : results[0];
		}
		public DomainObject Find (DataRow row) {
			DomainObject result = CreateDomainObject();
			Load(result, row);
			return result;
		}
		abstract protected DomainObject CreateDomainObject();

class CricketerMapper... 
		protected override DomainObject CreateDomainObject() {
			return new Cricketer();
		}

I load the data into the new object with a series of load methods, one on each class in the hierarchy.

class CricketerMapper... 
		protected override void Load(DomainObject obj, DataRow row) {
			base.Load(obj,row);
			Cricketer cricketer = (Cricketer) obj;
			cricketer.battingAverage = (double)row["battingAverage"];
		}

class AbstractPlayerMapper... 
		protected override void Load(DomainObject obj, DataRow row) {
			base.Load(obj, row);
			Player player = (Player) obj;
			player.name = (String)row["name"];
		}

class Mapper... 
		protected virtual void Load(DomainObject obj, DataRow row) {
			obj.Id = (int) row ["id"];
		}

I can also load a player through the player mapper. It needs to read the data and use the type code to determine which concrete mapper to use.

class PlayerMapper... 
		public Player Find (long key) {
			DataRow row = FindRow(key);
			if (row == null) return null;
			else {
				String typecode = (String) row["type"];
				switch (typecode){
					case BowlerMapper.TYPE_CODE: 
						return (Player) bmapper.Find(row);
					case CricketerMapper.TYPE_CODE: 
						return (Player) cmapper.Find(row);
					case FootballerMapper.TYPE_CODE: 
						return (Player) fmapper.Find(row);
					default:
						throw new Exception("unknown type");
				}
			}
		}

Updating an object

The basic operation for updating an object is the same for all objects, so I can define the operation on the mapper superclass.

class Mapper... 
		public virtual void Update (DomainObject arg) {
			Save (arg, FindRow(arg.Id));
		}

The save method is similar to the load, each class defines it to save the data in that class.

class CricketerMapper... 
		protected override void Save(DomainObject obj, DataRow row) {
			base.Save(obj, row);
			Cricketer cricketer = (Cricketer) obj;
			row["battingAverage"] = cricketer.battingAverage;
		}

class AbstractPlayerMapper... 
		protected override void Save(DomainObject obj, DataRow row) {
			Player player = (Player) obj;
			row["name"] = player.name;
			row["type"] = TypeCode;
		}

The player mapper forwards to the appropriate concrete mapper

class PlayerMapper... 
		public override void Update (DomainObject obj) {
			MapperFor(obj).Update(obj);
		}
		private Mapper MapperFor(DomainObject obj) {
			if (obj is Footballer)
				return fmapper;
			if (obj is Bowler)
				return bmapper;
			if (obj is Cricketer)
				return cmapper;
			throw new Exception("No mapper available");
		}

Inserting an object

Insertions are similar to updates, the only real difference is that a new row needs to be made in the table before saving

class Mapper... 
		public virtual long Insert (DomainObject arg) {
			DataRow row = table.NewRow();
			arg.Id = GetNextID();
			row["id"] = arg.Id;
			Save (arg, row);
			table.Rows.Add(row);
			return arg.Id;
		}

class PlayerMapper... 
		public override long Insert (DomainObject obj) {
			return MapperFor(obj).Insert(obj);
		}

Deleting an object

Deletes are pretty simple, defined at the abstract mapper level or in the player wrapper.

class Mapper... 
		public virtual void Delete(DomainObject obj) {
			DataRow row = FindRow(obj.Id);
			row.Delete();
		}

class PlayerMapper... 
		public override void Delete (DomainObject obj) {
			MapperFor(obj).Delete(obj);
		}