Sort of a many to many relationship

[conneticat]

I have a database of information including kids and their parents. I want to also indicate that some kids are siblings (not always evident by sharing a parent!). This might be simple, but I just can't figure it out. All I can think of is making another table that creates a random "sibling group" number that attaches to the kids I check, or something along those lines. I haven't worked out the details and this is making my head spin. It was hard enough figuring out how to list kids who live with different parents at different times. In addition, some kids in one group might be siblings with one kid in another group of siblings, due to stepbrothers and all that, even if they don't live together. Any ideas?

 

[Remou]

Hi
How about going with your family table?
[tt]SiblingID KidID
1 23
1 24
1 25
1 26
2 24
2 25
2 27[/tt]

And a parent table
[tt]KidID ParentID ParentType
23 25 BM
23 30 F
23 31 AM
23 32 SM
23 33 SF
24 50 M[/tt]

Would that work?

 

[MajP]

Why not one table? Unless you collect different types of information for grown ups then kids. Call it "tblPersons". Create a join table called (joinTblRelationship)

joinTblRelationship
intPersonOneID
intPersonTwoID
strRelationshipTypeOneToTwo
blnLiveTogether

So if you had a family you would have 6 records
ID Name
1 MotherName
2 FatherName
3 ChildName

1 2 Wife
1 3 Mother
2 1 Husband
2 3 Father
3 1 Child
3 2 Step Child

Now join tblPerson to joinTblRelationship by intPersonOneID, and join another instance of tblPerson by intPersonTwoID.

You could half the amount of entries in the join tbl by adding another field
strRelationshipTypeTwoToOne
But that would make entry hard to keep track of. In the first way you could just build a subform of a person form, and pick all the relationships that a person has.

 

[Remou]

What about Birth Mother, Current Mother, Previous Mother? Child in both Family 1 and Family 2? Child of Child in Family 1? Birth Mother of Child 1 is Current Mother of Child 2 and Wife of Father of Child 6?

 

[Remou]

Sorry MajP, I did not read carefully enough. Though I think the live togther may need to be expanded, for example, "live together every second week".

 

[conneticat]

The table is all about the kids...the parent information is just one of the many many tables attached to the kid's table. If the kids were all orphans, it would be a little easier. I think some sort of slurry of the first and second anwer might work.

 

[SeeThru]

Form a Kibbutz. All kids now belong to all parents.

That'll be $1000 consultancy fees please!

 

[conneticat]

Yes, a Kibbutz will work. I will herd everyone together, eartag & chip them, and track them that way. Thanks for all your help!