Many-to-many relations in RDBMS databases

Question

What is the best way of handling many-to-many relations in a RDBMS database like mySQL?

Have tried using a pivot table to keep track of the relationships, but it leads to either one of the following:

• Normalization gets left behind

• Columns that is empty or null

What approach have you taken in order to support many-to-many relationships?

Answer 1

Keep track of a many-to-many relationship in a table specifically for that relationship (sometimes called a junction 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));

You then use the junction table to join other tables through it via the 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 issues 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 somebody else mentioned in response to your Student/Teacher/Course question (which would have an additional table to store 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 student information, the Course table stores course information...and the pivot table simply contains the Ids of the relevant students and courses. That shouldn't lead to any null/empty columns or anything.