Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / April 2005

Tip: Looking for answers? Try searching our database.

Relational Design

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bell - 14 Apr 2005 02:16 GMT
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 - 14 Apr 2005 03:37 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am designing a relational Family data base which has
> among other fields: LNAME & FNAME FOR EACH FAMILY MEMBER,
[quoted text clipped - 7 lines]
> Tasks
> Thanks for any help
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.