design patterns

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.