SQL: Many to many relationships

In the following I create a database including a “many to many” relationship. The document is created in Quarto accessed from RStudio. ¹

1. Loading libraries

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.1     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(DBI) 
library(RPostgres) 
library(dbplyr)


Attaching package: 'dbplyr'

The following objects are masked from 'package:dplyr':

    ident, sql

2. Establishing a database connection

I connect to a locally hosted PostgreSQL database using R package RPostgres.

con <- DBI::dbConnect( 
RPostgres::Postgres(),   
dbname = 'postgres',   
host = 'localhost',   
port = 5432,   
user = 'postgres',   
password = 'abcd'  )

3. Creating a database

Database diagram

The diagram is created with drawSQL tool

Creating tables


CREATE TABLE student (
id SERIAL,
name VARCHAR(128),
email VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);


CREATE TABLE course (
id SERIAL,
title VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);


CREATE TABLE member (
student_id INTEGER REFERENCES student(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES course(id) ON DELETE CASCADE,
ROLE INTEGER,
PRIMARY KEY (student_id, course_id)
);

In “members” table we don’t have id SERIAL column that in other tables functions as the primary key. Instead, we use the combination of student_id and course_id as the primary key.

Inserting data into tables


INSERT INTO student (name, email)
VALUES ('Jane', 'jane@abc.com');


INSERT INTO student (name, email)
VALUES ('Ed', 'ed@abc.com');


INSERT INTO student (name, email)
VALUES ('Sue', 'sue@abc.com');


select * from student;

3 records
id	name	email
1	Jane	jane@abc.com
2	Ed	ed@abc.com
3	Sue	sue@abc.com


INSERT INTO course (title)
VALUES ('Python');


INSERT INTO course (title)
VALUES ('SQL');


INSERT INTO course (title)
VALUES ('PHP');


select * from course;

3 records
id	title
1	Python
2	SQL
3	PHP

In the following we use in the “role” column 1 (teacher) or 2 (student)


INSERT INTO member(student_id, course_id, role)
VALUES (1, 1, 1);


INSERT INTO member(student_id, course_id, role)
VALUES (2, 1, 0);


INSERT INTO member(student_id, course_id, role)
VALUES (3, 1, 0);


INSERT INTO member(student_id, course_id, role)
VALUES (1, 2, 0);


INSERT INTO member(student_id, course_id, role)
VALUES (2, 2, 1);


INSERT INTO member(student_id, course_id, role)
VALUES (2, 3, 1);


INSERT INTO member(student_id, course_id, role)
VALUES (3, 3, 0);

Joining tables

The following operatrions are done from the command line/ psql shell

The table member is “in the middle” of the join.

In addition to joining, I order the records first by “role” (1 - teacher, 0 - student), and then - course title and student name.

Footnotes

Image copyright: Ch. R. Severance↩︎