Many-to-Many Relationships in RDBMS Databases

Question

What is the best way to handle many-to-many relationships in an RDBMS database like mySQL?

Tried using a pivot table to track relationships, but this leads to one of the following:

• Normalization left behind

• Column empty or empty

What approach did you use to support many-to-many relationships?

Answer 1

Track the many-to-many relationships in the table specifically for this relationship (sometimes called the conversion table). This table models the relationship as two one-to-many relationships pointing in opposite directions.

CREATE TABLE customer ( customer_id VARCHAR NOT NULL, name VARCHAR NOT NULL, PRIMARY KEY (customer_id)); CREATE TABLE publication ( issn VARCHAR NOT NULL, name VARCHAR NOT NULL, PRIMARY KEY (issn)); -- Many-to-many relationship for subscriptions. CREATE TABLE subscription ( customer_id VARCHAR NOT NULL, FOREIGN KEY customer_id REFERENCES customer (customer_id), issn VARCHAR NOT NULL, FOREIGN KEY issn REFERENCES publication (issn), begin TIMESTAMP NOT NULL, PRIMARY KEY (customer_id, issn)); 

Then you use the navigation table to join other tables through it using foreign keys.

 -- Which customers subscribe to publications named 'Your Garden Gnome'? SELECT customer.* FROM customer JOIN subscription ON subscription.customer_id = customer.customer_id JOIN publication ON subscription.issn = publication.issn WHERE publication.name = 'Your Garden Gnome'; -- Which publications do customers named 'Fred Nurk' subscribe to? SELECT publication.* FROM publication JOIN subscription ON subscription.issn = publication.issn JOIN customer ON subscription.customer_id = customer.customer_id WHERE customer.name = 'Fred Nurk'; 

Answer 2

I would use a pivot table, but I don’t see where your problems are coming from. Using a simple student / class example:

 Student ------- Id (Primary Key) FirstName LastName Course ------ Id (Primary Key) Title StudentCourse ------------- StudentId (Foreign Key -> Student) CourseId (Foreign Key -> Course) 

Or, as someone else mentioned in response to your question "Student / Teacher / Course" (which would have an extra table for storing the type of person in the course):

 PersonType ---------- Id (Primary Key) Type Person ------ Id (Primary Key) FirstName LastName Type (Foreign Key -> PersonType) Course ------ Id (Primary Key) Title PersonCourse ------------ PersonId (Foreign Key -> Person) CourseId (Foreign Key -> Course) 

The Student table contains information about the student, the course table stores course information ... and the summary table simply contains the identifiers of the corresponding students and courses. This should not lead to any null / empty column or anything else.

Answer 3

In addition to Justin's answer: if you skillfully use foreign key constraints, you can control what happens when data is updated or deleted. This way you can make sure that you have not finished the de-normalized data.