how to handle children of parents in contacts table


I

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.
 
Ad

Advertisements

J

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.
 
Ad

Advertisements

I

icccapital

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top