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