Re: [Modeling-users] Re: attaching information to a relation

[Mario R. <ma...@ru...>]

On Jeudi, ao=FB 7, 2003, at 01:38, Mario Ruggier wrote:
> On Mardi, ao=FB 5, 2003, at 00:01, Sebastien Bigaret wrote:
>>
>>         Hi Mario,
>>
>> Mario Ruggier <ma...@ru...> wrote:
>>> On Lundi, ao=FB 4, 2003, at 20:49, Sebastien Bigaret wrote:
>>>> Mario wrote:
>>>>> just wondering how this can be best done... as a simple example,
>>>>> assume a Person and an Address tables. A Person may have
>>>>> more than one address, but each address may be categorized
>>>>> as being "primary", "secondary", "private", "work", or anything
>>>>> a priori unknown, Where does one best stick this information?
>>>>
>>>> I think this is what uml calls a "qualified association".
>>>
>>> Thanks for the explanation(s) below...
>>> This would work just fine, if an Address belongs (exclusively)
>>> to any one Person. What I am looking for is a way to reuse
>>> the same Address for different people, but the type of
>>> relation may be different each time. E.g.
>>>
>>> Mr. Dough's lives in a Villa, across from the beach (private =
Address)
>>> Ladi Lean is the housemaid for Mr. Dough (work Address)
>>> Mr. Dough's little sister spends summers at his villa (secondary=20
>>> Address)
>>> ...
>>>
>>> The most natural thing to me would be to add a "property"
>>> to the association, if that'd be allowed. I guess one would have
>>> to make an addditional association table for this?
>>
>> Okay, I didn't understand you properly. Now it's clear, you need
>> qualified many-to-many associations. Here is a draft guideline for
>> modeling a many-to-many relationship:
>
> Very nice, thanks a lot. The association table is easy enough to
> create (especially with the wonderfully expressive PyModel
> Association statement). Thanks also for the sample code
> to do the important manipulations. I will let you know what i
> discover when doing such things... (have not played
> with this part yet...). It would also certainly be great
> to have a test/howto for this.
>
> I have a question here: is it a good idea to re-use such
> assoc tables to relate in a many-2-many way any arbitrary
> pair of tables? An m2m relationship with a type seems to
> me a frequently occurring situation, and it seems like a lot
> of overhead to declare a dedicated assoc table per relation.
> As an example, consider:
>
>   Entity('User',
>     properties=3D[
>       AString('firstname',isRequired=3D1),
>       AString('lastname',isRequired=3D1),
>       ...
>     ]
>   ),
>
>   Entity('Address',
>     properties=3D[
>       AString('street'),
>       ...
>     ]
>   ),
>
>   Entity('Organization',
>     properties=3D[
>       AString('name',isRequired=3D1),
>       AString('type',isRequired=3D1),
>     ],
>   ),
>
>   Entity('ManyToMany_Type',
>     properties=3D[
>       AString('type'),
>     ]
>   ),
>
>
>  Association('User','ManyToMany_Type',
>     relations=3D['addresses','user'],
>     delete=3D['cascade','nullify'],
>     keys=3D['FK_Address_id','id'] # what should this fk be named?
>   ),
>
>   Association('Address','ManyToMany_Type',
>     relations=3D['users','address'],
>     delete=3D['cascade','nullify'],
>     keys=3D['FK_User_id','id'] # what should this fk be named?
>   ),
>
>  Association('User','ManyToMany_Type',
>     relations=3D['organizations','user'],
>     delete=3D['cascade','nullify'],
>     keys=3D['FK_Organization_id','id'] # what should this fk be named?
>   ),
>
>   Association('Organization','ManyToMany_Type',
>     relations=3D['users','organization'],
>     delete=3D['cascade','nullify'],
>     keys=3D['FK_User_id','id'] # what should this fk be named?
>   ),
>
> This, the same assoc table is used for the m2m relations
> between User<>Address and User<>Organization.
> Is this a good way?

I should have played with the above case a little more, as
it will not work like this -- because this will attempt to create
two relations on the ManyToMany_Type (now TypedAssoc) table,
that are both named 'user'. The mdl_validate error given is:

ValueError: Attempt to insert a relationship in entity TypedAssoc but a=20=

key named 'user' is already registered.

However, if i change the name of the relation on the Assoc table
(e.g. following a convention such as source_target), then the
db is created ok. Thus the assocs will change to:

   Association('User','TypedAssoc',
     relations=3D['organizations','user_organization'],
     delete=3D['cascade','nullify'],
     keys=3D['FK_Organization_id','id'],
   ),
   Association('Organization','TypedAssoc',
     relations=3D['users','organization_user'],
     delete=3D['deny','nullify'],
     keys=3D['FK_User_id','id'],
   ),
   Association('User','TypedAssoc',
     relations=3D['addresses','user_address'],
     delete=3D['cascade','nullify'],
     keys=3D['FK_Address_id','id']
   ),
   Association('Address','TypedAssoc',
     relations=3D['users','address_user'],
     delete=3D['deny','nullify'],
     keys=3D['FK_User_id','id']
   ),

However, I am surprised that in the schema dump for the
created sqlite database, none of these relation names
(user_address, etc) show up! How does he know about
them when re-creating the db from the schema dump
(which he seems to do just fine) ?

> Cheers, mario