Not understanding M:M relationship

[MCB]

I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?

 

[Ken Snell [MVP]]

You need three tables:

Categories table
CategoryID (primary key)
CategoryName

Subcategories table
SubcategoryID (primary key)
SubcategoryName

Cat2SubCat table
CategoryID (composite primary key with SubcategoryID) - (foreign key to
Categories)
SubcategoryID (composite primary key with CategoryID) - (foreign key to
Subcategories)


The Cat2SubCat table is the junction table that gives you the M:M
relationship.

 

[Sprinks]

A junction table solves it by creating a separate record for each combination
of category and subcategory. So the structures you need are:

Categories
------------
CategoryID
CategoryName

SubCategories
-----------------
SubCategoryID
SubCategoryName

JunctionTable (call it what you wish)
------------------------------------------
ID AutoNumber
CategoryID Number (Foreign Key to Categories)
SubcategoryID Number (Foreign Key to Subcategories)

So your records will look like:

[ID], 1, A
[ID], 1, B
[ID], 1, C
[ID], 2, B
[ID], 2, D
[ID], 2, F
[ID], 3, C
[ID], 3, E
[ID], 3, G

Hope that solves it.
Sprinks

 

[MCB]

Ahh, okay. I was under the impression that it was do-able without any kind of
duplication or replication. Makes sense now. Thanks!

A junction table solves it by creating a separate record for each combination
of category and subcategory. So the structures you need are:

Categories
------------
CategoryID
CategoryName

SubCategories
-----------------
SubCategoryID
SubCategoryName

JunctionTable (call it what you wish)
------------------------------------------
ID AutoNumber
CategoryID Number (Foreign Key to Categories)
SubcategoryID Number (Foreign Key to Subcategories)

So your records will look like:

[ID], 1, A
[ID], 1, B
[ID], 1, C
[ID], 2, B
[ID], 2, D
[ID], 2, F
[ID], 3, C
[ID], 3, E
[ID], 3, G

Hope that solves it.
Sprinks

I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?