>>Hi All,
>>
>>I always thought I was pretty good at designing data but am having a challenge wrapping my mind around this. I was hoping if I describe this situation a bit and give you my table design you could give me some feedback.
>>
>>This application is for a Food Pantry (yes mostly free gratus work on my part) where they serve food to families that qualify. There isn't a huge need to keep lots of data, but I am normalizing the data from what the current big flat file system does. I have a persons table that holds all the attributes of a person and a household table that identifies the persons that make up a household. The household table also holds a foreign key to a location table where I have the addresses. The intent here is to end up with locations that are maintained seperate rather than as part of the household which changes. Same with the persons; if a person moves from one household to another we still have the information on that person. When they are added to a household or move from one household to another I store a HouseholdRole or Relationship role for that person in the house. These can be values such as "Spouse, Head of House, Son, Daughter, Uncle, Aunt, or even Friend".
>>
>>Some of my concerns with this design is the household ends up holding the locationID for every person I add to the household table. Also, because we keep track of the "Head of Household" or otherwise known as Applicant seperate from the other people in the house, I often need to query the table and return a household for a given person along with the applicant in that household. This creates a complicated query.
>>
>>Here is the table design without a few extra fields.
>>

>>*** Household ***
>>HouseholdID
>>LocationID
>>PersonID
>>RelationshipRoleID
>>
>>*** Person ***
>>PersonID
>>FirstName
>>MiddleName
>>LastName
>>DateOfBirth
>>Gender
>>Race
>>PhoneNumber
>>
>>*** RelationshipRole ***
>>RelationshipRoleID
>>RelationshipRole
>>
>>*** Location ***
>>LocationID
>>LocationTypeID
>>AddressNumber
>>StreetName
>>ApartmentNumber
>>PostalCode
>>
>>*** PostalCode ***
>>PostalCodeID
>>PostalCode
>>City
>>StateID
>>IsInServiceArea
>>

>>
>>Right off the bat I need a query to get Applicant of the household with name, the location address including postalcode information for a person including that persons role in the house and supply a personID. That person may or may not be the applicant.
>>
>>Any thoughts on this would be helpful as I have been agonizing over this design for a few days now.
>>Thanks
>>Tim
>
>Without too much analyzing I suggest to split HouseHoldLocations as a separate table that will only have HouseHoldID and LocationID
>and HouseHold that will have HouseHoldID PersonID
>
>So, two separate junction tables and not one.

Thanks Naomi, I am working on testing a solution Viv suggested.