Thank you for your help this has been of great help, and Helen, your writing
has really made me see more indepth on this issue.

Regards from Iceland
Olafur
-----Original Message-----
From: Helen Borrie (TeamIBO) [mailto:helebor@...]
Sent: 12. februar 2002 23:12
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Many to many rel.


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


Yahoo! Groups Sponsor
ADVERTISEMENT





___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.

___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]