Many To Many and composite Keys

[Reader]

Many to Many

I have a basic question, I know for a fact that all many to many relationships have to be broken into two one to many relationships using a junction table. However does that mean it is (a must )to have the two foreign key of the parent tables have to be the (primary composite key) of the junction table.

For example, let’s say I have 5 tables that I need to establish many to many relationships between them. Is it correct to establish a junction table with all 5 foreign keys of the parent tables as composite primary key in the junction table to!

Hope the above making sense!!!

Many thanks

 

[tehNellie]

They dont have to be a composite key, but that can be handy if you want to stop duplication. Neither do they have to be the primary key of the junction table, there might be circumstances where you want to store additional information in the junction table itself or assign each record in the junction table it's own ID/Primary key.

I'm not sure that I'd attempt to utilise a single junction table to cater for relationships between more than two tables.

 

[Reader]

Thanks tehllie for your reply

But to be honest with you I’m still confused….

Please correct me if I am wrong, if this is true “the foreign keys of two or more tables don’t have to be composite nor set as a primary Key in the junction table to map M-M relationship” then all the Parent tables having foreign keys in one (don’t wanna call it junction table for now) table must be having a Many to Many relationship(s). I don’t think that’s quit right.

As such we will never have a “common” table with fields that two or more tables can be linked to in 1-M relationships without having M-M relationships between those tables.

The concept of junction table and the foreign keys in a junction table and the relationship between the foreign keys (if any) within the junction table is not so clear to me. I don’t have a solid understanding on the junction table and common table, I guess am trying to find or define the prerequisites/parameters/requirement to have in place to refer to a table as a junction table .

Does it sound too complicated.

Many thanks to all

 

[The_Doc_Man]

Reader, you are not alone in being confused by junction tables the first time you run into them. So for what it is worth, you are normal in that regard.

Let me try to toss some ideas your way.

When you design a table, you look for the entities that you wish to represent. You put the elementary entities each in their own table. Say, in a business, you put employees in one table. Job titles in another. Departments in another. So how would you do junction tables to handle this case?

If you have an EmpID as an employee identification and it is the prime key (PK) of the employee tabl, you have a start.

Let's also say that you have job codes to define the jobs in this company. The JobID is the PK of the jobs table.

Then you have department codes with a DeptID as the PK for department tables.

Now, if you have a junction table with EmpID, JobID, DeptID, start date, end date (and allow end date to be either nil or some impossible future date), you have the makings of an employment history table. So there is your three-way join example. Add salary to this junction and it becomes more and more like a detailed employment history. BUT - here is where a question of yours comes into play. The triplet <EmpID, JobID, DeptID> cannot be a compound PK in this case - because if I give the employee a raise in salary without changing titles or departments, you have two entries with the same triplet. This means the <EmpID, JobID, DeptID> is NOT a candidate key.

The more common method for this sort of thing is to just include some sort of autonumber - if you need any PK at all. Not every table needs a PK, though it rarely hurts to have one.

Now, if this were not an employee history table but only an employee STATUS table with NO IMPLIED HISTORY - then the triplet <EmpID, JobID, DeptID> is still a candidate for PK. So the question that decides when you do - or do not - want to use a compound PK is whether the table is a status table or a history table.

This exploration into design issues might help clarify the kinds of questions to ask. If this did not help, please post back with your questions. I or any of the other forum members will try to help you see what is happening with that junction table issue.