Single Table Inheritance?

86 views
Skip to first unread message

Fran

unread,
Jan 14, 2009, 2:42:30 AM1/14/09
to web2py Web Framework
Does Web2Py support his?
- it certainly looks very useful...

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

Examples of this in Rails:
http://wiki.rubyonrails.org/rails/pages/singletableinheritance
http://www.juixe.com/techknow/index.php/2006/06/03/rails-single-table-inheritance/
http://spattendesign.com/2007/12/31/fun-with-single-table-inheritance

I do /some/ of this already following the T2 style of defining a base
table type (T2 calls this 'trackable') & then extending this in the
model with resource-specific fields.
This gives me DRYness but doesn't mean that I can do a search of all
sub-tables without a JOIN...

Many thanks,
Fran.

mdipierro

unread,
Jan 14, 2009, 3:32:33 AM1/14/09
to web2py Web Framework
yes. and it actually supports mutliple table inheritance

single inheritance

db.define_table('player',db.Field('name'))
db.define_table('footballer',db.player,db.Field('club'))
db.define_table('cricketer',db.player,db.Field
('batting_average','integer'))
db.define_table('bowler',db.cricketer,db.Field
('bowling_average','integer'))

multiple inheritance

db.define_table('married',db.Field('companion_name'))
db.define_table('married_bowler',db.bowler,db.married)

too bad this is not yet documented anywhere. Perhaps you could help us
by writing somehting about it.

Massimo

On Jan 14, 1:42 am, Fran <francisb...@googlemail.com> wrote:
> Does Web2Py support his?
> - it certainly looks very useful...
>
> http://martinfowler.com/eaaCatalog/singleTableInheritance.html
>
> Examples of this in Rails:http://wiki.rubyonrails.org/rails/pages/singletableinheritancehttp://www.juixe.com/techknow/index.php/2006/06/03/rails-single-table...http://spattendesign.com/2007/12/31/fun-with-single-table-inheritance

mdipierro

unread,
Jan 14, 2009, 3:38:00 AM1/14/09
to web2py Web Framework
On a second thought.... this is not quite the same of what the link
says.

My example defines 6 different tables.

The link you send seems to suggest building a single table with unused
fields. web2py does not allow that and I would not consider it very
clean.

Massimo


On Jan 14, 2:32 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> yes. and it actually supports mutliple table inheritance
>
> single inheritance
>
> db.define_table('player',db.Field('name'))
> db.define_table('footballer',db.player,db.Field('club'))
> db.define_table('cricketer',db.player,db.Field
> ('batting_average','integer'))
> db.define_table('bowler',db.cricketer,db.Field
> ('bowling_average','integer'))
>
> multiple inheritance
>
> db.define_table('married',db.Field('companion_name'))
> db.define_table('married_bowler',db.bowler,db.married)
>
> too bad this is not yet documented anywhere. Perhaps you could help us
> by writing somehting about it.
>
> Massimo
>
> On Jan 14, 1:42 am, Fran <francisb...@googlemail.com> wrote:
>
> > Does Web2Py support his?
> > - it certainly looks very useful...
>
> >http://martinfowler.com/eaaCatalog/singleTableInheritance.html
>
> > Examples of this in Rails:http://wiki.rubyonrails.org/rails/pages/singletableinheritancehttp://...

Fran

unread,
Jan 14, 2009, 11:21:19 AM1/14/09
to web2py Web Framework
On Jan 14, 8:38 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> On a second thought.... this is not quite the same of what the link
> says.
> My example defines 6 different tables.

Right & my experience of trying to import data from an external source
into such a distributed set of tables is that it requires a lot fo
work :/

> The link you send seems to suggest building a single table with unused
> fields. web2py does not allow that and I would not consider it very
> clean.

Look at the Rails implementations...I'm not sure they do this?

What we want is for the user to see all relevant fields together in a
single form.

