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

[Sebastien B. <sbi...@us...>]

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
>=20
> 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.
>=20
> 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:
>=20
> 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 Address(ID=
),
>    CONSTRAINT organization4user FOREIGN KEY (FK_ORGANIZATION_ID) REFERENC=
ES
> Organization(ID),
>    CONSTRAINT user4address FOREIGN KEY (FK_ADDRESS_ID) REFERENCES User(ID=
),
>    CONSTRAINT user4organization FOREIGN KEY (FK_USER_ID) REFERENCES User(=
ID))
>=20
> 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),

  2. constraints are not ok, I'd write:

    CONSTRAINT address4user FOREIGN KEY (FK_ADDRESS_ID) REFERENCES Address(=
ID),
    CONSTRAINT organization4user FOREIGN KEY (FK_ORGANIZATION_ID) REFERENCES
         Organization(ID),
    CONSTRAINT user4address FOREIGN KEY (FK_USER_ID) REFERENCES User(ID),
    CONSTRAINT user4organization FOREIGN KEY (FK_USER_ID) REFERENCES User(I=
D))

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.

> Question: why is the order in an association important?
> Or, why does switching the assoc around to this:
>=20
>       Association('User','TypedAssoc',
>         relations=3D['typed_assocs','user'],
>         delete=3D['cascade','nullify'],
>         keys=3D['id','FK_User_id']
>       ),
>=20
> give the following error:
>=20
> ValueError: Association(User,TypedAssoc): specifies a destination attribu=
te
> FK_User_id for entity TypedAssoc but this entity already has a PK named id

Handling many-to-many relationships is equivalent to working with
couples (user_id, address_id) that works together. That's what the
correlation tables does.

  If I re-read your previous model, you were trying to model something
  like:

     User <<-----> TypeAssoc <<----> Address


Important: remember that Association(E1, E2) means E1 <<----> E2

  ---> this does not model many-to-many, since one of the resulting
       rel. is then: User --> Address.


Last: the reason why you get the error above is that you declare a the
following relationship: User <<----> TypeAssoc, which is modeled with a
FK in User linked to a PK in TypeAssoc. Since TypeAssoc already has a pk
'id', trying to build this association makes the pymodel adds a pk named
'FK_User_id' in TypedAssoc --and since compound pk are not supported,
you get the error.=20

  But, independently to the support of compund PKs, your declaration of
  the associations is wrong (the order is wrong), this can be seen of
  the rels' names: the to-one is called 'typed_assocs' while the to-many
  is called 'user'.

The problem is that you were probably misled here by the names: since
you read 'typed_assocs' and 'user', you tend to think that associations
are resp. to-many and to-one, and since the cardinalities are implicit
here, that makes it harder to see the problem. In fact,=20

       Association('User','TypedAssoc',
         relations=3D['typed_assocs','user'],
         delete=3D['cascade','nullify'],
         keys=3D['id','FK_User_id']

is equivalent to (see Association's defaults):

       Association('User','TypedAssoc',
         relations=3D['typed_assocs','user'],
         multiplicity=3D[ [0,1], [0,None] ],
         delete=3D['cascade','nullify'],
         keys=3D['id','FK_User_id']

  and this version makes the pb appears clearly, doesn't it? ;)

-- S=E9bastien.