table relationship [having trouble]

[neszz]

I have a question regarding relationships between tables (Parent to its child tables). Here is my situation of this Access 2002 database program.

Introduction:

I am creating a form for people to enter in properties for my project at school. I want people to be able to select a county that they live in (I only put 3 counties where I live, so they must live near me), and then input their full name, address, square ft, phone number, loan information, costs to sell, repairs, and so on and so on. After they input, they press the button to save the data. After it is saved, I want it to go to a query so that I can easily sort the data from counties and then by city.

Problem: Is defining the relationship from the Parent Table County to the rest of the tables, which I believe they are the children of County. Since, there is no relationship or not correctly related to, I cannot input any data when I test out the program.

Tables:

(Table1)Parent: Counties

Table2: Background (city, full name, phone number, sqft, etc.)
Table3: Worth: How much the house is worth.
Table4: Loan Information (Only made one table for loan information, b/c if the person had two mortgages/loans, they can just use this table or a duplicate of this table. I program in c++, so I would think you should make one table and reuse it. )
Table5: Total Loan information: This holds all the totals of the loan information
Table6: Retail: This consists of costs of the home to fix, refinance, realtors costs, etc. and also extra comments (about 5 lines worth).

How I tried to make this work. Of course this did not work but maybe you guys and help me out what I did WRONG.

I tried two methods in trying to make my table county as the parent table for the rest of the tables that I designed (The 5 tables). With my understanding of the relationship from the book I bought, at least one field name has to be the same and the data type. My first take on this is, adding the county field name on all the tables because I thought this is how I can make County to be the parent table. When I did this, I open up the county table and view it, and there was a [+], which I pressed on, I can see one of my tables. This tells me that Contra Costa County controls one table. I am not sure why, I cannot see the rest of my tables.

At the same time, I open up a query and add all the tables there so that I can sort the info later on in the program.

Another method I used to try to get my parent table in control of all the tables is by selecting one of the field name in the tables and putting them in each in “one” table, set it as a primary key then I relate, by dragging the field name from that table to other, to define a relationship.

So far, when I drag my tables to the form, I can input but cannot save because it is not related to anything. I cannot remember how I did it before,(few weeks ago), I dragged all the tables to the form and when I saved it saves the data to the table and the query. The only problem is that some tables were not able to input.

This is my problem so far. I am trying to have one parent, County, control the rest of the tables, which are on one form. At the same time, I would like to put these tables into a query to be able to sort by county then by city.

Do you guys have any suggestions? Or even advice?

neszz

 

[johnherman]

It looks to me that the base (parent) table is LOAN, Retail and Background are subordinate to the loan (actually Background is probably 1 to 1 and could be merged into loan). County is subordinate to Background or Loan.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[neszz]

So you are saying that Loan should be the Parent table-which retail and backgound info, be placed into the loan table a field names, is this correct? The reason I wanted the county to be the parent because on the form it is a drop down menu with the counties, then the rest is data entry of the house. So later, if they want to edit or view, they would go to the county drop down in the edit form click the county then the next step would be the drop down or a text box to type in the city, then the properties will show up sorted. So, I thought the parent table County ->backgroud-> then follows. But since you send put the integrate the loan table with background and retail into one table, it would be easy to hold the information. What do you think if they have two loan information? I plan to put two loan information using the same loan table. Also, using queries, just do the same thing like I said before, just add it to a query and then let it collect. I am new at this so.. If I ask dumb questions or not understanding you, let me know. My whole idea of the project is collecting data of properties and sorting it by counties, then cities, and so on. Let me know, if I am also making it hardier than it should.

Thanks for the reply.


neszz

 

[johnherman]

Yes, it appears that either loan or property will be your base table. Loan would be subordinate to property as there could be more than one loan to each property. Retail would be subordinate to property, since that info is independent of the loan(s). Worth would be an attribute (column) on property. Property holder would be subordinate to property. Loan holder would be subordinate to loan, and could be the same person. County would be both subordinate to property and to the property holder's address, since property holder may live elsewhere, said property being a vacation home or being rented to another. Background would be split between info that applies to the property, that which applies to the property holder, and that which applies to the loan holder.

I think I covered all your data elements. If you have any other questions, please let me know.

Best Regards,
- John

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[neszz]

Thanks John. I am going to try this out. After doing this, I add all the tables into one query in order to sort by counties, then cities, and etc.

I know I am getting ahead of myself, but after doing the tables relationship and query, I drag the tables into the form, in order for the end user type data correct? or is it the query?

I am going to keep you updated, if you don't mind.

Thanks for your time!

Niles

 

[neszz]

