how to handle children of parents in contacts table

[icccapital]

I am trying to create a personal contacts table to teach myself access. I
have worked with databases before so understand relational databases to a
degree.

What I am stuck on is what is the normal method for handling children of my
friends in the table structure.

Have you seen people have a seperate children table that relates the ID of
the parent to the child or just have multiple children fields in the parents
(friend) table and just hope I crate enough?

Thanks for the thoughts and new ideas.

 

[John W. Vinson]

I am trying to create a personal contacts table to teach myself access. I
have worked with databases before so understand relational databases to a
degree.

What I am stuck on is what is the normal method for handling children of my
friends in the table structure.

Have you seen people have a seperate children table that relates the ID of
the parent to the child or just have multiple children fields in the parents
(friend) table and just hope I crate enough?

Neither.

A child is a person; a child may even have children. Fields are expensive,
records are cheap - you need a "Self Join"!

A person may have zero, one or more children; a person will have two parents,
who may or may not be represented in the people table - but they are all
people.

You can include a FatherID and MotherID field in the contacts table; they
should not be required (because the parent may not be represented in the
table), but if they do exist they would contain a link (a "self join") to the
primary key ContactID of the table.

You can create a query joining the table to itself, twice:

SELECT P.LastName, P.FirstName, <other fields>,
F.Lastname AS FatherLast, F.FirstName AS FatherFirst,
M.Lastname AS MotherLast, M.FirstName AS MotherFirst
FROM (Contacts AS P Left Join Contacts AS F ON P.FatherID = F.ContactID)
LEFT JOIN Contacts AS M ON P.MotherID = M.ContactID;

The left joins allow for the possibility that the parent record may not exist.

 

[icccapital]

Thank you, I knw I was missing something, so that helps a lot. Thanks for
the time