Relaciones muchas a muchas en bases de datos RDBMS

¿Cuál es la mejor forma de manejar relaciones de muchos a muchos en una database RDBMS como mySQL?

He intentado usar una tabla dinámica para realizar un seguimiento de las relaciones, pero me lleva a cualquiera de los siguientes:

  • La normalización se queda atrás

  • Columnas que están vacías o nulas

¿Qué enfoque has tomado para apoyar las relaciones de muchos a muchos?

Haga un seguimiento de una relación de muchos a muchos en una tabla específica para esa relación (a veces llamada tabla de unión ). Esta tabla modela la relación como dos relaciones de uno a muchos apuntando en direcciones opuestas.

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)); 

A continuación, utiliza la tabla de unión para unir otras tablas a través de las keys externas.

 -- 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 'Fnetworking 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 = 'Fnetworking Nurk'; 

Usaría una tabla dinámica, pero no veo de dónde vienen sus problemas. Usando un ejemplo simple de estudiante / class:

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

O, como alguien más mencionó en respuesta a su pregunta de Estudiante / Profesor / Curso (que tendría una tabla adicional para almacenar el tipo de persona en el curso):

 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) 

La tabla de Estudiantes contiene información del estudiante, la tabla de Cursos almacena información del curso … y la tabla dinámica simplemente contiene los Id. De los estudiantes y cursos relevantes. Eso no debería llevar a ninguna columna nula / vacía ni a nada.

Además de la respuesta de Justin: si hace un uso inteligente de las restricciones de key externa, puede controlar lo que sucede cuando los datos se actualizan o eliminan. De esta forma, puedes asegurarte de que no termines con datos desnormalizados.