This is where the rubber meets the road as far as I'm concerned -- or put
another way, where theory meets implemenation. There are some excellent
database gurus on the vb.database forum who might give you a better
explanation, but this is how I do it:
a. You will not be adding items to both main tables at the same time, so at
the time you add an item (with its associated Primary key) to EITHER main
table, you insert a corresponding Foreign key to the linking table (what you
call "junction").
b. At the time you add an item and Primary key to the OTHER main table, you
add a SINGLE Foreign key to the linking table for EACH item that has a
Foreign key from the first table.
In your example:
Let's say I add a Topic first (clearly we want our users to be able to add
topics or questions whenever they would like, with no priority given to one
or the other). At the time I add a Topic, I generate a Primary key in the
Topics table and COPY this Primary key into the TopicsQuestions table as a
Foreign Key. Here it sits unassociated until such time as Questions are
added that relate to the Topic (remember that linking tables have no Primary
key of their own and thus their TWO foreign keys comprise a unique Composite
Primary Key).
Now a user comes along and sees the Topics listed in the app. But no
Questions are shown for that Topic. So as they add a Question, they:
1. Create a Primary key for the Question (auto-generated when they add the
Question most likely).
2. Create one copy of the Question Primary key for EACH associated Topic.
And obviously, we could have started this by adding Questions first and at
some later date add the Topics. Remember that the sole purpose for a linking
table is to reduce data redundancy, but at the same time, it creates
independence for the main tables. The only "redundancy" exists in the
linking table, but since these are merely foreign keys, this is acceptable.
Topics ---> TopicsQuestions <---- Questions
ID TopicID / QuestionID ID
1 1 3 1
2 1 5 2
3 1 6 3
4 2 4 4
3 4 5
4 <null> 6
<null> 1
<null> 2
Here we have threee topics associated with question 3, two topics associated
with question 4, NO Questions YET assocated with Topic 4, and no Topics YET
assocaited with Questions 1 and 2.
