Tables and Relationships

[Steve]

I have been thinking hard about our database needs. We are in a nonprofit
transitional housing organization for persons willing to recover from
chemical addictions. In a way our business is property management. Our
orginazation serves People who form Households. Households turn in
Applications and are put on a Waiting List, and eventually they sign Leases.
Many people lease from us two or more times because they don't always follow
the rules of our program the first try. It is likely that their households
change each time. Many come to our program as a single person household and
then move on to a different unit with the families they have reunited with.
Each person in the household can have multiple sources and amounts of
Income. I have to track the incomes of people individually and by the
household for periods of time (usually the period of the lease). I am
required to report on the amount and sources of income by person and by
household when they enter a lease agreement, at different intrevals during
thier lease, and at the end of their lease period. I am required to report
on many differnt things but most are simular to the Incomes I have
mentioned.

I just can not seem to figure out the best way to build my tables and
relationships. So far I have a tblPeople and a tblRelationships. The
tblRelationships has a PrimaryPersonID and a RelatedPersonID both related to
the tblPeople. I have an Incomes table with the FK PeopleID and it includes
a StartDate and an EndDate field. I know I will need a tblLeases and
tblApplications but I think my FK should be HouseholdID. I really don't
know how to create the Households table and tie it all together. This is my
first shot at a database and I hope they aren't all this difficult. Any
suggestions would be very helpful. I've asked a lot of questions in this
group and others trying to get things right. I am very grateful for the
help because I plan to live with this database for a long time. I have
learned to stop and think about the questions I'm asking before posting
them. I regret asking some stupid questions. I figure I can always
experiment building queries, forms, and reports as my skills increase but
right now I just need to get these tables correct so I can move on. I've
promised myself (and others) that this will be the weekend they are
completed.

TIA
Steve

 

[Marc]

Hi

I have been thinking hard about our database needs. We are in a nonprofit
transitional housing organization for persons willing to recover from
chemical addictions. In a way our business is property management. Our
orginazation serves People who form Households. Households turn in

table People: PK PersonID
table HouseHold: PK HouseholdID, FK PersonID

Applications and are put on a Waiting List, and eventually they sign

Leases.
table ApplicationsK ApplicationID FK LeaseID FK HouseholdID
table Leases: PK LeaseID, startdate, enddate
If each application results in a new lease and each lease needs a new
application then these 2 tables can be combined.

Many people lease from us two or more times because they don't always follow
the rules of our program the first try. It is likely that their households
change each time. Many come to our program as a single person household and
then move on to a different unit with the families they have reunited with.
Each person in the household can have multiple sources and amounts of

table Income: IncomeID, date earned, periodic (W,F,M), FK PersonID

Income. I have to track the incomes of people individually and by the
household for periods of time (usually the period of the lease). I am
required to report on the amount and sources of income by person and by
household when they enter a lease agreement, at different intrevals during
thier lease, and at the end of their lease period. I am required to report
on many differnt things but most are simular to the Incomes I have
mentioned.

<snip>

Based on the info given the above should serve.
Marc

 

[Steve]

Thanks Marc

Regarding tblHousehold: I'm sorry I don't understand what you mean about
setting up the FK PersonID in it. I would enter all people into the
tblPeople then in someway they (or their PersonID) needs to get into the
tblHousehold? Or do people need to be assigned a household? Some people
will be single households and need to be considered a household all by
themselves. Several people in the tblPeople may be in the same household.
Could you give me an example of the way the tblHoueshold should be setup?
And how multiple people would be in the same household.

It seems as if I would need to setup a many to one relationship from
tblHouseholds to tblPeople? tblHouseholds could have two fields HouseholdID
and HouseholdName or one field HouseholdID that would not have the data type
property of autonumber.

The tblPeople would have the FK HouseholdID. Would this be correct? Since
housholds will change over different leases should I have StartDate and
EndDate fields included in the tblHouseholds? Seems like there is no way to
win with this method. If the persons leases from me again with a different
household I would have to change the FK HouseholdID in the tblPeople. This
would make it impossible to store a permanent record of households. Hope
this makes sense and thanks again.
..

 

[Guest]

From what I've read of your project, there should not be a FK in tblPerson or tblHousehold that creates a direct relationship between the two tables. It sounds like there is a Many-to-Many relationship between Peron and Households: One person might be in many households and one household might have many people. In Many-to-Many relationships, you need a junction table to hold the two foriegn keys

Suggestions: **tblCurrentHouseholdStatus:StatusID(PK),PersonID(FK),HouseholdID(FK
**A form "frmStatus" where you could have combo boxes for selecting
from the people list and the household list
**You could have a query for finding all the households a person is in
(or visa-versa) and display the results in a listbox

Your tblHousehold would have pertinent information about the household; ex. HouseholdID, household name, reason for requesting application, current location, etc. that is different than information in tblPerson. Really, only you know what should be in the table - this is just to point you in a possible direction

Here's another idea: Your Person form could have a command button (or maybe a combo box with a NotInList event) that would take to the Status form. The status form might have a listbox populated with all of the household relationships this person has. You could select one or create a new from this form

Rick..

----- Steve wrote: ----

Thanks Mar

Regarding tblHousehold: I'm sorry I don't understand what you mean abou
setting up the FK PersonID in it. I would enter all people into th
tblPeople then in someway they (or their PersonID) needs to get into th
tblHousehold? Or do people need to be assigned a household? Some peopl
will be single households and need to be considered a household all b
themselves. Several people in the tblPeople may be in the same household
Could you give me an example of the way the tblHoueshold should be setup
And how multiple people would be in the same household

It seems as if I would need to setup a many to one relationship fro
tblHouseholds to tblPeople? tblHouseholds could have two fields HouseholdI
and HouseholdName or one field HouseholdID that would not have the data typ
property of autonumber

The tblPeople would have the FK HouseholdID. Would this be correct? Sinc
housholds will change over different leases should I have StartDate an
EndDate fields included in the tblHouseholds? Seems like there is no way t
win with this method. If the persons leases from me again with a differen
household I would have to change the FK HouseholdID in the tblPeople. Thi
would make it impossible to store a permanent record of households. Hop
this makes sense and thanks again

 

[Marc]

<snip>

Thanks Marc

Regarding tblHousehold: I'm sorry I don't understand what you mean about
setting up the FK PersonID in it. I would enter all people into the
tblPeople then in someway they (or their PersonID) needs to get into the
tblHousehold? Or do people need to be assigned a household?

People would need to be assigned to a household. The lease would hold the
details of the House. People has the details of each person. That's were my
mistake was -
table HouseHold: PK HouseholdID, FK LeaseID, FK PersonID - each household
has one to many people associated

Some people
will be single households and need to be considered a household all by
themselves. Several people in the tblPeople may be in the same household.
Could you give me an example of the way the tblHoueshold should be setup?
And how multiple people would be in the same household.

It seems as if I would need to setup a many to one relationship from
tblHouseholds to tblPeople? tblHouseholds could have two fields HouseholdID
and HouseholdName or one field HouseholdID that would not have the data type
property of autonumber.

The tblPeople would have the FK HouseholdID. Would this be correct?

No, then you have a circular relationship - it is easier to manage through
junction tables.

Since
housholds will change over different leases should I have StartDate and
EndDate fields included in the tblHouseholds? Seems like there is no way

to
The correction would work if leases are for the same period as the household
endures as is - so do new leases get signed if the household changes? If not
then start date and end date would need to be added to the junciton table
household.

win with this method. If the persons leases from me again with a different
household I would have to change the FK HouseholdID in the tblPeople. This
would make it impossible to store a permanent record of households. Hope
this makes sense and thanks again.
.

Marc