# Table Inheritance based on a function

**URL:** <https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245>\
**Category:** Rails\
**Created:** [June 29, 2009, 2:16pm UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245 "2009-06-29T14:16:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bernd-vdb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ee7513/32.png) [@bernd-vdb](https://www.ruby-forum.com/u/bernd-vdb)\
**Post date:** [June 29, 2009, 2:16pm UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245/1 "2009-06-29T14:16:29Z")

</div>

Hello,  
we have a problem in a CMS project that we believe is basic for RoR  
developement and could concern others as well - or have already:

RoR implements the ‘Single Table Inheritance’ (STI) Pattern:  
[http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html](http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html)

… through its ‘ActiveRecord’ ORM.  
The Fowler example mentions a ‘type’ column and RoR implements this  
directly - which we consider inconsistent with DB abastraction:  
It forces a ‘type’ column in the database following the application -  
instead of the application asking the DB to derive the type _property_  
from immanent record properties.

Practically, we would like to have a database function or condition to  
control the type.  
As a first step this can be done like this:  
Select \*, CASE WHEN some\_condition THEN ‘a’ ELSE ‘b’ END AS type  
FROM table1;

We could define this as a view and put ut in a RoR model.  
Problem is RoR can not tell a real table from a view (another  
deficiency IMO) - and will try to UPDATE or INSERT the view which  
fails.

So what we’d need is a more flexible way to define STI, or maybe a way  
to define a different Table for SELECTs (here a DB-view) in a model  
than for UPDATEs and INSERTs.

Has anybody done this before or what are your proposals for a solution?

---

<div class="post-metadata">

**Author:** ![bernd-vdb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ee7513/32.png) [@bernd-vdb](https://www.ruby-forum.com/u/bernd-vdb)\
**Post date:** [June 30, 2009, 4:59pm UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245/2 "2009-06-30T16:59:25Z")

</div>

How is this different from setting the type attribute in a before\_save  
callback, other than moving the calculation from Ruby to SQL and doing  
it on _every_ query? If it’s 100% necessary to move the type  
calculation into the DB, triggers could accomplish much the same  
thing.

But maybe I’m missing something - is there a use case where caching  
the type field like this wouldn’t work? The only thing I can think of  
is a situation where the object’s type depends on _external_  
parameters (eg, the current date), but that seems exceptionally  
unlikely.

–Matt J.

---

<div class="post-metadata">

**Author:** ![bernd-vdb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ee7513/32.png) [@bernd-vdb](https://www.ruby-forum.com/u/bernd-vdb)\
**Post date:** [July 3, 2009, 3:43pm UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245/3 "2009-07-03T15:43:14Z")

</div>

Hello,

On 30 Jun., 16:58, Matt J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> How is this different from setting the type attribute in a before\_save  
> callback, other than moving the calculation from Ruby to SQL and doing  
> it on _every_ query? …

> … is there a use case where caching  
> the type field like this wouldn’t work? The only thing I can think of  
> is a situation where the object’s type depends on _external_  
> parameters (eg, the current date),

Of course the reason to do have the type derived from data in every  
query would be that the data might change, depending on user input,  
external phenomenon or another application accessing the same  
database.  
Abstracting data from application was one reason to use DBs in the  
first place.

> If it’s 100% necessary to move the type  
> calculation into the DB, triggers could accomplish much the same  
> thing.

Triggers could also provide an “updateable view”, but this would mean  
introducing quite some code into the DB just to facilitate a special  
application mechanism.  
If the information about ‘type’ is already given by other columns, an  
extra column is more like a cheap hack.

Am I really the only one who sees a shortcoming here?  
And has no one ever wanted to access DB-Views with RoR (i.e. having  
the app understand that a certain relation is _not_ updateable)?

---

<div class="post-metadata">

**Author:** ![bernd-vdb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ee7513/32.png) [@bernd-vdb](https://www.ruby-forum.com/u/bernd-vdb)\
**Post date:** [July 4, 2009, 12:11am UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245/4 "2009-07-04T00:11:59Z")

</div>

On Jul 3, 9:42 am, bvdb [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > parameters (eg, the current date),
> 
> Of course the reason to do have the type derived from data in every  
> query would be that the data might change, depending on user input,  
> external phenomenon or another application accessing the same  
> database.  
> Abstracting data from application was one reason to use DBs in the  
> first place.

Even the view method is going to end up binding the two together,  
unless you plan on somehow returning _different_ values for type  
depending on the calling application.

> > If it’s 100% necessary to move the type  
> > calculation into the DB, triggers could accomplish much the same  
> > thing.
> 
> Triggers could also provide an “updateable view”, but this would mean  
> introducing quite some code into the DB just to facilitate a special  
> application mechanism.

Maybe I’m being dense, but it doesn’t seem like any more “code in the  
DB” than defining the view.

> If the information about ‘type’ is already given by other columns, an  
> extra column is more like a cheap hack.

Or, as the computer scientists call it, “memoization”. I’d presume  
that the types involved change far less often than the data is  
accessed.

> Am I really the only one who sees a shortcoming here?  
> And has no one ever wanted to access DB-Views with RoR (i.e. having  
> the app understand that a certain relation is _not_ updateable)?

Some Google searching shows that most of the discussion around views  
and ActiveRecord focuses on either using updatable views or on doing  
read-only stuff with non-updatable views. I did find the Class-table  
Inheritance plugin, which may yield some insight for your project  
(docs @ [http://clti.rubyforge.org/](http://clti.rubyforge.org/)). The biggest issue is that while  
views are part of the SQL standard, the rules for updating are very DB-  
specific. Since AR is targeted to be database-independent, the result  
is totally ignoring the feature.

–Matt J.

---

<div class="post-metadata">

**Author:** ![bernd-vdb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ee7513/32.png) [@bernd-vdb](https://www.ruby-forum.com/u/bernd-vdb)\
**Post date:** [July 4, 2009, 5:14pm UTC](https://www.ruby-forum.com/t/table-inheritance-based-on-a-function/171245/5 "2009-07-04T17:14:51Z")

</div>

On 4 Jul., 00:10, Matt J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Even the view method is going to end up binding the two together,

Of course, but it will derive the ‘type’ at the moment of the query,  
and not a type that was last UPDATEd by the last RoR application (and  
hopefully anybody else accessing the DB).

> Maybe I’m being dense, but it doesn’t seem like any more “code in the  
> DB” than defining the view.

Unfortunately it _is_ a lot more.  
A database view is per se not updateable - several mechanism are being  
discussed for a solution - like here for Postgres:  
[http://wiki.postgresql.org/wiki/Updatable\_views](http://wiki.postgresql.org/wiki/Updatable_views)

None of these concepts is simple and without debate.  
And IMHO updateable view is also violating the “spirit of the  
inventor” of relational DBs.

> Or, as the computer scientists call it, “memoization”. I’d presume  
> that the types involved change far less often than the data is  
> accessed.

The database and its developers are mostly more succesful in deciding  
how to optimize access to its tables than an application. This is one  
of the organizational decisions a database implies.

> Some Google searching shows that most of the discussion around views  
> and ActiveRecord focuses on either using updatable views or on doing  
> read-only stuff with non-updatable views. I did find the Class-table  
> Inheritance plugin, which may yield some insight for your project  
> (docs @[http://clti.rubyforge.org/](http://clti.rubyforge.org/)).

Ok, I’ll look closer into this.

> The biggest issue is that while  
> views are part of the SQL standard, the rules for updating are very DB-  
> specific. Since AR is targeted to be database-independent, the result  
> is totally ignoring the feature.

Exactly - that’s why it would make sense to have a standard RoR method  
for setting a DB function - see example in my first post - for  
deriving the class; instead of a fixed column.

And concerning DB-views, which is a separate issue:  
A standard RoR solution for DB-Views would be nice.