Relational Design

[Bell]

I am designing a relational Family data base which has
among other fields: LNAME & FNAME FOR EACH FAMILY MEMBER,
DOB, TASKS (MANY). Should i have seperate tables (linked)
for say Husband, Wife & Children as sometimes the LNAME
will differ due to defacto etc. If that is the case how
do I link say LNAME of Husband to Lname of Wife/Partner.
Do I need to call the attribute(field) of the Partner say
LNAME of partner in the seperate table or can it also be
called simply LNAME (IE DUPLICATED). Same with DOB and
Tasks
Thanks for any help

 

[Allen Browne]

All the individuals should be in one table, say tblPerson.

Typically, you have another two tables to define the families: one that just
holds a record for each family, and a related table that lists the persons
in the family and their roles. This tblFamilyPerson table has fields:
FamilyID relates to tblFamily.FamilyID
PersonID relates to tblPerson.PersonID
RoleID relates to a table of roles (e.g. "Husband", "Wife",
"Child").

That works, but it has some limitations. For example, if you need to send a
mailing to your clients who may be individuals or families, there is no one
table you can use. Likewise, if you give counselling sessions to both
individuals and families, you cannot set that up.

This article suggests a more flexible structure:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html
It explains how to set up a client table where the client can be an
individual or a family. You end up with both in the one table, and then
other tables define who belongs to which family and with what roles.
Contains example database.