What is an optimal way to model inheritance in a database with mostly static data?

Question

I'm modelling a role-playing game and need to create the database schema for equipment. The data will be mostly static.

This article discusses three ways to model inheritance in a database: Table per Class Hierarchy, Table per Subclass, and Table per Concrete Class. Of the three, which would you recommend?

I did a sample Entity Framework 4.2 code-first solution to see what it came up with and it used the "Table per Class Hierarchy" method.

Here are the (simplified) classes:

public class Gear {
    public int GearID { get; set; }
    public string Name { get; set; }
}

public class Armor : Gear {
    public int Kinetic { get; set; }
    public int Energy { get; set; }
}

public class Weapon : Gear {
    public int Damage { get; set; }
    public int Range { get; set; }
}

score 0 · Accepted Answer · answered Jan 13 '12 at 18:32

everything has a tradeoff. You need to pick the approach best for you. The downside of per call hierarchy is that the table needs to have all the fields that are on any subclasses, which could mean that some rows in the table are taking up more space than necessary because they don't have some fields. Also, you need a way to discriminate between which type of class to hydrate when loading rows.

On the other hand, per class hierarchy is pretty simple to implement. The other strategies become more attractive the more divergent the data between subclasses is.

I decided to go with table-per-subclass. I have too many different types of subclasses to make table-per-class-hierarchy attractive. I used [this guide](http://www.robbagby.com/entity-framework/entity-framework-modeling-table-per-type-inheritance/) as a starting point. — Jason, Jan 16 '12 at 20:27

score 0 · Answer 2 · answered Jan 13 '12 at 18:38

You could do name value pairings. Have a table of Gear that has id, name, Attribute1Name, and Attribute1Value. Have as many name/value columns as you need.

So if you were storing an Armor item Attribute1Name would be Kinetic and Attribute1Value would be the value of Kenetic. Attribute2Name would be Energy and Attribute2Value would be the value of Energy.

What is an optimal way to model inheritance in a database with mostly static data?

2 Answers2