Adding Data to Junction Table through Datasets

  • Thread starter Thread starter Abul Hasan
  • Start date Start date
A

Abul Hasan

Hi Everyone,

I am quite inexperienced in databses so I just wanna ask that how can we add
data to junction table after adding data to the Parent Tables.

I have Three Tables:

Topics ---> TopicsQuestions <---- Questions

topicId TopicId qid
topicName Qid Questions
Difficulty

There is a many to many relationship between these tables.

I know how to add data to the two primary tables but I don't know how the
data will be added to the Junction Table.

Thanks in advance for any kind of help.

Abul Hasan Lakhani.
MCP
 
Thanks a lot Earl.

I am sure this would be helpful to me as your answer has cleared my
confusion!

And yes i will try that group too, vb.databse

Abul Hasan.
 
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.
 
Back
Top