Student-Parent link (Not Ozzie and Harriet Anymore)

[rpadams]

I am working on a school database which has two tables (tblStudents and tblParents). What I need is a field to link each student to his parents and each parent to their child. BUT, it's no longer one set of parents to a set of children. Many children have two (or more with foster care and/or grandparent) distinct parents and different addresses and there seems to be every permutation possible of siblings with different parents. I eventually need to sort by each child and list all his parents and siblings OR sort by each parent and collect all the children.

My question: How do I set up the links and relationships to keep this all straight? I already have a field StudentID and ParentID.

How much easier it was in the 50's when the typical family was Ozzie and Harriet with just David and little Ricky.

 

[The_Doc_Man]

There are two or three ways to do this. As long as no member of your parents table is also a student you are OK. (Yeah, I know - there is a Jeff Foxworthy/Redneck joke in there somewhere...)

Seriously, let's take the case of a Parental Unit and a Child Unit and explore this situation.

Table tblParent
- loPNum (autonumber, prime key)
- stLName
- stFName
- stMName (or stMInitial)

Table tblChild
- loCNum (autonumber, prime key)
- stLName
- stFName
- stMName (or stMInitial)

Table tblRelType
- loRCode (autonumber, prime key)
- loRName (text description of relationship)

Table tblRelation
- loCNum (long, foreign key)
- loPNum (long, foreign key)
- loRCode (long, foreign key)

In tblRelType, you list relationships like:
Birth Mother
Step Mother
Birth Father
Step Father
Foster Mother
Foster Father
Adopted Mother
Adopted Father
Legal Guardian
Lecherous Uncle

When you discover a new type of relation, you enter it into the tblRelType table. After that, you can use it.

When you get new people, you enter them into the parent or child table as appropriate. The next step is the relationship table. You can implement this with a form that has three drop-down combo boxes that STORE the numbers or codes but SHOW the actual text name. The combo box wizard will do this for you.

Now, to list children and their parents, you sort the tblRelation table by loCNum. Then join it to the other three tables. (You might have to do a layered JOIN to make this work conveniently, but it is easy to do.) OR just use the report wizard to make it do the lookup for you.

To list parents and their children, you sort the tblRelation table by loPNum. NOTE that this leads to interesting problems if you do it this way.

Now, the REAL way to do this is with only ONE person table. When you do the relationships, you just add that table to the relationships window twice. In the tblRelationships table, you do not have "parent" and "child" but rather just "Person1" and "Person2" as the pointers to the two people for whom that relation is significant.

This way, you can also have relationships between kids or between parents.

I.e.
Between two parents you could have relations such as
Spouse
Ex-Spouse
Betrothed
Common-Law Spouse
Significant Other
Shack-up Lover
Same-Sex Domestic Partner
Roommate

Between two children you could have relations such as
Sibling
Half-Sibling
Cousin

This way, you could put in ANY person's name and build a list of folks related to that person.