>>>>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
>>>
>>>Would it be cleaner to split the HouseHold table and have a separate HouseHoldMembers table:

*** Household ***
>>>HouseholdID
>>>LocationID
>>>Applicant Id  -> 1-1 to Persons
>>>
>>>**^HouseHoldMembers (Link table)
>>>HouseHoldId          -> HouseHold
>>>PersonId                -> Person
>>>RelationshipRoleId  ->Role

>>
>>Hi Viv,
>>That is definately one of the considerations and would solve some of the issues. I wonder if the Applicant should be in the Household table then or just added with the proper role in the HouseholdMembers.
>
>My inclination for the Applicant would be to put a direct link between the Household and Persons table and not to include an entry in the HouseHoldMembers table at all (if you do that then you're pretty much back to your original schema). But there could other factors that mitigate against it.....

That does make sense. I may try this and see if I can create some queries to prove my needs. Thanks