tricky problem :-) - self references & direction

I want to model relations between "Groups" and
also annotate the relations!

My solution so far:

Group
- GroupName : Text | PRIMARY_KEY
- Titel : Text
- Info : Memo

Group2Group
- GroupName1 : Text | PRIMARY_KEY
- GroupName2 : Text | PRIMARY_KEY
- Relation : Memo

Now I connect Group.GroupName to Group2Group.GroupName1 and
Group_1.GroupName to Group2Group.GroupName2 !

Problem: I want _only_ one record for _each_ relation and
_not_ for every direction.
(Depends on who is GroupName1 or GroupName2, if
you swap them you get two distinct records, not
one :-(

Thanks for your ideas!

Ciao,
Rittersporn

re: tricky problem :-) - self references & direction

The structure you have works for directional relationships, e.g. where
GroupName1 represents a parent in the relation, and GroupName2 is the child.

If the relation is non-directional (reflex), it might be better to specify
the members of the group as multiple records in the members table, instead
of as multiple repeating fields:

Group
- GroupName : Text | PRIMARY_KEY
- Titel : Text
- Info : Memo

GroupMember
- GroupName : Text | PRIMARY_KEY
- Relation : Memo

It's not a simple situation with a one-size-fits-all answer, but there's a
very flexible structure suggested in this article:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<rittersporn@gmail.com> wrote in message
news:1123522731.915255.327030@g47g2000cwa.googlegr oups.com...[color=blue]
>I want to model relations between "Groups" and
> also annotate the relations!
>
> My solution so far:
>
> Group
> - GroupName : Text | PRIMARY_KEY
> - Titel : Text
> - Info : Memo
>
> Group2Group
> - GroupName1 : Text | PRIMARY_KEY
> - GroupName2 : Text | PRIMARY_KEY
> - Relation : Memo
>
> Now I connect Group.GroupName to Group2Group.GroupName1 and
> Group_1.GroupName to Group2Group.GroupName2 !
>
> Problem: I want _only_ one record for _each_ relation and
> _not_ for every direction.
> (Depends on who is GroupName1 or GroupName2, if
> you swap them you get two distinct records, not
> one :-(
>
> Thanks for your ideas!
>
> Ciao,
> Rittersporn[/color]

re: tricky problem :-) - self references & direction

Sorry, that GroupMember tab le should have had the parent group and the
child member:

GroupMember:
ParentGroup Foreign key to Group.GroupName
GroupMember Also foreign key to Group.GroupName

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:42f7fb71$0$32236$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> The structure you have works for directional relationships, e.g. where
> GroupName1 represents a parent in the relation, and GroupName2 is the
> child.
>
> If the relation is non-directional (reflex), it might be better to specify
> the members of the group as multiple records in the members table, instead
> of as multiple repeating fields:
>
> Group
> - GroupName : Text | PRIMARY_KEY
> - Titel : Text
> - Info : Memo
>
> GroupMember
> - GroupName : Text | PRIMARY_KEY
> - Relation : Memo
>
> It's not a simple situation with a one-size-fits-all answer, but there's a
> very flexible structure suggested in this article:
> People in households and companies - modelling human relationships
> at:
> http://allenbrowne.com/AppHuman.html
>
> <rittersporn@gmail.com> wrote in message
> news:1123522731.915255.327030@g47g2000cwa.googlegr oups.com...[color=green]
>>I want to model relations between "Groups" and
>> also annotate the relations!
>>
>> My solution so far:
>>
>> Group
>> - GroupName : Text | PRIMARY_KEY
>> - Titel : Text
>> - Info : Memo
>>
>> Group2Group
>> - GroupName1 : Text | PRIMARY_KEY
>> - GroupName2 : Text | PRIMARY_KEY
>> - Relation : Memo
>>
>> Now I connect Group.GroupName to Group2Group.GroupName1 and
>> Group_1.GroupName to Group2Group.GroupName2 !
>>
>> Problem: I want _only_ one record for _each_ relation and
>> _not_ for every direction.
>> (Depends on who is GroupName1 or GroupName2, if
>> you swap them you get two distinct records, not
>> one :-(
>>
>> Thanks for your ideas!
>>
>> Ciao,
>> Rittersporn[/color][/color]

re: tricky problem :-) - self references & direction

Hi Allen

thank you very much for your help. I still use
a typical m:n mapping.

i) As a convention I assign GroupName1 the "smaller"
value. Well, I compare Name1 < Name2 and swap
if necessary before assignment.

This is just to prevent "double" entries.

ii) To fill a subform with the relations I use:

SELECT GroupName2,Comment FROM tblGroup2Group
WHERE GroupName1="The xyz Group"
UNION SELECT GroupName1,Comment FROM tblGroup2Group
WHERE GroupName2="The xyz Group";

So basically I can do want I want, I just have
to use some more Visual Basic "magic" instead
of (just) the wizard to fill out forms and insert
or delete entries.

The creation of "meta"-tables or defining relations
as table entries leads to madness ;-)

Ciao,
Rittersporn