Table structure for separated families
in [Tables]
|
From: Serendipity on 22 Mar 2010 17:51 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.
From: Jeff Boyce on 22 Mar 2010 18:12 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" <Serendipity(a)discussions.microsoft.com> wrote in message news:296C1E0C-2B74-4ABE-8B9E-515740AD25B2(a)microsoft.com... > 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.
From: Fred on 22 Mar 2010 18:42 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!
From: Serendipity on 22 Mar 2010 21:04 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" <Serendipity(a)discussions.microsoft.com> wrote in message > news:296C1E0C-2B74-4ABE-8B9E-515740AD25B2(a)microsoft.com... > > 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. > > > . >
From: Allen Browne on 22 Mar 2010 22:54 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" <Serendipity(a)discussions.microsoft.com> wrote in message news:93279617-981A-42EF-B3F5-B974633899AE(a)microsoft.com... > 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" <Serendipity(a)discussions.microsoft.com> wrote in message >> news:296C1E0C-2B74-4ABE-8B9E-515740AD25B2(a)microsoft.com... >> > 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. >> >> >> . >>
|
Pages: 1 Prev: Proper Indexing of Table Fields Next: work order form and report design help |