Many-many-many relationship

1. Senior Member
Join Date
Dec 1969
Posts
2,892

## Many-many-many relationship

Hi all:<BR><BR>I am trying to normalize some data while building a small keyword search. The main data is a list of resource materials available from a training library. Each resource will be listed under a very basic training category. Each resource will also be searchable by a set of appropriate keywords. Each resource category can share keywords with other categories. It seems that this is a many-many-many relationship.<BR><BR>If a junction table is the solution to a many-many relationship, would two junction tables be the solution to a many-many-many relationship?<BR><BR>An example:<BR><BR>Table R<BR>Pk Resource<BR>1 Book 1<BR>2 Book 2<BR>3 Video 1<BR>4 Video 2<BR>5 Article 1 <BR><BR>Table C<BR>Pk Category<BR>1 H.R.<BR>2 Training<BR>3 Development<BR>4 Budgeting<BR><BR>Table K<BR>Pk Keyword<BR>1 MacMillan Theory<BR>2 Practice<BR>3 Application<BR>4 Compliance<BR>5 Reporting<BR>6 Best Practice<BR><BR>So, if each resource can be under more than one category, AND each resource can have more than one keyword, would I create junction table 1st of all variations of resources to their categories….<BR> <BR><BR>Table J1<BR>Pk Rpk Resource Cpk Category<BR>1 1 Book 1 2 Training<BR>2 1 Book 1 3 Development<BR>3 2 Book 2 1 H.R.<BR>4 2 Book 2 3 Development<BR>5 2 Book 2 4 Budgeting<BR>6 4 Video 2 2 Training<BR>7 4 Video 2 4 Budgeting<BR><BR>… then match this to another junction table of all J1s to their keywords, like this….<BR><BR>Table J2<BR>Pk J1pk Keyword Kpk <BR>1 1 Practice 2<BR>2 1 Application 3<BR>3 1 Compliance 4<BR>4 2 MacMillan 1<BR>5 2 Best Practice 6<BR>6 3 Practice 2<BR>7 3 Application 3<BR><BR>Like that? In this way Table J1 creates the Resource/Category relationship and then Table J2 creates the J1/Keyword relationship.<BR><BR>The users want to be able to search for Resources mainly by Category, with the keywords acting as a an additional search parameter.<BR><BR>And thoughts on this would be greatly appreciated.<BR><BR>Thanks<BR><BR>KurtW<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, hopefully...

...you don&#039;t *REALLY* have the Resource and Category fields in table J1 !!!<BR><BR>The whole point of normalization is to avoid repeating strings. Linking via just the PK/FK stuff.<BR><BR>Ditto in J2, just the FKs, no "Keyword" field.<BR><BR>Yes?<BR><BR>So you want to end up with<BR> Book1 Training Practice<BR> Book1 Training Application<BR> Book1 Training Compliance<BR> Book1 Development Practice<BR> Book1 Development Application<BR> Book1 Development Compliance<BR>???<BR><BR>Yep, looks right to me.<BR><BR><BR><BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Many-many-many relationship

Rule of thumb for many to many tables<BR>"Take each Join and create a Table on the Join"<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
2,892

## RE: Well, hopefully...

Yes, those fields were include in post only for clarity.<BR><BR>So, it&#039;s RIGHT? WAHOO!<BR><BR>Thanks Bill.<BR><BR>KurtW

5. Senior Member
Join Date
Dec 1969
Posts
2,892

## RE: Many-many-many relationship

I think I follow that. But that brings up another point I hadn&#039;t even thought far enough to get to: how do I do multiple joins in an sql query?<BR><BR>I&#039;ve done basic INNER JOINS between two tables. Can I use other JOINS as expressions in the same sql, then?<BR><BR>Like this, I guess:<BR><BR>"SELECT A.snark, A.Blap, A.Froom, B.smurg, B.grool, C.bib, C.bloob "_<BR>& "FROM Grup AS A "_<BR>& INNER JOIN Ploog AS B "_<BR>& "ON A.snark = B.grool "_<BR>& "(JOIN Dip AS C ON B.grool = C.bib) "_<BR>& "WHERE A.Foo = C.Bar "<BR><BR>Close?

6. Senior Member
Join Date
Dec 1969
Posts
11,247

## That's it give or take a grup <nt>

;-)

7. Senior Member
Join Date
Dec 1969
Posts
2,892

## RE: That's it give or take a grup <nt�

Thanks WK. Appreciated.<BR><BR>KurtW

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•