Family (Record) Challenges

Doing database for a non-profit can require a higher level of detail on a family than most folk are used to. Here is some basics I have come across over the years, and I know there are even more detailed systems when you get into the education realm. Depending on what you are doing “famly” can mean different things. A lot of the mixing and matching is based on relationships in the family unit. And in those relationships members could be related to multiple families in different roles. So lets go on to my current family record structure. It is divided into 5 tables at the core, with additional depending on needs.

Adult

• Adult Record ID
• Name
• ID (ssn/whatever)
• Gender, Race, Birthdate, etc.
• Phone(s)
• Income
• Adult Specific Eligibility Criteria

FamilyAdult

• FamilyAdult Record ID
• Adult Record ID
• Family Record ID
• Member (in family) (Primary, spouse, other)

Family

• Family Record ID
• Physical Address
• Mailing Address
• Income (any that is not specific to adult or child)
• General Family Eligibility criteria

FamilyChild

• FamilyChild Record ID
• Family Record ID
• Child Record ID
• Child's Relation to Family (natural born/adopted, foster/guardian group 1, foster/guardian group 2, foster/guardian group 3, etc.)
• Data specific to this child in this family

Child

• Child Record ID
• Name
• ID (ssn, etc)
• Gender, Race, birthdate, etc.
• Income (Specific to the child)
• Child Specific Eligibility Criteria

With this structure adults and children can exist in the database un-duplicated while also able to be members of multiple families (in the adult case it could be a father of children in two families, for a child, maybe the child of divorced parents with joint custody).

So when linking in data that always related to with the family member (i.e. work information) you would link to the Adult or Child; but if it is some specific service that is dependent on the family that they are a member of, you would link those to the FamilyAdult or FamilyChild records, where they will still identify the adult/child but only in relation to that family, not any others.

There is yet another dimension already in this, some children can be a family in themselves - foster kids and guardianships. That is the purpose of foster/guardian groups in the relationship in FamilyChild. All related foster kids would have the same group number, if there is another foster child not related to the others, they would get another group number, etc. That way to can use that to determine the 'families of one' or more which comes up in some eligibility and reporting instances.

Dimension of Time

Starting out you may go with just the above, but as time proceeds relationships change, people marry, people die, or leave. So time can become another factor to the data. For the comprehensive data file I need, when records are updated the old data is retained and a new record is made. (I covered this a little in my entry about Table Field Naming Tips)

Thats the basics, again not all applications need this detail and some may require much more - but if you haven't done such work, this will get you started in the right direction if you need to think of families and services related to them more.

~~LINKBACK~~ ~~DISCUSSION~~