php - MySQL: comprensión de las tablas de asignación

database-design mapping

Question

Cuando construyo un sistema de navegación de categoría para un directorio de negocios con una relación de muchos a muchos, entiendo que es una buena práctica crear una tabla de mapeo.

Tabla de categoría (CategoryId, CategoryName)
Business Table (BusinessId, BusinessName)
Tabla de asignación de categorías (ID de empresa, ID de categoría)

Cuando me una a la tabla de Categoría y a la tabla de Negocios para crear la tabla de asignación, ¿esto me daría una tabla que contiene todas las relaciones comerciales y de categoría posibles?

Tengo 800 categorías y 1000 listas de negocios. Eso me daría una tabla que contiene 800,000 posibles relaciones. Si es así, ¿cómo me enfocaría solo en las relaciones que existen? ¿Tendría que revisar todas las listas (800,000) marcándolas como verdaderas o falsas?

Me he sentido realmente confundido acerca de esto, por lo que cualquier ayuda sería muy apreciada.

Answer 1

Cuando me una a la tabla de Categoría y a la tabla de Negocios para crear la tabla de asignación, ¿esto me daría una tabla que contiene todas las relaciones comerciales y de categoría posibles?

Sí.

¿Tendría que revisar todas las listas (800,000) marcándolas como verdaderas o falsas?

No, debe usar la cláusula ON para establecer condiciones de unión.

SELECT <columns> FROM categories as c 
INNER JOIN mapping AS m
    ON m.CategoryId = c.CategoryId
INNER JOIN businesses as b
    ON m.BusinessId = b.BusinessId

Answer 2

Debe usar tablas de asignación cuando intente modelar una relación de muchos a muchos o de uno a muchos.

Por ejemplo, en una aplicación de libreta de direcciones, un contacto en particular podría pertenecer a cero, una o varias categorías. Si establece su lógica comercial de que un contacto solo puede pertenecer a una categoría, debe definir su contacto como:

Contact
--------------
contactid (PK)
name
categoryid (FK)

Category
--------------
categoryid (PK)
categoryname

Pero si desea permitir que un contacto tenga más de una dirección de correo electrónico, use una tabla de asignación:

Contact
--------------
contactid (PK)
name

Category
--------------
categoryid (PK)
categoryname

Contact_Category
--------------
contactid (FK)
categoryid (FK)

Luego puede usar SQL para recuperar una lista de categorías a las que está asignado un contacto:

seleccione a.categoryname de la Categoría a, Contacto b, Contact_Category c donde a.categoryid = c.categoryid y b.contactid = c.contactid y b.contactid = 12345;

select a.categoryname 
from Category a
inner join Contact_Category c on a.categoryid=c.categoryid
inner join Contact b on b.contactid=c.contactid
where b.contactid=12345;

Answer 3

usted solo pone las relaciones reales en la tabla de mapeo. Entonces, en promedio, una empresa está en 2 categorías, luego en su ejemplo, solo habría 2000 registros en la tabla de asignación, no 800,000

"Cuando me uno a la tabla Category y Business para crear la tabla de asignación" no se unen esas dos tablas para crear la tabla de asignación. Usted crea una tabla física real.

Answer 4

Al usar relaciones de muchos a muchos, la única manera realista de manejar esto es con una tabla de asignación.

Digamos que tenemos una escuela con maestros y estudiantes, un estudiante puede tener múltiples maestros y viceversa.

Entonces hacemos 3 tablas

student
  id unsigned integer auto_increment primary key
  name varchar

teacher
  id unsigned integer auto_increment primary key
  name varchar

link_st
  student_id integer not null
  teacher_id integer not null
  primary key (student_id, teacher_id)

La mesa de estudiantes tendrá 1000 registros
La mesa del maestro tendrá 20 registros
La tabla link_st tendrá tantos registros como enlaces (NO 20x1000, pero solo para los enlaces reales).

Selección
Selecciona, por ejemplo, alumnos por profesor usando:

SELECT s.name, t.name 
FROM student
INNER JOIN link_st l ON (l.student_id = s.id)   <--- first link student to the link-table
INNER JOIN teacher t ON (l.teacher_id = t.id)   <--- then link teacher to the link table.
ORDER BY t.id, s.id

Normalmente siempre deberías usar una inner join aquí.

Hacer un enlace
Cuando asigna un profesor a un alumno (o viceversa, es lo mismo) . Solo necesitas hacer:

INSERT INTO link_st (student_id, teacher_id) 
   SELECT s.id, t.id 
   FROM student s 
   INNER JOIN teacher t ON (t.name = ''Jones'')
   WHERE s.name = ''kiddo''

Esto es un mal uso de una unión interna, pero funciona siempre que los nombres sean únicos.
Si conoce las identificaciones, puede insertarlas directamente, por supuesto.
Si los nombres no son únicos, esto será un error y no se debe usar.

Cómo evitar enlaces duplicados
Es muy importante evitar enlaces duplicados, todo tipo de cosas malas sucederán si las tienes.
Si desea evitar la inserción de enlaces duplicados a su tabla de enlaces, puede declarar un índice unique en el enlace (recomendado)

ALTER TABLE link_st
  ADD UNIQUE INDEX s_t (student_id, teacher_id); 

O puede hacer el control en la declaración de inserción (no muy recomendable, pero funciona).

INSERT INTO link_st (student_id, teacher_id) 
  SELECT s.id, t.id
  FROM student s
  INNER JOIN teacher t ON (t.id = 548)
  LEFT JOIN link_st l ON (l.student_id = s.id AND l.teacher_id = t.id)
  WHERE (s.id = 785) AND (l.id IS NULL)

Esto solo seleccionará 548, 785 si esos datos no están ya en la tabla link_st , y no devolverá nada si esos datos ya están en link_st. Por lo tanto, se negará a insertar valores duplicados.

Si tiene una escuela de mesa, depende si un estudiante puede inscribirse en varias escuelas (poco probable, pero asumamos) y los maestros pueden inscribirse en varias escuelas. Muy posible.

table school
  id unsigned integer auto_increment primary key
  name varchar

table school_members
  id id unsigned integer auto_increment primary key
  school_id integer not null
  member_id integer not null
  is_student boolean not null

Puede enumerar a todos los estudiantes en una escuela así:

SELECT s.name
FROM school i
INNER JOIN school_members m ON (i.id = m.school_id)
INNER JOIN student s ON (s.id = m.member_id AND m.is_student = true)