What is the point of a junction table

Question

I've been reading up on normalization and relationships and am curious -- is the only point of a junction table to store attributes that don't make sense in either of the parent tables?

For example, if you have a Chat and Users table, and a chat can have many users and visa versa, couldn't you just have a one-to-many relationship on both tables without a junction table?

That is, couldn't you say that a chat has many users and that a user has many chats?

Why would you have to create a table such as ChatUsers and then say that a chats has many chatUsers and that a user has many ChatUsers?

The only reason I can think of is that you want to add attributes like number of undread messages per chat, or notification settings, which only make sense in a ChatUsers table.

If this is not the case, and you don't need to add anything, does the concept of many-to-many even make sense? wouldn't it just be easier to think of it as two one-to-many relationships?

Answer 1

This is old but the question is not quite answered. Suppose a Chats table that contains a list of Chats, i.e. each record represents one Chat, and a Users table that contains a list of Users, i.e. each record represents one User.

Then suppose you want to create a foreign key in each table linking to the other. Each Chat record will point to one-and-only-one User record, but multiple Chat records could point to the same User. Similarly, each User record will point to one-and-only-one Chat record, but multiple User records could point to the same Chat record.

This would not satisfy the requirement of many-to-many since each Chat record could reference only one User record and each User record could reference only one Chat record.

A junction table fixes this. In this example, junction table might only contain two fields:

• Chat record key
• User record key

Suppose a Chat has 12 Users, then the junction table will contain (among other records) 12 records with the Chat record key. Each junction record will also contain an entry for the User record key. There will be 12 records with the Chat record key duplicated 12 times while each User record key (within the 12) will be unique.

Suppose a User is in 7 Chats, then the junction table will contain (among other records) 7 records with the User record key. Each junction record will also contain an entry for the Chat record key. There will be 7 records with the User record key duplicated 7 times while each Chat record key (within the 7) will be unique.

To get a list of all Users in a particular Chat record key of C, query where Chat Record Key = C.

To get a list of all Chats containing a particular User record key of U, query where User Record Key = U.

In this example, the junction table contains no additional attributes; it merely tracks which Users and Chats exist together.

Answer 2

The use of the junction table takes your data to its lowest possible normalised level, it should be used where there is something that could be duplicated across multiple entires such as a date or a time.

I cannot think of a specific example with a Chat and Chat User table, but one ive come across a lot is in relation to addresses.

As individuals both you and myself could live at the same address, but at different times. It wouldnt be appropriate to hold a date of when I lived at an address in a user table (as I could live at multiple addresses). It also wouldnt be appropriate to record a date in the address table (as this could lead to multiple address lines), as such you should end up with tables with the following design and data.

User_Table

User_Id	User_Name
U1	John Smith
U2	Jane Smith
U3	Rebecca Smith

Address_Table

Address_Id	Address
AD1	1 High Street
AD2	2 High Street
AD3	3 High Street
AD4	4 High Street

Address_History_Table

Add_His_ID	User_Id	Address_Id	Start_Date	End_Date
AH1	U1	AD1	01/09/2000	01/01/2019
AH2	U1	AD2	02/01/2019	31/08/2022
AH3	U2	AD1	02/01/2019
AH4	U3	AD2	01/08/1965	31/08/2022

In this example U1 (John Smith) and U2 (Jane Smith) have both lived at address AD1(1 High Street) but not at the same time. In this example Jane moved in (02/01/2019) the day after John moved out (02/01/2019).

U1 (John Smith), then moved into AD2 (2 High Street) from the 02/01/2019 with no end date. U3 (Rebecca Smith) lived in this address already (start_date of 01/08/1965) and lived there until 31/08/2022.

From this we can identify that John and Jane havent lived together, while John and Rebecca did (From the 02/01/2019 to 31/08/2022).

The use of the junction table will very much depend on your data. In your chat example if two users were in the same chat for the exact same time, could lead to a duplication if a time for example was recorded.

A juntion table can sometimes just be three columns

• Primary Key of the table
• Foreign Key of the user table
• Foreign Key of the chat table

The junction table would then just be used to denote which chat linked to which user, and dosent need to contain any other information.