Right now there are 3 options to achieve this:
1. Have a single table with fields for all potential cases
- this means the developer can use standard T2 CRUD functions (low
maintenance) & then tweak using jquery to hide/show fields as they
become relevant or not (extra maintenance here).
2. Have multiple tables - 1 for each subtype.
- this allows developers to use the standard T2 CRUD again, although
itemize requires additional work as each subtype needs to be listed
separately. We also can't change sub-type within the form...if we
needed that we'd have to add a separate controller with jquery to suck
in a whole new form which seems like work involved to retain field
salready filled-in, etc.
3. Have multiple linked tables to handle the fields which are only
relevant to certain contexts (as per your example & how current Sahana
is structured).
- this means the developer has to ditch T2 & use manual forms (the
table with most fields could be a SQLFORM with just the extra ones
handled manually). Again if subtype is to be changeable, need to use
jquery to hide/show fields. This is a lot more work, especially to
build into a central CRUD controller to which extra functionality like
new import/export routines, authorization & auditing can be added. It
is also painful when trying to import data from 3rd-party systems as
each table needs to be imported separately with matching ID links (I'm
fighting this exact issue currently for a live implementation).

If we look at my GIS Layers example (OpenStreetMap, Google, Yahoo, etc
base layers with overlays from WMS, internal features
, GeoRSS feeds, etc) I implemented originally using option 3 which
worked fine, but the maintenance levels required were high.
I started trying to rewrite as a CLASS (extending SQLTABLE), but
before I got too far with this I just implemented option 2.
This is massively easier for me & I have no need to adjust subtype in
the forms so I just have the hassle of maintaining the itemize list.
(I'm still DRY in the model as I borrowed the T2 idea of defining the
common fields once & reusing).

However I need a different solution for the Person resource.
A Person can be any of many & multiple contexts:
* Contact for an Organisation (or Office) with a 'title' relevant to
that context (could have different titles in different contexts)
* Role for administering data relevant to an Organisation (or Office,
or Country, or Region)
* Volunteer
* Donor
* Victim (Missing/Dead/Beneficiary)
* Public (relative/friend wanting to report or check on status of a
missing person)

Ideas for how to model this environment are very welcomed :)

I have concerns around the scalability of the tagging-style
multiple=True fields (which aren't currently working for me anyway -
just producing SELECT...no MULTIPLE) when there are a huge number of
options to choose from.

The low developer effort on maintenance is a key requirement since the
application is often rapidly-customised in the field...this isn't a
single website which is polished hard & maintained by a small team of
people who know the system intimately.

Best Wishes,
Fran.

mdipierro

unread,
Jan 14, 2009, 12:00:15 PM1/14/09
to web2py Web Framework
about
> 1. Have a single table with fields for all potential cases
> - this means the developer can use standard T2 CRUD functions (low
> maintenance) & then tweak using jquery to hide/show fields as they
> become relevant or not (extra maintenance here).

not necessarily. SQLFROM(,...fields=[...]) left you specify with
fields to display in forms.
in T2 you just set db.table.exposes=[...] list of fields.

There is possibly a better way in trunk

db.table.field.writable=False (it will disable the field from all
forms)

Massimo

Fran

unread,
Jan 14, 2009, 2:27:36 PM1/14/09
to web2py Web Framework
On Jan 14, 5:00 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > 1. Have a single table with fields for all potential cases
> > - this means the developer can use standard T2 CRUD functions (low
> > maintenance) & then tweak using jquery to hide/show fields as they
> > become relevant or not (extra maintenance here).
> not necessarily. SQLFROM(,...fields=[...]) left you specify with
> fields to display in forms.
> in T2 you just set db.table.exposes=[...] list of fields.

Right - I use this already...but if the fields to display/expose vary
depending on selection options, then that needs to be done client-side
=> JS (unless you want to have a new page brought up after each option
which is slow & old-style)

> There is possibly a better way in trunk
> db.table.field.writable=False (it will disable the field from all
> forms)

I agree that this seems easier to maintain as usually it will be just
a few fields to hide & you don't want to have to adjust the exposes/
displays each time you add a new field.

However it doesn't help with this issue at all, I don't think.

Best Wishes,
Fran.

Bill Thayer

unread,
Oct 3, 2012, 3:45:59 PM10/3/12
to web...@googlegroups.com
Fran,
Hi Fran,

It's been a while since you posted this but I'd like to know which solution you chose.  Sounds like you have much more experience than I have and what you described above (the options to have the user see all relevant fields in a single form) was exactly my experience on a few all nighters..I guess the T2 approach is the way to go for me now,

Please reply.

Regards,
Bill
Reply all
Reply to author
Forward
0 new messages