Husband - Wife - Child

[Eric C]

Hello,

My organization tracks many people, including many people
from the same family. Often times, we send mass mailings
and we have many people in our system that are from the
same family. How would I design my tables in a manner to
be able to track people from the same family. I would
need an individual record for each person, but when I go
to send a mailing for example, I might only want to send
one letter to the family.

Thanks for your help.

Eric

 

[Allen Browne]

You certainly need your Person table (for the individuals).

You can also create a Family table, with fields such as:
- PersonID foreign key to Person.PersonID
- RoleID foreign key to Role.RoleID

The Role table contains entries such as parent, child, etc.

Before you go too far with this design, consider how you will handle
families where the kids live half the time with their Dad, and half with
their Mum. Are the members of two families? Do de-facto relationships count
as families? What about students sharing a house? They are a household, but
a family? If they start sleeping together, are these students then a defacto
family?

Just to set you thinking about the fact that human relationships are not
easy to define, let alone to model with database relationships.

 

[Ron]

I would think the key consideration would be to eliminate
duplicate mailings by only sending one mailing to a given
address. So you should be tracking addresses and not names.
Members of a family may not have the same last name, so
you can't use that as a basis. Conversely, multiple people
at the same address whether they have the same last name
or not, may not be members of the same family.
There are other considerations, like privacy (and some
laws like HIPAA which mandate it) issues where information
regarding one family member may not be shared with another
family mermber.

 

[TC]

You certainly need your Person table (for the individuals).

You can also create a Family table, with fields such as:
- PersonID foreign key to Person.PersonID
- RoleID foreign key to Role.RoleID

The Role table contains entries such as parent, child, etc.

Um, do you not mean:

tblRelationship
PersonID_1 ( composite )
PersonID_2 ( primary )
RoleID ( key )

?

Then two people could have multiple relationships (eg. parent, employer &
landlord).

TC

 

[Mike Sherrill]

My organization tracks many people, including many people
from the same family. Often times, we send mass mailings
and we have many people in our system that are from the
same family. How would I design my tables in a manner to
be able to track people from the same family.

If by "family", you mean "family" <g>, then you need to think along
the lines of

CREATE TABLE Families (
<family candidate key>,
<one person's candidate key>,
<another person's candidate key>,
<the familial relationship between those two people>
)

where all the stuff between angle brackets is deliberately vague.

But there are many shades of gray here. You might really just want to
send one piece of mail to a specific address, regardless of the
relationships of the people who live there. (That's one way of saying
that sometimes people from several families live together. Students,
for example.)

 

[Allen Browne]

That depends on how you define your familes and roles.

The structure I suggested would permit multiple people in one family.

The structure you suggested would be good for defining relationships between
pairs of people.