¿Cómo almacenar múltiples opciones en una sola tabla?

Quiero diseñar una aplicación para el cálculo de resultados.

Primero, necesito saber cómo almacenar registros en una base de datos MySQL de tal manera que los estudiantes puedan tener tantos cursos adjuntos, por ejemplo, el estudiante A puede tener 6 asignaturas adjuntas, mientras que el estudiante B puede tener 12 asignaturas adjuntas a él. .

En este caso, necesito saber cómo podría diseñar una estructura de base de datos que permita a un campo almacenar tantos temas como sea posible en forma de una matriz .

Cualquier sugerencia o una mejor manera de manejar esto será muy apreciada.

Lea sobre normalización de datos , conceptos de Indización general y restricciones de clave externa para mantener los datos limpios con integridad referencial. Esto te pondrá en marcha.

Almacenar datos en matrices puede parecerle natural en papel, pero para el motor de base de datos el rendimiento generalmente no tiene uso de índices. Además, en el Día 2 encontrará que obtener y mantener sus datos será una pesadilla.

Lo siguiente debería ayudarte a empezar bien mientras juegas. Se une también

create table student ( studentId int auto_increment primary key, fullName varchar(100) not null -- etc ); create table dept ( deptId int auto_increment primary key, deptName varchar(100) not null -- Economics -- etc ); create table course ( courseId int auto_increment primary key, deptId int not null, courseName varchar(100) not null, -- etc CONSTRAINT fk_crs_dept FOREIGN KEY (deptId) REFERENCES dept(deptId) ); create table SCJunction ( -- Student/Course Junction table (aka Student is taking the course) -- also holds the attendance and grade id int auto_increment primary key, studentId int not null, courseId int not null, term int not null, -- term (I am using 100 in below examples for this term) attendance int not null, -- whatever you want, 100=always there, 0=he must have been partying, grade int not null, -- just an idea -- See (Note Composite Index) at bottom concerning next two lines. unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term) key (courseId,studentId), CONSTRAINT fk_sc_student FOREIGN KEY (studentId) REFERENCES student(studentId), CONSTRAINT fk_sc_courses FOREIGN KEY (courseId) REFERENCES course(courseId) ); 

