I'm having a hard time understanding intersection tables and how to set them up in DBMan SQL, though I may be much closer to the solution than I realize.
I have a database of real estate properties, and it contains several tables, but three in particular are giving me trouble.
The main table, called fileReview, contains most of the information about each property. A contacts table contains fields for name, address, telephone numbers, etc. Additionally, this table contains a select-menu field called contactTypes, which categorizes each contact as a borrower, realtor, lawyer, seller, etc. This field doesn’t actually store these words, but rather, an ID field (as a foreign key) from a third table, named contactTypes.
The main fileReview table has a corresponding field for each of these contact types, so that users can view the name of the lawyer, borrower, etc., connected with each particular piece of property.
I've set up the add screens for the fileReview table to separate all borrowers into one select menu, all laywers into another, and so forth. Then, each contact gets stored as its corresponding ID field (contacts.contactsID).
My problem arises in trying to display the names for these contacts rather than their ID numbers. I can accomplish this using a select queries as template globals. Unfortunately, I can only do it for one field at a time by editing the fileReview.defs file to include a foreign key as follows:
'fk' => {
'contacts' => {
'Borrower' => 'contactsID'
}
}
If I try to add more than one foreign key entry (e.g. 'Lawyer' => 'contactsID'), the ID numbers (or even nothing at all) appear in my records rather than the names. If I take out the foreign key completely, I get the names for each contact type, but they are repeated three or four times.
Do I need an intersection table, or is the contacts table already acting as one? If I need an intersection table, what fields would I include. Also, must I create global "insert" and "update" commands to automatically add entries to this new table every time I add a new contact or real-estate property?
I've been beating my head against the wall for more than a week now. Sigh...
I have a database of real estate properties, and it contains several tables, but three in particular are giving me trouble.
The main table, called fileReview, contains most of the information about each property. A contacts table contains fields for name, address, telephone numbers, etc. Additionally, this table contains a select-menu field called contactTypes, which categorizes each contact as a borrower, realtor, lawyer, seller, etc. This field doesn’t actually store these words, but rather, an ID field (as a foreign key) from a third table, named contactTypes.
The main fileReview table has a corresponding field for each of these contact types, so that users can view the name of the lawyer, borrower, etc., connected with each particular piece of property.
I've set up the add screens for the fileReview table to separate all borrowers into one select menu, all laywers into another, and so forth. Then, each contact gets stored as its corresponding ID field (contacts.contactsID).
My problem arises in trying to display the names for these contacts rather than their ID numbers. I can accomplish this using a select queries as template globals. Unfortunately, I can only do it for one field at a time by editing the fileReview.defs file to include a foreign key as follows:
'fk' => {
'contacts' => {
'Borrower' => 'contactsID'
}
}
If I try to add more than one foreign key entry (e.g. 'Lawyer' => 'contactsID'), the ID numbers (or even nothing at all) appear in my records rather than the names. If I take out the foreign key completely, I get the names for each contact type, but they are repeated three or four times.
Do I need an intersection table, or is the contacts table already acting as one? If I need an intersection table, what fields would I include. Also, must I create global "insert" and "update" commands to automatically add entries to this new table every time I add a new contact or real-estate property?
I've been beating my head against the wall for more than a week now. Sigh...
Difficulty with M->M relationship, intersection table