A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table structure for separated families



 
 
Thread Tools Display Modes
  #1  
Old March 22nd, 2010, 10:51 PM posted to microsoft.public.access.tablesdbdesign
Serendipity
external usenet poster
 
Posts: 74
Default Table structure for separated families

Using Access 2003. I have a tblFamilies and a tblFamilyMembers. What do I do
when I have a "family" where the Dad lives at a different address and is
remarried and the Mom is remarried meaning that there is a stepdad and a
stepmom but they all need to get mail? I could just put them all in the
family members table with a fldrole where I put stepmom, stepdad, etc., but
that would necessitate putting the addresses in the tblfamilymembers which
would not be correct.

Thanks for your help.
  #2  
Old March 22nd, 2010, 11:12 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Table structure for separated families

We aren't there. We can't see your table structure.

When you say "they all need to get mail", how are you recording that fact
now? Are you using the "family members table" todetermine who gets mail?
If so, consider this alternative ...

trelWhoGetWhichMail
FamilyMemberID
MailID
...

With a "junction" table like this, you can list as many FamilyMembers as you
need to, plus which piece of Mail they are to receive. Notice that there is
NO use of FamilyID.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Serendipity" wrote in message
...
Using Access 2003. I have a tblFamilies and a tblFamilyMembers. What do I
do
when I have a "family" where the Dad lives at a different address and is
remarried and the Mom is remarried meaning that there is a stepdad and a
stepmom but they all need to get mail? I could just put them all in the
family members table with a fldrole where I put stepmom, stepdad, etc.,
but
that would necessitate putting the addresses in the tblfamilymembers which
would not be correct.

Thanks for your help.



  #3  
Old March 22nd, 2010, 11:42 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Table structure for separated families

Here's my thought, I would like and welcome others to give theirs.

You have to start by defining your mission(s)

Does it include tracking all of these people and the relationships to them?

If "family" is an entity that you are tracking, then you are going to have
to define what "family" means for the purposes of your database.

Is your mission to get properly addressed mail to all of these locations?
If so, you probably need a "mailing" table independent from all of the other
tables. Key fields would be:

Address
City
State
Zip
WhatShouldBeInTheFirstLineOfTheAddressLabel (of course, shorten that
field name)

This could be linked to people and families (per your definition) as needed.



Well, there's one idea!




  #4  
Old March 23rd, 2010, 02:04 AM posted to microsoft.public.access.tablesdbdesign
Serendipity
external usenet poster
 
Posts: 74
Default Table structure for separated families

I am new at this. Still trying to design my database. Right now, I am trying
to get my tables right so that I can enter data. We are a school and need to
send mail to both the primary and secondary families.

If there were no broken families, I would have a family table with the
fields of family id, family last name. And then a family members table with
the fields of family member and position (dad, mom, daughter, son, etc.)--and
other fields such as phone, etc.

How do I design my tables to accommodate families with different last names,
stepparents, etc.? Also, there are children resulting from a remarriage that
are not part of the original family at all.

At this point, I don't have a table structure. I am just trying to figure
out what tables I need to make. Sorry I was unclear. Accommodating all these
family connections are confusing to me!

"Jeff Boyce" wrote:

We aren't there. We can't see your table structure.

When you say "they all need to get mail", how are you recording that fact
now? Are you using the "family members table" todetermine who gets mail?
If so, consider this alternative ...

trelWhoGetWhichMail
FamilyMemberID
MailID
...

With a "junction" table like this, you can list as many FamilyMembers as you
need to, plus which piece of Mail they are to receive. Notice that there is
NO use of FamilyID.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Serendipity" wrote in message
...
Using Access 2003. I have a tblFamilies and a tblFamilyMembers. What do I
do
when I have a "family" where the Dad lives at a different address and is
remarried and the Mom is remarried meaning that there is a stepdad and a
stepmom but they all need to get mail? I could just put them all in the
family members table with a fldrole where I put stepmom, stepdad, etc.,
but
that would necessitate putting the addresses in the tblfamilymembers which
would not be correct.

Thanks for your help.



.

  #5  
Old March 23rd, 2010, 03:54 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Table structure for separated families

So:
- one family can have multiple persons, and
- one person can belong in multiple families.
That's a classic many-to-many relation.

To resolve it, you need 3 tables:
a) a table of persons
b) a table of families
c) a table defining which persons belong in which families.

The 3rd table will have fields like this:
- FamilyID relates to one of your families
- PersonID relates to one of your persons.
So, if Sally belongs in 2 families, she will have 2 entries in this table.

I would think that the personal names (first name and surname) belong in the
person table, but the address fields belong in the family table. The 3rd
table might also have a field to describe the person's role in the family
(e.g. parent, child, boarder, ...)

That should cope with the school situation you describe. If you want a more
comprehensive approach, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
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.


"Serendipity" wrote in message
...
I am new at this. Still trying to design my database. Right now, I am
trying
to get my tables right so that I can enter data. We are a school and need
to
send mail to both the primary and secondary families.

If there were no broken families, I would have a family table with the
fields of family id, family last name. And then a family members table
with
the fields of family member and position (dad, mom, daughter, son,
etc.)--and
other fields such as phone, etc.

How do I design my tables to accommodate families with different last
names,
stepparents, etc.? Also, there are children resulting from a remarriage
that
are not part of the original family at all.

At this point, I don't have a table structure. I am just trying to figure
out what tables I need to make. Sorry I was unclear. Accommodating all
these
family connections are confusing to me!

"Jeff Boyce" wrote:

We aren't there. We can't see your table structure.

When you say "they all need to get mail", how are you recording that fact
now? Are you using the "family members table" todetermine who gets mail?
If so, consider this alternative ...

trelWhoGetWhichMail
FamilyMemberID
MailID
...

With a "junction" table like this, you can list as many FamilyMembers as
you
need to, plus which piece of Mail they are to receive. Notice that there
is
NO use of FamilyID.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Serendipity" wrote in message
...
Using Access 2003. I have a tblFamilies and a tblFamilyMembers. What do
I
do
when I have a "family" where the Dad lives at a different address and
is
remarried and the Mom is remarried meaning that there is a stepdad and
a
stepmom but they all need to get mail? I could just put them all in the
family members table with a fldrole where I put stepmom, stepdad, etc.,
but
that would necessitate putting the addresses in the tblfamilymembers
which
would not be correct.

Thanks for your help.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.