Uniqueness of pair

[MDS]

All,

I am endeavouring to implement an "Also in the area" feature to an
Access 97 DB.

Within the table, there are two columns drawn from the same domain -
let's call them Place A and Place B. Place A and Place B form the
composite primary key.

When the user enters "Place A" is in the area of "Place B," it should
not be necessary to insert a second record stating "Place B" is in the
area of "Place A" as this can be infered from the first statement.

Therefore, the initial A/B entry should also reflect the uniqueness of
A/B as, in effect, an unordered pair.

Can this be implemented in Access? If so, how?

Thanks for your help in this,
Mark

[Steve Jorgensen]

That's actually not a trivial problem in database design.

Because I like to show off, I'll first present the complicated answer that is
most "right" in the clever sense of the word, though rarely the most practical
answer. This answer is in terms of places and vicinities.

tblPlace
* PlaceId Autonumber
PlaceName Text(45)

tblPlaceRelation
* PlaceRelationId Autonumber
MilesApart Double

tblPlaceRelationLink
* PlaceRelationId Long
* PlaceId Long
LinkNum Byte

Now, also create a unique index on tblPlaceRelationLink comprising
PlaceRelationId and LinkNum, and make a constraint on LinkNum only allowing
values 1 or 2. The code that creates the 2 tblPlaceRelationLink records for a
relation must assign LinkNum 1 to one record, and 2 to the other (it doesn't
matter which is which).

Notice that, if it weren't for the LinkNum value, there would be no difference
between the relationships between "Place A" to "Place B" and "Place B" to
"Place A", and the only purpose of LinkNum is so its rules enforce that only 2
places can be associated in a single relation. You can query all the
vicinity-relatives of a place using a query something like the following:

SELECT PLACE2.*, tblPlaceRelation.MilesApart
FROM ((( tblPlace AS PLACE1
INNER JOIN tblPlaceRelation AS PLACE_REL_LINK1
ON PLACE1.PlaceId = PLACE_REL_LINK1.PlaceId
) INNER JOIN tblPlaceRelation AS PLACE_REL_LINK2
ON PLACE_REL_LINK1.PlaceRelationId =
PLACE_REL_LINK2.PlaceRelationId
) INNER JOIN tblPlace AS PLACE2
ON PLACE_REL_LINK2.PlaceId = PLACE2.PlaceId
) INNER JOIN tblPlaceRelation
ON PLACE_REL_LINK1.PlaceRelationId =
tblPlaceRelation.PlaceRelationId
WHERE PLACE1.PlaceId=[startfromplaceid] AND
PLACE2.PlaceId <> PLACE1.PlaceId

Yup - it's kinda ugly. Also, this schema has no way of enforcing that all the
records comprising a 2-way relationship are created, so it's possible to have
1/2 a relationship defined if a code error occurs. Each possible alternative
schema has its own kind of ugliness, though.

Now, a suggestion that might actually be useful <g>:

One of the more practical alternatives is to use a simple junction table, but
always create a matching entry going in the opposite direction to any relation
created. The application code (or possibly a trigger if you're using a server
database) must keep any data about the relation (such as distance in this
case) in sync between the 2 complimentary junction records or have some
hueristic to decide which of the 2 records is treated as the official junction
data container.

On Tue, 5 Apr 2005 23:09:33 +0200, "MDS" <md*********@yahoo.co.uk> wrote:

All,

I am endeavouring to implement an "Also in the area" feature to an
Access 97 DB.

Within the table, there are two columns drawn from the same domain -
let's call them Place A and Place B. Place A and Place B form the
composite primary key.

When the user enters "Place A" is in the area of "Place B," it should
not be necessary to insert a second record stating "Place B" is in the
area of "Place A" as this can be infered from the first statement.

Therefore, the initial A/B entry should also reflect the uniqueness of
A/B as, in effect, an unordered pair.

Can this be implemented in Access? If so, how?

Thanks for your help in this,
Mark