Another Relationship Question

[damsel]

I am designing a database for a kindergarten. I have a question as a newbie.

I'm to record ParentA and ParentB and Child information. I make 3 tables for them respectively. The question is what kind of relationship should I assign to them?
I noticed most of the family are single family. So I assigned ParentA one-to-Many Relationship to Child table. I assigned ParentA as a One-to-One Relationship to ParentB.
But what if the family are not single-family (Parent A and Parent B live in the same household), would that mean Child has Many-to-Many Relationship with ParentA and Parent B?
A note that a family might have more than 1 child enrolled at school.

Thank's a bunch again everybody.

 

[DBL]

What I think you want is a record for each child showing any related parents, even if the child has siblings. You will need three tables, one for the child details, one for the parent details (regardless of how many parents) and a join table that links the two together in a many to many relationship.

Child table
ChildID (autonumber) - primary key field
other fields

Parent table
ParentID (autonumber) - primary key field
other fields

JoinTable
RecordID (autonumber) - primary key field
ChildID
ParentID

Join the child table from ChildID to ChildID in the join table as a one to many relationship. Join the parent table from parentID to ParentID in the join table as a one to many relationship.

When you create a form, create the main form for the child's details. You then create a subform what shows the parent's details - as you can have more than one parent for each child. If you use the form wizard to create the form it should do the main form and subform for you.

HTH

 

[The_Doc_Man]

Technically, you can do this one in TWO tables. However, you could do it in three if you wanted to.

The two-table approach...

Table Persons:
Autonumber ID
PFirst, PMid, PLast - person's first, middle, last name
IsStudent? Yes/No
other things you need to track

Table PersonRel
ID of person
ID of related person
Nature of relation (Mother, Father, Ugly Step Uncle, Legal Guardian, Fairy Godmother...)

OK, the trick is, when you design the PersonRel table, you have to link it TWICE to Persons. You do this by adding the Persons table.... then adding it again.... to whatever screen you need this defintion. The second instance will be named PERSON1 in the window where it appears. But it is really the same table.

Of course, you will NEVER create a relationship of a person to him/herself. Also, there is no particular need to define a Child relationship to the parents in this table, though you could if you wanted to.

Then you drive reports off the PersonRel table linked to the Person table where you group by [ID of Person] and show the rows for [ID of Related Person] as detail rows.

To find a child's mother, the query is to find the PersonRel row with the Child as the [ID of Person] and relationship "Mother" - and voila, you get the [ID of Related Person].