DB design puzzle: individuals & families

[RDecker]

I'm puzzling over how to best organize a DB structure--perhaps just thinking out loud and looking for some feedback. This is for a nonprofit group in which we need to track info on both family units as well as the individuals who make up each family (parents and children). What's the best way to organize the relationships to do this? Or what are the right questions to ask to decide that?

Should I be entering individuals and then combining them into families, or entering families with related members? There really isn't any distinctive "family" info that's true only of the family (other than the list of people who comprise it)--even the addresses might be different if, e.g., one child is off to college, and with the proliferation of cell phones, there might be several different phone numbers. All of which, I think, suggests entering individuals and then combining them.

On the other hand, is there any simple way to create a relationship other than manually assigning a "family number," then looking it up and filling it in for each individual in the family? Does it make sense to have a Family table with only one field (i.e., family number), and then use a portal on a layout to list family members by name (allowing records to be created in a Person table through that portal)? Data entry would then still require going to each individual record to enter the relevant address, etc., but at least this would make it a bit easier (I think!) to enter the family members.

Or am I going about this all wrong?

Thanks,

decker

[Ender]

You are asking the right questions.

There are several ways to represent families in database design. The right way for you depends largely on what parts of a "family" are important for your project, and how flexible it needs to be. The more flexible it is, the more tables, relationships, and work it will require.

I'll try to think of some possibile structures and get back to you...

[Ender]

I do all my relationship thinking with ER diagrams, so here are a few possibilities.

To read the graphs, think of a single line entering an entity as a "one" and a chicken foot entering an entity as a "many". So in example 1, "one" family can have "many" members, but each member can only have "one" family. Underlined words are the identifiers for that entity, along with any entering chicken feet with a slash through it.

[RDecker]

Thanks. In your diagram 3, do you intend this to model a data/interface separation model? i.e., is the blank box in the middle an interface file with no fields/data, or is this some other relational feature that I haven't run into yet? (Or just an incomplete diagram?) As you can tell, I'm coming from a "flat" background and just learning the relational way of doing things.

Decker

[Ender]

Good questions.

The HH_Membership entity in example 3 is a back-end table. You would access it through portals in one of the other two files. The fields it would have are the identifiers it depends on (Member ID from Member and Household ID from Household), plus some lookups from other related files that make the interface work. The fields aren't specified because the IDs are implied from the dependencies on the chicken feet, and the lookups are not where the data originates.

Examples for HH_Membership fields if the portal to HH_Membership is in the Household table:

Household ID

Member ID

Member Name

Member Date of Birth

Member Type

Member Phone 1

...

So in example 3, the household data is populated in a layout in Household, the member data is populated in a layout in Member, and the members are picked for each household after words, through a portal in Household. As you can see the data entry is more work in this model.

[Ender]

I dreamt up another interesting Family solution involving one table with three self-join relationships. This is sort of a family tree model. Each person can have a Mother, Father and a Guardian. The address would then be a lookup based on the LivesWithChoice. Each person, in turn can have children that are Persons under them in the tree.

The difficulty with this model is that there is no Family entity which ties all the Persons together. They are only related by who has the same Mother and Father IDs and who is directly above and below them in the tree.

[Kurt Knippel]

Your last solution really is what defines a family tree. By adding the "Family" table you have to then define what a family is.

So what is a family? Certainly myself, my brother, my parents, my grandparents and maybe my aunts/uncles and cousins are all in the same "family". OK, but are my cousins on my dad's side (part of my family) are not really part of my mom's family, and they are certainly not part of my mom's mom's family, but they are all in the same family as I am.

See a family is a distinct grouping that we impart, and is at least partiallyt defined from the perspective of the individual in question. They are not distinct groupings.

Of course your business logic may dictate that a "family" is the individual in question, thier parents and siblings. In which case you can certainly group them distinctly.

[Ender]

"What is a family?" is the key question when you define a Family database. The answer (for your database) is defined by what your organization is trying to know about a family.

There are several hard questions you must wrestle with to come to the best solution:

Is "a family" the people who live together in one household, or is it parents and their children?

What if the parents are not living together--does your business logic allow two separate family records to track them, or must they be linked?

What about two unrelated divorced parents that marry?

How should you account for foster children or adopted children?

When you work on Family modules, it is best to bring these questions to the people in the organization who work closely with the data to try to get a consensus about what it is they really need to track, and how they want the data to flow.

[RDecker]

Thanks for the comments, esp. the sample diagrams. They've been helpful.

decker