Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Difficulty with M->M relationship, intersection table

Quote Reply
Difficulty with M->M relationship, intersection table
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...
Quote Reply
Re: [Halito] Difficulty with M->M relationship, intersection table In reply to
I'm still struggling. Crazy I don't think I'm creating the intersection table correctly. It has three fields: an auto-incrementing primary key field and two integer fields that refer to the primary keys of two other tables. Each time I try to make the latter two integer fields unique, I get the following error:

GT::SQL::Editor (1312): Unknown method 'alter_add_unique_sql' called at /my_path/admin/GT/SQL/Editor.pm line 312

What is going on here?
Quote Reply
Re: [Halito] Difficulty with M->M relationship, intersection table In reply to
Okay, I don't have to learn the M->M relationship right now. It turns out my DB doesn't need that kind of relationship between tables anywhere.

However, I do need to figure out how to auto-insert a record in a second table when I write a record to the first one.

I want all records in table2 to correlate to corresponding records in table1 (1:1 relationship). As table2 has more than 300 fields, I'm assuming it's a good idea to put them in a separate table since they won't be accessed as frequently by users as the data in table1.

I currently have a global, add_table2, that I cobbled together from an example in the help documentation. It doesn't produce an error, but it doesn't add a record to the second table, either. It looks like this:

sub {
my $sql = shift;
my $rel = $DB->table (qw/table1 table2/);
$rel->insert ( { table2_ID => 'table1_ID' } );
}


I have the ID field in table2 defined as a non-auto-incrementing foreign key pointing to table1_ID. I'm thinking each table should share the same ID number from table1 so I can easily switch back and forth from one table to the other in my HTML.

Can anyone help? Thanks.
Quote Reply
Re: [Halito] Difficulty with M->M relationship, intersection table In reply to
Okay, I managed to auto-add a row to a second table whenever a row is added to the first one. But now I need to auto-delete it later. Can anyone help?

The auto-add global:
sub {
my $tags = shift;
my $id = $tags->{table1_ID};
my $relation = $DB->table('table2');
my $sth = $relation->insert ( { table2_ID => $id } );
return;
}


Then you insert a call to this global--like <%auto-add%>--in your add_success template.

My global for auto-deleting won't work because DBMan SQL uses a <%row_num%> variable in the delete_search_results template. Anyone know how to adjust my global to make it work? Here it is:

Auto-delete global:
sub {
my $tags = shift;
my $id = $tags->{table1_ID};
my $relation = $DB->table('table2');
my $sth = $relation->delete ( { 'table2_ID' => '$id' } );
}