This is what I got so far from the post. Let me know if there is something is missing.


Parent Table:
Property

Child Table: Subordinate to Property-tbl

Loan- I think what you said is how many and then have a child parent which is subordinate to this table (loan), would be the Loan Holder table. Is this correct?

Retail Costs table- Is subordinate to Property-tbl, since
the info is independent of the loan

County table- Is also subordinate to the Property-tbl.
(I would had the three counties and use it as a combo box which will sort the cities,and properties, correct?)
-This table be also be subordinate to the Property address, which I thought would be in the property-tbl? So, actually the County table is only subordinate to one table, which is the parent table? I could be wrong

The backgroun is split into two new tables:

One table is used for info regarding the property (i.e. full name, phone number, sq ft. etc) -This applies to the property holder. <- is this table subordinate to the other new table that was created after the split of the background table?

The other new table applies to the loan holder. Even though the majority of the time the property owner is the loan holder, but sometimes this is not the case. I will get more in depth next time, by providing the actually field names and primary keys.

Thanks!

Niles

 

[lespaul]

You may want to review 'Fundamentals of Relational Database Design'





Leslie

 

[neszz]

John: This is what I produced from what you are saying.


Table: County_tbl
Field Name:
County (PK) ? I already input the county into the table, so user can just use the combo box to access which county the user wants to pick.

Table: Loaninfo_tbl1
Field Name:
Total_Interest_Owed
Total_PrePayments
Cash_Twner
Assignment_Fee
Foreclosure_Cure_Costs
Total_Loan_Payment


Table: Loan_Holder_tbl1
Field Name:
First_Name
Last_Name
Loan_Holder ?Acting as a dummy field variable so the loan table can relate to this
For the name of the loan holder.


Table: Loan_tbl1
Field Name:
Loan_num (PK) ? Connecting to the base table
Amt_Owed_mtg
Amt_Owed_Del_interest
Tot_Owed_mtg
Loan_date
Interest_rate
PrePayment_Penalty
Mortgage_Payment
Assumable
Loan_Holder (PK) ? relating to the table Loan_Holder
Total_Loan_Payment (PK) ? relating to the table Loan_Holder_tbl1.

Table: Propertyinfo_tbl1
Field Name:
Date
State
City
County (PK) ? relating to the base table and also the county table.
Bedroom
Bathroom
Sqft
Yrbuilt
First_Name
Last_Name
Phone_Number
Work_Number

