Question on creating tables linking parent/child/spouse info

[nysimonsez]

Right now I have 2 tables in a database, 1 to collect personal info, the other for cash received from each person:

Table 1: NameID, address, etc...
Table 2: Date, NameID, amount

I want to add 2 functions to the database:

1. If I run a cash received report for husband (nameid=1), it will
automatically include the wife's cash received if he has one (nameid=2,
for example) and print both on the same report. How can I build that
"relationship" between 2 nameid's to reference they are husband and
wife.

2. The personal info table will have relatives, for ex: nameid1=father,
nameid2=wife, nameid3=child, nameid4=nameid1's father, etc. How can I keep track of these "relationship" in the database? I've read other threads where I should create a parent/child relationship table, is that enough or do I have to create a father/grandfather relationship table also?

Thanks for any suggestions you may have.

 

[neileg]

You have a theoretically unlimited number of relations (though in practice people don't have an infinite number of children), certainly too many to manage with fixed fields in your record. You could create a new realtionship table that has three fields, one for the ID of the 'target' the second for the ID of the relation and the third for the description of the relation.

Then, when you want to query on any individual, you can bring in the IDs of the related people from the relationship table.

That's the easy bit. The hard bit is going to be maintaining and managing the relationships. For example, you might be adding a woman who is the mother of three of your existing donors, the child of two, the sister of one, the grandmother of five, the wife of one, and the exwife of two. How are you going to do this unless you keep the family trees of all potential donors?

It's much easier when you are doing a parent child relationship because you always have two parents to each child so you're only tracking two relatives with fixed relationships.