Re: [SQLObject] Join tables

[Andy T. <an...@ha...>]

Ian Bicking wrote:

> On Nov 10, 2003, at 2:42 PM, John Baker wrote:
> 
>> class Area (SQLObject):
>>
>> class Office (Area):
>>
>> class Home (Area):
>>
>> SQLObject creates three tables, with a foreign key from Home -> Area and
>> Office -> Area.
>>
>> This means I can do:
>>
>> class Person (SQLObject):
>>   areas = ReferenceJoin("Area"...)
>>
>> and map a Person to a number of Areas, without having to map him to 
>> Office and
>> Home via two join tables.
>>
>> Someone said superset mapping wouldn't be happening, which is a shame, 
>> as it's
>> the most obvious and logical choice of the majority of OO mapping.
> 
> 
> That was probably me.  I find it difficult to map between class 
> hierarchies and tables, and I don't like the ambiguity or arbitrariness 
> of how that mapping has to happen.
> 
> Which isn't to say I'd be opposed to superset mapping, I'd just rather 
> that it not look like Python inheritance.  To me it's more of an 
> implicit join.  Or the folding together of multiple tables.  Or... I 
> don't know.  When I see a metaphor that looks better, and hopefully 
> doesn't involve terms (or even concepts) like "implicit" or "folding", 
> then maybe I'll feel more enthusiastic.  I'm trying to avoid magic to 
> the degree possible, which is where my reluctance comes from.
> 
> -- 
> Ian Bicking | ia...@co... | http://blog.ianbicking.org
> 

In relational data modelling this is implemented via an 'arc' 
relationship. At the logical level the relationship can be to either the 
supertype (area) or one of its subtypes (home or office).

When this is translated to a physical model you, usually, end up with 
one table with the same name as the super type. This table then has a 
'type' column which indicates what sort of sub-type it is. Then all of 
the foreign keys can be to the super type table and any further 
restrictions (e.g. this fk is only for this specific sub-type) can be 
enforced with constraints.

Unless, of course, you do it the other way round ;-) But implementing 
each of the subtypes as its own table brings many more problems (as 
mentioned by the op) and is only really a good idea when the sub types 
have very little in common - in which case are they really sub types?

All of which is fine until you introduce objects, with their 
associations and inheritance which are just about close to what I've 
described, but not quite close enough. So how this should work in an 
object relational mapper I leave to more experienced heads.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/