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?

Answers


Ctx February 2016

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

CREATE TABLE users (
                    userid int,
                    username text,
                    ...
)

and

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.

Consider:

student
id   name   mag_id
---------------------
 1   Joe     1
 1   Joe     2
 1   Joe     3
 2   Tom     1
 etc

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:

student
id    name
 1    Joe
 2    Tom

magazine
id    name
 1    Weekly
 2    Sports

junction
id  stud_id    mag_id
 1     1         1
 2     1         2
etc

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

Search




Leave an answer