Table: Property_tbl1 (BASE TABLE)
Field Name:
County (PK) ? Connecting to the County table which hold the prewritten counties
So the user can select the county from the combo box.
Address
Loan_num (PK) ? This will connect to the Loan Table (Loan num means is it a
The first mortgage, 2nd, 3rd, etc.
Estimated_Market_Value
Our_Asking_Price
Percent_Of_EMV
Repairs (PK) ? relating to the table repairs

Table: Retail_tbl1
Field name:
Cost_To_Refi
Percent_To_Refi
Real_Estate_Commission
Percent_for_Commission
Repairs ? relating to the base table
Home_Warranty
Escrow_Closing_Fees
Holding_Costs
Number_Months_To_Hold
Roof_Report_Repair
Electrical_Report_Repair
Total_Cost_To_Buy_Home
Profit
Addition_Comments


I am thinking that the field name county should go to the table property info and have the base table property to relate to the property_info.

To me this looks fine, but I am not sure if it correct or not making it clear enough to programmers. What do you guys think I should take out, swap, change, or etc.?

Leslie: I am going to read that paper that you link me to. Thanks..

Thanks for everybodys time
Niles

 

[johnherman]

I am wondering why you split the property info into two tables. If the info will not change (year built) or changes quite rarely (sq feet), then just put it in the base table. If it may change often (and you want to track the changes), or if there are more than one value (dual ownership, partnership, multiple loans, multiple loan signers) then use a child table.

Otherwise, things look pretty good. Also, please read the article that Leslie recommended. I think you will be pretty close to 3NF with your design.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[neszz]

Thanks, I printed the article. I split the table because I thought you suggested it. All I am doing is tracking properties and their owners. Later on I want to track the dates of the properties being sold. I just to get moving on this project, due in 2 weeks.

I will keep you updated.

Thanks! Are my primary keys look good and their relationships?

Niles

 

[johnherman]

You will need a PK for the property table. It could be the county plus a sequential number, or it could just be a sequential number. The Repairs and County, which you have marked as PK in the base table are actually FK, Foreign Keys, in the base table. They are PK's in their respective Repair and County tables. It's OK to generate key fields which have no purpose other than to be keys, and I believe you will need to do it in several places since the natural keys (address for property, address + repair sequence for repair, etc) are quite large and cumbersome.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[neszz]

[New]

Table: County_tbl
Field Name:
County (PK) ? I already input the county into the table, so user can just use the combo box to access which county the user wants to pick.

Table: Loaninfo_tbl1
Field Name:
Total_Interest_Owed
Total_PrePayments
Cash_Twner
Assignment_Fee
Foreclosure_Cure_Costs
Total_Loan_Payment (PK) ? relates to the Loan_tbl1


Table: Loan_Holder_tbl1
Field Name:
First_Name
Last_Name
Loan_Holder ?Acting as a dummy field variable so the loan table can relate to this
For the name of the loan holder.


Table: Loan_tbl1
Field Name:
Loan_num (PK) ? Connecting to the base table
Amt_Owed_mtg
Amt_Owed_Del_interest
Tot_Owed_mtg
Loan_date
Interest_rate
PrePayment_Penalty
Mortgage_Payment
Assumable
Loan_Holder (PK) ? relating to the table Loan_Holder
Total_Loan_Payment (PK) ? relates to the table Loaninfo_tbl1
Loan_num (PK) ? relates to the base table.









Table: Property_tbl1 (BASE TABLE)
Field Name:

PropertyId (PK) ? Sequential number for each property
County (PK) ? Connecting to the County table which hold the prewritten counties
So the user can select the county from the combo box.
Address
Loan_num (PK) ? This will connect to the Loan Table (Loan num means is it a
The first mortgage, 2nd, 3rd, etc.
Date
State
City
Bedroom
Bathroom
Sqft
Yrbuilt
First_Name
Last_Name
Phone_Number
Work_Number
Estimated_Market_Value
Our_Asking_Price
Percent_Of_EMV
Repairs (PK) ? relating to the table repairs

Table: Retail_tbl1
Field name:
PropertyId (FK) ? relate to the base table
Cost_To_Refi
Percent_To_Refi
Real_Estate_Commission
Percent_for_Commission
Repairs (FK) ? relating to the base table
Home_Warranty
Escrow_Closing_Fees
Holding_Costs
Number_Months_To_Hold
Roof_Report_Repair
Electrical_Report_Repair
Total_Cost_To_Buy_Home
Profit
Addition_Comments
Here is an updated on my relationships. Everything is relating correctly except the field PK- PropertyId and Loan_num. It said that these two field names do not have any unique values. I just wanted propertyId for each property, even though I am actually going to be search by county-city-address, instead of propertyId. But who knows, I might change it in the future. The field name loan_num, is what I am concern at this time. Each property might have one, two, three, or four loans. I do not understand why I cannot relate one-many from my base table to my loan_tbl. I know that the loan table will probably be used up to four times, depending on how many loans it has on the property. How can I make this work. I did read the article and it was really helpful, but at this time I still remain puzzle why I can get this to work.


Thanks for your time. I am going to keep trying.


Niles

 

[neszz]

[Update:5]
I made it relate by changing the loan_num field properties. Now, I am adding all the tables into a query and onto the form. It says that I cannot add or change a record because a related record is required in the table Loan_tbl1. I not sure what is needs, I guess place the PropertyId in there or something.


Niles

 

[neszz]

Final: I have a better question to ask then just submitting updates and thoughts. I hope this thread helps other beg. b/c it is helping me out. I have the relationships set up now. I took out the loanholder because the relationship with the table loan was not working out when I was doing some data entry. When I took it out, everything worked fine. Even though, the loan holder could have been an entity like a bank or others, I just assumed that the property owner is the person who has the loan. Now, I can submit data to my database. I have ran into many duplicate and relationship problems. I tested out the data entry flow from the query I setup. My two problems,

1) The FK's do not except duplicates because the definition of PK and FK are to hold unique values. A couple of my PK and FK are not unique value in some point.

For example, Total_Loan_Payment in the Loan_tbl1 which relates to the Total_Loan_payment cannot hold duplicate values. Some properties might have the same total loan payment, or repairs (which is the PK).

2)Since I have ID's, which are PropertyId, and Loan_num, how do I have them auto select numbers for them. So, PropertyId, for each property it would go up 1. As for the Loan_num, I am having second thoughts in having this as a PK because it is not a unique value. See, for each property they can have up to 4 loans. I do not think this will be a good to have it as a PK. How would I connect the tables together since there are no FKs?

3) One more question, Since there could be up to 4 loans on the house, how should I structure it? I understand I can re-use the tbl- loan_tbl1. Do I have to link the tables together? This might waste space if the majority properties only have 1 or 2, while few would have 3 or even 4.

thanks for your time.. enjoy programming..

Niles