Crear datos de prueba

 insert student(fullName) values ('Henry Carthage'),('Kim Billings'),('Shy Guy'); -- id's 1,2,3 insert student(fullName) values ('Shy Guy'); insert dept(deptName) values ('History'),('Math'),('English'); -- id's 1,2,3 insert course(deptId,courseName) values (1,'Early Roman Empire'),(1,'Italian Nation States'); -- id's 1 and 2 (History dept) insert course(deptId,courseName) values (2,'Calculus 1'),(2,'Linear Algebra A'); -- id's 3 and 4 (Math dept) insert course(deptId,courseName) values (3,'World of Chaucer'); -- id 5 (English dept) -- show why FK constraints are important based on data at the moment insert course(deptId,courseName) values (66,'Fly Fishing 101'); -- will generate error 1452. That dept 66 does not exist -- That error is a good error to have. Better than faulty data -- Have Kim (studentId=2) enrolled in a few courses insert SCJunction(studentId,courseId,term,attendance,grade) values (2,1,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknown attendance/grade insert SCJunction(studentId,courseId,term,attendance,grade) values (2,4,100,-1,-1); -- Linear Algebra A insert SCJunction(studentId,courseId,term,attendance,grade) values (2,5,100,-1,-1); -- World of Chaucer -- Have Shy Guy (studentId=3) enrolled in one course only. He is shy insert SCJunction(studentId,courseId,term,attendance,grade) values (3,5,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknow attendance/grade -- note if you run that line again, the Error 1062 Duplicate entry happens. Can't take same course more than once per term 

Algunas preguntas simples

¿Qué curso es en qué departamento?

mostrar todo, usa alias de tabla (abreviaturas) para hacer menos tipeo, legibilidad (a veces) mejor

 select c.courseId,c.courseName,d.deptId,d.deptName from course c join dept d on c.deptId=d.deptId order by d.deptName,c.courseName -- note the order +----------+-----------------------+--------+----------+ | courseId | courseName | deptId | deptName | +----------+-----------------------+--------+----------+ | 5 | World of Chaucer | 3 | English | | 1 | Early Roman Empire | 1 | History | | 2 | Italian Nation States | 1 | History | | 3 | Calculus 1 | 2 | Math | | 4 | Linear Algebra A | 2 | Math | +----------+-----------------------+--------+----------+ 

¿Quién está tomando el curso de World of Chaucer este trimestre?

(conociendo el courseId = 5)

El siguiente se beneficia de uno de nuestros índices compuestos en SCJunction. Un compuesto es un índice en más de una columna.

 select s.StudentId,s.FullName from SCJunction j join student s on j.studentId=s.studentId where j.courseId=5 and j.term=100 +-----------+--------------+ | StudentId | FullName | +-----------+--------------+ | 2 | Kim Billings | | 3 | Shy Guy | +-----------+--------------+ 

Kim Billings está inscrito en lo que este término?

 select s.StudentId,s.FullName,c.courseId,c.courseName from SCJunction j join student s on j.studentId=s.studentId join course c on j.courseId=c.courseId where s.studentId=2 and j.term=100 order by c.courseId DESC -- descending, just for the fun of it +-----------+--------------+----------+--------------------+ | StudentId | FullName | courseId | courseName | +-----------+--------------+----------+--------------------+ | 2 | Kim Billings | 5 | World of Chaucer | | 2 | Kim Billings | 4 | Linear Algebra A | | 2 | Kim Billings | 1 | Early Roman Empire | +-----------+--------------+----------+--------------------+ 

Kim está abrumada, así que suelta la clase de matemáticas

 delete from SCJunction where studentId=2 and courseId=4 and term=100 

ejecuta esa statement de arriba que muestra lo que está tomando Kim:

 +-----------+--------------+----------+--------------------+ | StudentId | FullName | courseId | courseName | +-----------+--------------+----------+--------------------+ | 2 | Kim Billings | 5 | World of Chaucer | | 2 | Kim Billings | 1 | Early Roman Empire | +-----------+--------------+----------+--------------------+ 

Ah, mucho más fácil plazo. Papá no será feliz sin embargo.

Tenga en cuenta cosas como SCJunction.term. Se puede escribir mucho sobre eso, en este momento me saltearé sobre todo, aparte de decir que también debería estar en un FK en alguna parte. Es posible que desee que su término se parezca más a SPRING2015 y no a int.

Y en cuanto a id’s ir. Esta es la forma en que lo haría. Es preferencia personal. Requeriría conocer los identificadores, buscarlos. Otros pueden optar por tener un curso con algo así como HIST101 y no 17. Es mucho más legible (pero más lento en el índice (apenas). Haz lo que es mejor para ti.

Nota Índice Compuesto

Un índice compuesto (INDEX significa KEY, y viceversa) es aquel que combina múltiples columnas para la recuperación rápida de datos. Los pedidos se cambian para los dos compuestos en la tabla SCJunction para que, dependiendo del universo de consultas que vaya después de sus datos, el motor db pueda elegir qué índice usar para la recuperación más rápida en función de la columna de la izquierda a la que va después .

En cuanto a la clave única, n. ° 1, el comentario al lado que indica que no se imponen duplicados (es decir, datos no deseados) es bastante autoexplicativa. Por ejemplo, el primer término 1 del curso 1 del alumno no puede existir dos veces en esa tabla.

Un concepto crucial para comprender es el concepto de ordenamiento de columnas left-most izquierda en un índice.

Para las consultas que van solo después de studentId , se studentId la clave que tiene studentId listed first ( left-most ). En consultas que van solo después de courseId , se utiliza la clave que tiene courseId left-most. En las consultas que van después de studentId y courseId, el motor de db puede decidir qué clave compuesta usar.

Cuando digo “ir después”, me refiero a la on clause o where clause .

Si no se tuvieran esas dos claves compuestas (con la columna 1 y 2 invertidas), entonces en las consultas donde la columna solicitada no está indexada a la left-most , no se beneficiaría con el uso de la clave y sufriría un escaneo de tablas lento para los datos regresar.

Entonces, esos dos índices combinan los siguientes 2 conceptos

  • Recuperación de datos rápida basada en el extremo izquierdo o ambos (columnas studentId y courseId)
  • Imponer la no duplicación de datos en esa tabla en función de studentId, courseId y los valores de término

The Takeaway

Lo importante es que las tablas de unión permiten una recuperación rápida del índice y una administración sensata de los datos en comparación con los datos delimitados por comas (mentalidad de matriz) agrupados en una columna, y toda la miseria del uso de dicha construcción.

Para mayor completitud, no en una cuestión de que esta es una solución general recomendada:

MySQL proporciona el tipo de datos JSON , que permite almacenar y recuperar objetos y matrices en el formato JSON .

De esta forma, puede almacenar objetos enteros y matrices en un campo, como una matriz se vería así:

  ['subject_1', 'subject_2', 'subject_3'] 

Especialmente los principiantes no saben esto, y reinventan la rueda mediante otra implementación de cadenas separadas por comas o usando enfoques de serialización / deserialización dependientes del lenguaje.

Al menos JSON se usa muy comúnmente y se analiza fácilmente como un formato de intercambio de datos.

Existen casos de uso válidos para usar matrices y objetos dentro de un campo MySQL, por ejemplo, para la optimización de velocidad o cuando tiene propiedades desconocidas o dinámicas que aún desea guardar en un DB.

Sin embargo, como regla general, si confías en almacenar objetos y arreglos en MySQL, entonces es muy probable que el diseño de tu base de datos esté roto.