ActiveRecord as an implementation of a design pattern straight out of the book

ActiveRecord has been built straight from the Martin Fowler's Single Table Inheritance design pattern. Period. This is fine for simple applications with that can live with the database design imposed by the application framework.

Once the developer tries to think throughly the database design, he immediately hits the limits of the STI paradigm. Here's an example.

One common method to define a hierarchical relation in Object-Relational Mapping where a child can have only one parent is to put the reference to the parent in the child table. This is a common and very efficient way to map a class hierarchy into database tables in Hibernate, for instance. The resulting tables can then look as follows:

CREATE TABLE `foos` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
);
CREATE TABLE `bars` (
  `id` int(11) NOT NULL auto_increment,
  `foo_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);
CREATE TABLE `bazs` (
  `id` int(11) NOT NULL auto_increment,
  `foo_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

Note that this table structure allows to efficiently retrieve bars and bazs from foos, as well as ascend to foos from bars and bazs.

If you are bound to ActiveRecord, it imposes a solution with one table having one additional id to identify the parent and a string to identify the type of the child. All fields belonging to bars and bazs will appear in the same table, intermixed.

CREATE TABLE `foos` (
  `id` int(11) NOT NULL auto_increment,
  `foo_id` int(11) NOT NULL,
  `foo_type` char(4) NOT NULL,
  PRIMARY KEY  (`id`)
);

This approach has many disadvantages, the most obvious one is that it may be necessary to remove restrictions on some columns that behave differently in different children. For example, using default NULL on fields that are shared between classes behind bazs and bars, whether or not one of the requires them.