Data Base Design

M

Matthew Wayne

I am in the process of creating a family members data
base and wish to provide fields for Lastnames and take
into account where their is a defacto relationship etc.
For EG: Lastname: "Smith" and living with partner "Jones"
(Children will also be recored). I have thought of 2
fields, one called "Lastname Male" and Lastname Female".
Am I barking up the wrong tree or on the right track. I
also need to include their individual hobbies, but feel
that when reports are generated by a specific hobby, I
will need to construct 2 reports, 1 by Male and 1 by
Female.

Any help would be appreciated.
 
J

John Vinson

I am in the process of creating a family members data
base and wish to provide fields for Lastnames and take
into account where their is a defacto relationship etc.
For EG: Lastname: "Smith" and living with partner "Jones"
(Children will also be recored). I have thought of 2
fields, one called "Lastname Male" and Lastname Female".
Am I barking up the wrong tree or on the right track. I
also need to include their individual hobbies, but feel
that when reports are generated by a specific hobby, I
will need to construct 2 reports, 1 by Male and 1 by
Female.

Any help would be appreciated.
Click to expand...

Well... it's the 21st century. I don't know how you feel about it, but
it's a fact that there are gay couples, some with children; I even
know some polyamorous families (one with three husbands and two
wives). Not legal I know but it's real.

I'd suggest an "indirect many to many self join" which will handle
these more arcane situations as well as your simpler one. You might
want to have a Household table (based on shared living quarters)
related one to many to a People table; then there'd be a Relationships
table with fields Person1ID, Person2ID and Relationship. This might
have the ID of the husband as Person1ID, the ID of the wife as
Person2ID, and "Wife" as the relationship, and another record with the
reciprocal relationship and "Husband"; you could have a "Significant
Other" relationship; you could even have "Father", "Grandson",
"Niece", etc. etc.

John W. Vinson[MVP]
 
M

Mike Painter

Matthew said:
I am in the process of creating a family members data
base and wish to provide fields for Lastnames and take
into account where their is a defacto relationship etc.
For EG: Lastname: "Smith" and living with partner "Jones"
(Children will also be recored). I have thought of 2
fields, one called "Lastname Male" and Lastname Female".
Am I barking up the wrong tree or on the right track. I
also need to include their individual hobbies, but feel
that when reports are generated by a specific hobby, I
will need to construct 2 reports, 1 by Male and 1 by
Female.

Any help would be appreciated.
Click to expand...

I think you are barking up the wrong tree and may need a "family unit" table
to link to.
It solves the last name problem and the kids to a certain extent.
I suspect the kids would have to have a custody field (male, female, joint,
adult) but that does not solve all the problems.

A recursive family table might do it but the project fell apart and I
stopped thinking about it.... except late at night when the wind howls.
 
Top