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

guardar varios datos en un campo mysql

Question

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 a él, mientras que el estudiante B puede tener 12 asignaturas adjuntas. .

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

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

Answer 1

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

El almacenamiento de datos en matrices puede parecerle natural en papel, pero para el motor db el rendimiento puede ser mayormente sin uso de índice. Además, encontrará en el día 2 que acceder y mantener sus datos será una pesadilla.

Lo siguiente debería ayudarlo a comenzar bien mientras juega. Joins 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 (a.k.a 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 hay en qué departamento?

mostrar todo, usa alias de tabla (abreviaturas) para hacer que escribir sea menos, 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 World of Chaucer este término?

(conociendo el courseId = 5)

Los siguientes se benefician 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á abrumado, así que deja caer la clase de matemáticas

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

ejecute la declaración de selección anterior que muestra lo que Kim está tomando:

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

Ah, término mucho más fácil. Sin embargo, papá no será feliz.

Tenga en cuenta cosas como SCJunction.term. Mucho se puede escribir sobre eso, lo pasaré por alto en este momento principalmente, 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 un int.

Y hasta donde llega la identificación. Esta es la forma en que lo haría. Es preferencia personal. Requeriría conocer los números de identificación, buscarlos. Otros podrían optar por tener un curso ID algo así como HIST101 y no 17. Esos son mucho más legibles (pero más lentos en el índice (apenas)). Así que haz lo que sea mejor para ti.

Nota índice compuesto

Un índice compuesto (ÍNDICE significa CLAVE, y viceversa) es uno que combina múltiples columnas para una recuperación rápida de datos. Los pedidos se voltean para los dos compuestos en la tabla SCJunction de modo que, dependiendo del universo de consultas que van después de sus datos, el motor de base de datos puede elegir qué índice usar para la recuperación más rápida en función de la columna más a la izquierda que está buscando .

En cuanto a la clave única, # 1, el comentario al lado indicando que no se aplican duplicados (es decir, datos basura) se explica por sí mismo. Por ejemplo, el estudiante 1 curso 1 término 1 no puede existir dos veces en esa tabla.

Un concepto crucial para entender es el concepto de ordenamiento left-most de los nombres de columna en un índice.

Para las consultas que van solo después de studentId , se studentId la clave que tiene studentId primero (más a la left-most ). En las consultas que van solo después de courseId , entonces se usa la clave que tiene courseId izquierda. En las consultas que van después de studentId y courseId, el motor db puede decidir qué clave compuesta usar.

Cuando digo "ir tras", quiero decir en la on clause o where clause condición de la where clause .

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

Entonces, esos dos índices combinan los siguientes 2 conceptos

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

La comida para llevar

La conclusión importante es que las tablas de unión permiten una recuperación rápida del índice y una gestión sensata de los datos frente a los datos delimitados por comas (mentalidad de matriz) amontonados en una columna, y toda la miseria de usar tal construcción.

Answer 2

Para completar, no en el caso de que esta sea una solución general recomendada:

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

De esta manera, puede almacenar objetos completos y matrices en un campo, ya que una matriz se vería así:

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

Especialmente los principiantes no lo saben, y reinventan la rueda con otra implementación de cadena separada por comas o utilizando enfoques de serialización / deserialización dependientes del lenguaje.

Al menos JSON se usa con mucha frecuencia y se analiza fácilmente como formato de intercambio de datos.

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

Sin embargo, como regla general, si confía en almacenar objetos y matrices en MySQL, lo más probable es que el diseño de su base de datos esté roto.