Home Ask Login Register

Developers Planet

Your answer is one click away!

Papyrusko February 2016

Use (or not) of junction tables

I'm in the process of developping a small project for school, and I'm unsure on a design choice.

Here is what I am trying to achieve :

I have a table of users, who are allowed to subscribe/unsubscribe from different magazines stored in a second table.

My question is fairly easy. Does that justify the use of a junction table? I was initially thinking about just including the IDs of subscribers directly in the magazine table with an additional column. It felt sloppy.

On the other hand, if I use a junction table, one way or the other I will still end up storing multiple IDs (users or magazines) into a single column, so it feels like a junction table serves no purpose.

What's your take on it?


Ctx February 2016

No, the junction table would exactly avoid having multiple userids or magazineids in one row. You have two relations:

                    userid int,
                    username text,


CREATE TABLE magazines (
                    magid int,
                    magname text,

Your junction table connects these tables (a Many-to-Many-relation):

CREATE TABLE user_magazine (
                    userid int,
                    magid int

Now for each abonnement make an entry in the junction table user_magazine. Usually you would also use foreign key constraints for the fields in user_magazine and set appropriate indices.

I would definitively recommend this design over your second proposed option.

Stidgeon February 2016

You are dealing with a many to many relationship - a student can subscribe to more than one magazine, and a magazine can have more than one subscriber.

The way to deal with this is with a junction table, otherwise you end up with duplicated data within either the student or magazine table.


id   name   mag_id
 1   Joe     1
 1   Joe     2
 1   Joe     3
 2   Tom     1

There's needless duplication of entries here in order to accommodate the M-N relationship.

A junction table is going to make things way easier in the long run:

id    name
 1    Joe
 2    Tom

id    name
 1    Weekly
 2    Sports

id  stud_id    mag_id
 1     1         1
 2     1         2

This way you have unique entries for each student-magazine pairing, less duplication of other field entries, and an easier time querying the data.

Post Status

Asked in February 2016
Viewed 1,508 times
Voted 8
Answered 2 times


Leave an answer

Quote of the day: live life