single table inheritance (with Ebean + Play! framework)

Question

I'm using the concept of single table inheritance because of OOP considerations of course.
for example, PostLike and TopicLike inherit from Like class.
I see two problems with this methodology:

1. instead of two tables (PostLike and TopicLike) I get "one big table" of likes.
2. this table has an extra column called dtype which enables record identification (i.e. type of like). in the long term it could be a huge waste of disk space. isn't it?

I'm not a DB expert and because of that I wanted to get your insights about this database design and whether or not those two problems are crucial.

Vaccination

Martin Fowler recommends STI over CTI. You should not be worried about the space requirements of a single smallint or tinyint field when a terabyte drive costs $60. – Mendenhall

Could you post a link to Martin Fowler recomendation, @NeilMcGuigan? – Bruce

@Bruce Can't find a link for you, but I recall reading an article by Mr. Fowler indicating that he recommended STI as the best first choice, especially for Agile development. He wasn't saying CTI is bad, just that it's more complicated and a bit slower. – Mendenhall

Answer 1

If you have only one table instead of two, the reads will be faster, because you'll avoid "joins". But you'll use more space because you'll have an extra "dtype" column, and some empty columns.

Let's get an example. Here is the model (without the JPA annotations):

public abstract class Like {
    public Long id;

    public String foo;
}

public class PostLike extends Like {

    public String post;
}


public class TopicLike extends Like {

    public String topic;
}

You'll get the table Like:

----------------------------------
|dtype | id | foo | topic | post |
----------------------------------
|post  |  1 |   a |  NULL |   p1 |
|topic |  2 |   b |    t1 | NULL |
----------------------------------

And as you can see, for a "PostLike" item, you'll have a NULL "topic" value.

But nowadays, the disk space if not a real problem.

The only flaw I see with single table inheritance is the number of columns which can be huge if you have a lot of properties, and it is harder to add a new property/column in your model (if you have to apply a DB evolution).

And AFAIK, ebean only supports "single table inheritance".

Stickney