Re: Hierarchical Relationship

In article <89liln$7h0$1_at_news1.xs4all.nl>, "John Smith" <someone_at_microsoft.com> wrote:
> Hi,
>
> I am trying to make a Familytree in Access.
> In simplified form I have made
> 2 tables: TblPerson and TblParents
> and 1 Querie: QryPerson&Parents.
>
> In TblPerson I have the basic properties of a person (ID, Name, sexe
 etc.).
> In TblParents I have 3 fields: Person_ID, Father_ID and Mother_ID
>
> TblParents is linked 1-to-1 with TblPerson by [TblPerson].[ID] and
> [TblParents].[Person_ID].
> The other 2 fields (Father_ID and Mother_ID) are fields that look up
 their
> values in TblPerson.
>
> QryPerson&Parents joins the 2 tables so that I get 1 recordset of a
 person
> including who his parents are.
>
> So far so good but there is one problem. I'd like to build in some
> validation. For example; one should not be able to select a distant
> offspring or a distant ancestor as a parent.
>
> I have 2 questions:
> -Is the method that I used any good or does somebody know a better
 solution
> to the problem.
> -Can somebody help me out with the validationrules.
>
> Corno Schraverus
>
>

Corno,

Have you considered the possibility that a person can have more than two parents over time (e.g. can be adopted)? This could be resolved by having a TBL_INTERPERSONAL_RELATIONSHIPS table instead of your TBL_PARENTS. That way you could specify the various other relationships between two people (e.g. marriage, grandparents, cousins, etc) and introduce validation restricting the selections you mention. This might be a bit of overkill though.

Don't forget to prevent a person being their own parent...

Cheers,

Tim

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 22 2000 - 00:00:00 CET