creating reflexive or unary relationship

[RichardO]

I know they are rare; but say you have a "is married to" relationship between two instances of a tblPERSON. I have a PersonID and Spouse attributes in tblPERSON. In Relationship view of Access, I cannot drag PersonID to Spouse to form a relationship

My workaround is to add another tblPERSON_1 to the Relationship view and create the relationship between the two tables in the normal manner. However, this creates a one-to-many relationship -- something frowned upon in most parts of the United States

Access Help indicates it is possible to create this reflexive relationship (also called unary relationship in textbooks). But it does not provide a description on how to do so. Is there a way to create a one-to-one reflexive relationship? If so, what is that way

Thanks, in advance
Richar
orwi
a
peoplep
do
com

 

[david epsom dot com dot au]

However, this creates a one-to-many relationship -- something frowned upon

It doesn't do that to me: I have a unique index on the spouse field.

(david)

I know they are rare; but say you have a "is married to" relationship

between two instances of a tblPERSON. I have a PersonID and Spouse
attributes in tblPERSON. In Relationship view of Access, I cannot drag
PersonID to Spouse to form a relationship.

My workaround is to add another tblPERSON_1 to the Relationship view and

create the relationship between the two tables in the normal manner.
However, this creates a one-to-many relationship -- something frowned upon
in most parts of the United States.

Access Help indicates it is possible to create this reflexive relationship

(also called unary relationship in textbooks). But it does not provide a
description on how to do so. Is there a way to create a one-to-one
reflexive relationship? If so, what is that way?

 

[Chris]

Yes, you can do this.
Lets say you have a table with fields
PersonID, type = long, Primary key
PersonName, type = text
SpouceID, type long, default value left empty,Required = no

If you are doing this in an existing table, ensure that
SpouceID = null if not used (don't set to 0)

In the index window, create an index called "Spouce"
(primary = no, unique = yes, Ignore nulls = yes)

Now, in the relationships window, insert two copies of the
table, (as you describe), then drag PersonId from either
table to SpouceID in the other, and tick "Enforce ref
integrity".

-----Original Message-----
I know they are rare; but say you have a "is married to"

relationship between two instances of a tblPERSON. I have
a PersonID and Spouse attributes in tblPERSON. In
Relationship view of Access, I cannot drag PersonID to
Spouse to form a relationship.

My workaround is to add another tblPERSON_1 to the

Relationship view and create the relationship between the
two tables in the normal manner. However, this creates a
one-to-many relationship -- something frowned upon in most
parts of the United States.

Access Help indicates it is possible to create this

reflexive relationship (also called unary relationship in
textbooks). But it does not provide a description on how
to do so. Is there a way to create a one-to-one reflexive
relationship? If so, what is that way?

 

[John Nurick]

Hi Richard,

You can outlaw polygamy by putting a unique index on the Spouse field,
so no two Persons can have the same Spouse.

Same-sex marriages would however be allowed. And it would be possible
for Person A to have Person B as Spouse, while Person B has Person C as
Spouse, which might be great fun but isn't allowed in the real world.

In fact I don't think it's possible using Access table-level validation
alone to enforce monogamy and prohibit (if you want to) same-sex
marriage. Can be done with triggers or code behind forms, of course.

You might do better to have a separate tblRelationships:

Man - FK related 1:1 to tblPersons.PersonID, sex of Person must be M
Woman - FK related 1:1 to tblPersons_1.PersonID, sex must be F
RelationshipType - e.g. married, posslq

I know they are rare; but say you have a "is married to"

relationship between two instances of a tblPERSON. I have a PersonID
and Spouse attributes in tblPERSON. In Relationship view of Access, I
cannot drag PersonID to Spouse to form a relationship.

My workaround is to add another tblPERSON_1 to the Relationship

view and create the relationship between the two tables in the normal
manner. However, this creates a one-to-many relationship -- something
frowned upon in most parts of the United States.

 

[John Vinson]

I know they are rare; but say you have a "is married to" relationship between two instances of a tblPERSON. I have a PersonID and Spouse attributes in tblPERSON. In Relationship view of Access, I cannot drag PersonID to Spouse to form a relationship.

My workaround is to add another tblPERSON_1 to the Relationship view and create the relationship between the two tables in the normal manner. However, this creates a one-to-many relationship -- something frowned upon in most parts of the United States.

<g> Put a unique index on Spouse (if you're willing to exclude my
friends k and c and their husbands b and r and w from your database).

The gimmick of adding another instance of tblPerson is just the way
that you need to do this in the relationships window.

 

[Tim Ferguson]

I know they are rare; but say you have a "is married to" relationship
between two instances of a tblPERSON. I have a PersonID and Spouse
attributes in tblPERSON. In Relationship view of Access, I cannot
drag PersonID to Spouse to form a relationship.

You need a table called Spouses as follows:

CREATE TABLE Spouses (
MarriageID LONGINT NOT NULL, // identify each marriage
Gender CHAR(1) NOT NULL
CONSTRAINT LegalGender Gender IN ('m','f'),
// need to check syntax there
PersonID LONGINT NOT NULL
CONSTRAINT GotWedded FOREIGN KEY REFERENCES People
// obvious

CONSTRAINT pk PRIMARY KEY (MarriageID, Gender),
CONSTRAINT monogamy UNIQUE INDEX (PersonID),

CONSTRAINT iswedded FOREIGN KEY MarriageID REFERENCES Weddings

)

The combined PK of (marriage, gender) ensures that each marriage is
heterosexual, and the unique index on personid ensures that each person
only participates in one marriage. The Weddings table would keep details of
WeddingDate, NumberOfChildren, FightsPerWeek and so on.

The biggest problem is ensuring that Spouses(PersonID, Gender) matches
People(PersonID, Gender), and in fact this table is not strictly in second
normal form. On the other hand, update anomalies would be limited by the
fact that sex-changes prior to marriage are so rare that they would need
special handling within the database anyway.

Just a thought


Tim F

 

[david epsom dot com dot au]

In fact I don't think it's possible using Access table-level validation

alone to enforce monogamy and prohibit (if you want to) same-sex
marriage. Can be done with triggers or code behind forms, of course.

Can't be enforced because the data is de-normalised: you have relationship
declared twice: husband of wife, and wife of husband. If you wish to add
additional constraint (spouse of person = spouse of (spouse of spouse), you
should normalise the data for that constraint.


(david)

Hi Richard,

You can outlaw polygamy by putting a unique index on the Spouse field,
so no two Persons can have the same Spouse.

Same-sex marriages would however be allowed. And it would be possible
for Person A to have Person B as Spouse, while Person B has Person C as
Spouse, which might be great fun but isn't allowed in the real world.

In fact I don't think it's possible using Access table-level validation
alone to enforce monogamy and prohibit (if you want to) same-sex
marriage. Can be done with triggers or code behind forms, of course.

You might do better to have a separate tblRelationships:

Man - FK related 1:1 to tblPersons.PersonID, sex of Person must be M
Woman - FK related 1:1 to tblPersons_1.PersonID, sex must be F
RelationshipType - e.g. married, posslq






relationship between two instances of a tblPERSON. I have a PersonID
and Spouse attributes in tblPERSON. In Relationship view of Access, I
cannot drag PersonID to Spouse to form a relationship.
view and create the relationship between the two tables in the normal
manner. However, this creates a one-to-many relationship -- something
frowned upon in most parts of the United States.

relationship (also called unary relationship in textbooks). But it does not
provide a description on how to do so. Is there a way to create a
one-to-one reflexive relationship? If so, what is that way?