Populating Many To Many Tables

[AnotherMuggle]

Hi,

I am new to MySQL and am having trouble understanding a few concepts.

Basically, I have created my database and it contains lots of many to many tables.

An example:

FORUM TABLE
TOPIC TABLE

FORUM_TOPIC TABLE

What I want to know is, the FORUM TABLE contains all the forums for my site and the TOPIC TABLE contains all the topics for my site.

The FORUM_TOPIC TABLE contains the references between the forums and the topics. i.e. forumID and topicID to relate the topics to the correct forums.

My question is, I have the data in FORUM TALBE and TOPIC TABLE but how/when am I supposed to populate the link table FORUM_TOPIC?

Thanks,
TK

[jasonahoule]

You should be inserting the primary keys from the FORUM table and the TOPIC table into the FORUM_TOPIC table when you do the insert into the TOPIC table.

Is that what you are asking?

[yamaharuss]

When a new topic is created the matching forum must be referenced and this is done via the forum_topic table.

If you create a new topic with an ID of 5005 and it's matching forum has an ID of 32, then you simply insert that reference into the forum_topic table. This type of relational system makes querying much more efficient.

[AnotherMuggle]

OK thanks for the responses.

I think I have that part working OK now.

Still a little puzzled about things however...

When I search the database, is it the FORUM_TOPIC link table that I should search? I am still not 100% of the way to use the relationship table.

Thanks in advance ;-)

[yamaharuss]

something along the lines of

select T.topictitle, T.topiccomment from topics T inner join forum_topic FT on FT.topicID = T.topicID where FT.forumID = 32

32 of course being the forum queried

[chazzy]

so... just wondering, but why is this a Many to Many relationship?

From topic's perspective, it's a Many to One relationship, meaning many topics belong to a single forum. From forum's perspective, it's one to many.

Unless you're creating a new concept of forums that is (kind of like a keyword cloud)

[AnotherMuggle]

so... just wondering, but why is this a Many to Many relationship?

From topic's perspective, it's a Many to One relationship, meaning many topics belong to a single forum. From forum's perspective, it's one to many.

Unless you're creating a new concept of forums that is (kind of like a keyword cloud)

As it happens, this is something I discussed with my tutor.

This is what he said:

"Lets think about the relationships: a user can make many posts to a discussion thread (topic). A post is from one user. A forum has many topics and a topic relates to one forum. So there is no direct relationship between user and forum. The user accesses forum through making a post. So I would have: User --------1:m------UserPost-------m:1------Topic where the primary key for Topic would be Topic ID and the primary key for UserPost will be a compound consisting of UserId, TopicID, Post Date, Post Time.............this will allow a user to make many posts about the same topic. Also, a forum can have many topics and a topic may appear on many forums. So I would relate Topic and Forum with a link entity called ForumTopic with a compound primary key of Forum ID and Topic ID."

Is this wrong? What would you recommend?

Thanks,
TK

[chazzy]

every application has its own rules. the one that's catching me off guard (though not necessarily) is this comment from your tutor:


Also, a forum can have many topics and a topic may appear on many forums.


Can a topic appear in many forums?

[AnotherMuggle]

every application has its own rules. the one that's catching me off guard (though not necessarily) is this comment from your tutor:
Can a topic appear in many forums?

In the way that I have the site designed, no.

I think I will just amend the tables to the relationship that you described.

[NogDog]

Although sometimes we have people here posting the same topic in multiple forums because they think it's applicable to each, so maybe it's time for a new forum paradigm that allows linking a single thread to multiple forums?

Mostly just kidding, as I'm sure it would be abused, especially by spammers.

[chazzy]

actually, it fits the "Tag Cloud" design paradigm, but replace forum with category/tag (in my opinion, tag is a chic/web 2.0 way of saying category). Instead of having multiple "forums," you have a single forum with everything, and a list of tags that you can click on to see posts that apply to it. This essentially gives the illusion of multiple forums per topic by giving each multiple tags.

http://en.wikipedia.org/wiki/Tag_cloud