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

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

On Samedi, ao=FB 9, 2003, at 14:32, Sebastien Bigaret wrote:
> Mario Ruggier <ma...@ru...> wrote:
>>>    In fact my previous post was about something like this:
>>>
>>>      Association('TypedAssoc','User',
>>>        relations=3D['user','typed_assocs'],
>>>        delete=3D['nullify','cascade'],
>>>        keys=3D['FK_User_id','id']
>>>      ),
>>>      Association('TypedAssoc','Address',
>>>        relations=3D['address','typed_assocs'],
>>>        delete=3D['nullify','cascade'],
>>>        keys=3D['FK_Address_id','id']
>>>
>>>    i.e.  User <----->> TypedAssoc <<-----> Address
>>
>> Hmmn, it seems I have switched the logic around.
>> In this way the "user" and "address" relations show up as constraints
>> on the TypedAssoc table.
>>
>> But, the problem with this is that if we re-use TypedAssoc
>> also for a similar association between User and Organization
>> (using this naming strategy) then there will be the ValueError
>> mentioned above. Hence, using a  <source4target> naming
>> scheme for these relations, and adding the ones for organization,
>> we end up with a TypedAssoc definition of:
>>
>> CREATE TABLE TypedAssoc (
>>    FK_USER_ID INTEGER ,
>>    FK_ORGANIZATION_ID INTEGER ,
>>    TYPE VARCHAR(30) ,
>>    ID INTEGER NOT NULL PRIMARY KEY,
>>    FK_ADDRESS_ID INTEGER ,
>>    CONSTRAINT address4user FOREIGN KEY (FK_USER_ID) REFERENCES=20
>> Address(ID),
>>    CONSTRAINT organization4user FOREIGN KEY (FK_ORGANIZATION_ID)=20
>> REFERENCES
>> Organization(ID),
>>    CONSTRAINT user4address FOREIGN KEY (FK_ADDRESS_ID) REFERENCES=20
>> User(ID),
>>    CONSTRAINT user4organization FOREIGN KEY (FK_USER_ID) REFERENCES=20=

>> User(ID))
>>
>> Is this correct? This will rule out re-use of such a table...
>
> I *think* this should work (even if untested, I've never gathered all
> correlation tables into a single one. However I feel that some things
> still needs to be fixed:
>
>   1. there's a missing name for TYPE VARCHAR(30),

The name is "Type"... What type of name would you like ;-?

>   2. constraints are not ok, I'd write:
>
>     CONSTRAINT address4user FOREIGN KEY (FK_ADDRESS_ID) REFERENCES=20
> Address(ID),
>     CONSTRAINT organization4user FOREIGN KEY (FK_ORGANIZATION_ID)=20
> REFERENCES
>          Organization(ID),
>     CONSTRAINT user4address FOREIGN KEY (FK_USER_ID) REFERENCES=20
> User(ID),
>     CONSTRAINT user4organization FOREIGN KEY (FK_USER_ID) REFERENCES=20=

> User(ID))

This was my mistake, in the pymodel...

> Last, the correlation tables has now 3 FKs: FK_USER_ID, FK_ADDRESS_ID
> and FK_ORGANIZATION_ID. It should work, given that each row in
> TypedAssoc only has two non null FKs.

Such constraints (must have 2 and only 2 non-null FKs) can only be
checked in python code (not in the model) ?

(Repeated question) Even if it is possible to do such a thing, is it a=20=

good
idea to re-use such a TypedAssoc table, or should a dedicated one be
made for each association? In addition to the constraint mentioned=20
above,
there is the issue of scalability -- that a table with less cols and=20
more rows
scales better than a table with more cols and less rows...

>> Question: why is the order in an association important?
[snip]

Thanks for the reminder/clarification of the multiplicity defaults on=20
Assocations...

mario