At 05:16 PM 12-02-02 +0000, you wrote:

>Hi, I'm wondering if there is someone who can guide my in making a many to
>many relationship work.
>
>I have three table's:
>Table A: aid integer, bName string, bTel string
>Table B: bid integer, bName string, bTel string
>and then the connecting table
>Table C: cid integer, aid integer, bid integer
>
>Do I need cid in table C?

According to database theory, no. In practical application, yes, 1) because of the way the optimiser works in IB (if the same columns are involved in both the primary key and the foreign keys, it will often ignore the indexes) and 2) because the intersection table's main or only work will be to participate in joins. (Unlike Paradox, IB doesn't need chains of hierarchical primary keys to form relationships).

Use your cid to separate the primary key from the two foreign keys and, wait until *after* you have formed and committed the keys to create the unique constraint on aid and bid. You will need cascades on both FKs as well to enforce the obligatory relationship (both aid and bid must exist in order to form the intersection). If a blank aid or a blank bid is permissible then represent it in tables A and B with a zero-equivalent, not a null.

>How would I set this up using TIB_Query?

It really depends on how you want to present the data.

To present a flat view of the relationship you might use a join across all three tables and use xxxSQL properties for DML, calling stored procedures to maintain all three tables.

You can form master-detail relationships using two or all three tables and you also have the option to invert the m/d relationships so that the (invisible) intersection table query is master to one or two details formed from TableA and/or TableB respectively. You can also join tables in several different ways to form masters and details; and you can "invert" relationships to suit requirements.

I'm doing such things in the online FAQ app with the keywords. My local maintenance app stores an unlimited number of keywords for each topic. Each keyword gets stored exactly once and all keywords are available to any topic. If a new keyword gets added, there are triggers to create the keyword-topic relationships. On the maintenance form, I display the topics along with a box showing all the keywords for that topic. (Topic is master, KeywordTopic - invisible - is detail, and Keyword - in the box - is detail to the invisible dataset).

When you select a keyword at the web interface you are passing a parameter which collects up all topics for which an intersection row exists for that keyword. The master-detail relationship gets inverted and the dataset in which the eligible topics are returned (on the results page) is the detail to the KeywordTopic intersection.

When you have many:many requirements, it really brings home to you the abstract nature of the relational database and the power of SQL.

If you like, provide a scenario of the requirements for these data.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com