tricky problem :-) - self references & direction

[rittersporn]

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

[Allen Browne]

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.

<ri*********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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

[Allen Browne]

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" <Al*********@SeeSig.Invalid> wrote in message
news:42***********************@per-qv1-newsreader-01.iinet.net.au...

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

<ri*********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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

[rittersporn]

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