If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Not understanding M:M relationship
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? |
#2
|
|||
|
|||
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. -- Ken Snell MS ACCESS MVP "MCB" wrote in message news 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? |
#3
|
|||
|
|||
A junction table solves it by creating a separate record for each combination
of category and subcategory. So the structures you need a 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" wrote: 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? |
#4
|
|||
|
|||
MCB wrote: I don't understand how a junction table would fix that If you can read SQL code, you may find this interesting: http://www.intelligententerprise.com/010101/celko.jhtml And if you know how to get an ADO connection to your Jet database, you can even try out the examples. You will need to use the same data type throughout (e.g. VARCHAR(30) in place of INTEGER) and use the longer CONSTRAINT syntax rather than the REFERENCES contraction e.g. the first example relationship table would be more like: CREATE TABLE Pairs ( boy_name VARCHAR(30) NOT NULL, girl_name VARCHAR(30) NOT NULL, CONSTRAINT fk__Pairs__Boys FOREIGN KEY (boy_name) REFERENCES Boys (boy_name), CONSTRAINT fk__Pairs__Girls FOREIGN KEY (girl_name) REFERENCES Girls (girl_name) ); Jamie. -- |
#5
|
|||
|
|||
Ahh, okay. I was under the impression that it was do-able without any kind of
duplication or replication. Makes sense now. Thanks! "Sprinks" wrote: A junction table solves it by creating a separate record for each combination of category and subcategory. So the structures you need a 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" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
PK not required to create FK 'relationship' | Jamie Collins | New Users | 3 | October 28th, 2004 09:13 AM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |