mysql - soporta - Mejora el diseño de la base de

optimizar consultas mysql explain (5)

No, no hay mejor diseño, porque el diseño no tiene nada que ver con cuántos registros habrá en las tablas. Elegirá el mismo diseño, sin importar si trata con diez estudiantes o diez mil.

El diseño de tu mesa se ve bien. No te preocupes por la cantidad de registros. A dbms está hecho para tratar con tablas grandes. Y 100k records sigue siendo una pequeña base de datos. Ni siquiera cambiaría este diseño si hubiera miles de millones de respuestas para almacenar.

Estoy trabajando en un proyecto de prueba en línea de opción múltiple aquí he diseñado la base de datos para almacenar el resultado pero buscando una forma más optimizada.

Requisitos:

Cada pregunta tiene cuatro opciones.
Solo se puede seleccionar una opción y debe almacenarse en la base de datos.

Mi diseño:

mesas:

estudiantes
stud_id, nombre, correo electrónico

pruebas
test_id, testname, duration

preguntas
que_id, question, opt1, opt2, opt3, opt4, answer, test_id

respuestas
stud_id, que_id, respuesta

De esta forma, las respuestas pueden almacenarse pero aumenta el número de registros, ya que para cada pregunta resuelta por el alumno, se agregará un nuevo registro en la tabla de respuestas.

Por ejemplo, una prueba consiste en 100 preguntas y 1000 estudiantes toman esa prueba, para cada estudiante habrá 100 registros para cada pregunta y para 1000 estudiantes 100k registros.

¿Hay alguna manera mejor de hacer esto donde el número de registros será menor?

Puede almacenar los detalles de la respuesta como un ~ registro separado para el ID de pregunta correspondiente que también está ~ separado. De esta forma, para una identificación de estudiante solo habrá un registro. También puede decodificar la ans para un ID de pregunta en particular

Si quiere normalizar los datos, crearía las tablas de forma un poco diferente.

Tu mesa de Estudiante se ve bien. En general, utilizo un nombre singular para tablas, en lugar de plural.

Student ------- Student ID Name Email ...

Aquí está la tabla de prueba:

Test ---- Test ID Test Name ...

Atamos a los estudiantes a las pruebas con una tabla de unión.

StudentTest ----------- Student ID Test ID Test Started Timestamp Test Duration ...

El tiempo y la duración de la prueba varían de un estudiante a otro, por lo que esas columnas se incluyen en la tabla StudentTest.

La tabla de preguntas.

Question -------- Question ID Question Text

Y la tabla de respuestas.

Answer ------ Answer ID Answer Text

Ahora aquí es donde las cosas se ponen difíciles. Puede asignar preguntas a una prueba basada en la ID, como esta.

TestQuestion ------------ Test ID Question ID

Pero si lo hace y alguien cambia el texto de la pregunta después de la prueba, entonces la ID de la pregunta apunta a una pregunta diferente a la pregunta sobre la prueba.

Para resolver este problema, creamos tablas de historial como esta:

QuestionHistory --------------- QuestionHistory ID Question Text AnswerHistory ------------- AnswerHistory ID Answer Text

Entonces, creamos la tabla TestQuestion de la siguiente manera:

TestQuestion ------------ Test ID QuestionHistory ID

Y copie las preguntas, así como las respuestas a las tablas de historial.

Por razones similares, creamos la tabla Question Answers de la siguiente manera:

QuestionAnswer -------------- QuestionHistory ID AnswerHistory ID Is Correct Answer

Su código podría asegurarse de que cada pregunta tenga 4 respuestas posibles. La base de datos permite más o menos de 4 respuestas posibles.

Finalmente, vinculamos las respuestas del alumno a las preguntas de la prueba.

StudentQuestionAnswer --------------------- Student ID Test ID QuestionHistory ID AnswerHistory ID Is Correct Answer

Sí, la columna ID de prueba está duplicada aquí. Esto es para que pueda hacer una consulta por la prueba, así como también el estudiante que tomó la prueba.

El campo Respuesta correcta tiene un significado diferente en la tabla Respuesta de pregunta y en la tabla Pregunta de estudiante. En la tabla de respuesta de la pregunta, la respuesta booleana de respuesta correcta apunta a la respuesta correcta. En la tabla StudentQuestionAnswer, la respuesta iscorrect boolean significa que el alumno respondió la pregunta correctamente.

Esta debe ser una base de datos completa de preguntas y respuestas. Podría vincular las pruebas a los cursos si lo desea.

Mira en este enlace . Esto podría ayudarlo a diseñar una solución similar suya.

Respuesta inicial

Comprender los datos

Has hecho un buen trabajo. En lo que respecta a los datos, el diseño es correcto, pero incompleto. Hay dos errores:

opt1…opt4 es un grupo repetitivo, que rompe 2NF. Debe ser colocado en una mesa separada.
- Además, parece que no hay un nombre o descriptor de opción, lo cual es extraño (¿qué pinta en la página, al lado de cada botón de radio?)
- Si alguna vez agregas una quinta opción, eso ahora es atendido; si tiene preguntas con menos de cuatro opciones, eso ahora se atiende.
- Por el contrario, tiene un conjunto fijo de columnas, y si hay tales cambios en el futuro, debe cambiar tanto la base de datos como el código existente. Y el código será horrendo (procesamiento adicional en lugar de SELECTs directos)
Su tabla de answers no tiene integridad. Tal como está, las respuestas se pueden registrar contra una pregunta que no se le pidió al estudiante, o para una prueba de que el estudiante no se sentó. La prevención de ese tipo de error es una tarifa ordinaria en una Base de Datos Relacional, y no es posible en un Sistema de Archivo de Registro.
- En estos días oscuros de TI, esta es una tendencia común. Las personas se centran en los valores de los datos; ellos imaginan los valores en forma de hoja de cálculo, y van directamente al objeto de implementación que contiene esos valores . En lugar de entender los datos y lo que significa.
- answers(stud_id, que_id, answer) no tienen significado ni integridad, a menos que se afirme el contexto de un student_test.
El tercer elemento no es un error, porque no se lo dio como requisito. Sin embargo, me parece que una pregunta puede usarse en más de una prueba. La forma en que lo haya configurado, tales preguntas se duplicarán (el objetivo de una base de datos es Normalizarlo, de modo que no haya duplicación).
- Por supuesto, la consecuencia es una tabla test_question. , test_question.

Preguntas

De esta forma se pueden almacenar las respuestas, pero aumenta el número de registros, ya que para cada pregunta resuelta por el alumno se agregará un nuevo registro en la tabla de respuestas.

Sí. Eso es normal para una base de datos.

¿Hay alguna manera mejor de hacer esto donde el número de registros será menor?

Para un sistema de archivo de registro, sí. Para una base de datos, no. Como ha etiquetado su pregunta como diseño de base de datos, supondré que eso es lo que desea.

Una base de datos es una colección de hechos, no de registros con campos relacionados. Los hechos son sobre el mundo real, limitados al alcance de la base de datos y la aplicación.

Es importante determinar los hechos discretos que necesitamos, porque los hechos subordinados dependen de hechos de orden superior. Eso es diseño de base de datos. Y normalizamos los datos, a medida que avanzamos, como parte de un solo y mismo ejercicio. La normalización tiene el propósito de eliminar la duplicación, de lo contrario, tiene Anomalías de actualización. Y determinamos las Claves Relacionales, a medida que progresamos, nuevamente como parte de un solo y mismo ejercicio. Las claves relacionales proporcionan la estructura lógica de una base de datos relacional, es decir. la integridad lógica

Por ejemplo, una prueba consiste en 100 preguntas y 1000 estudiantes toman esa prueba, para cada estudiante habrá 100 registros para cada pregunta y para 1000 estudiantes 100k registros.

Sí. Pero eso se expresa en términos de procesamiento de registros ISAM. En términos de la base de datos, no se puede evitar el hecho de que la base de datos almacena:

hechos sobre 100 preguntas
hechos sobre 1,000 estudiantes
datos sobre 1.000 estudiantes por las 100 elecciones que hicieron

Debe entender dos cosas: la gran cantidad de hechos discretos; y el uso de llaves compuestas. Ambos son esenciales para las bases de datos relacionales. Si alguno de ellos falta, o si los implementa con renuencia, no tendrá la integridad, potencia o velocidad de una base de datos relacional, tendrá un Sistema de registro de registros ISAM anterior a los años 1970.

Además, las plataformas SQL, y hasta cierto punto las plataformas NONsql como myNONsql, están muy optimizadas para procesar conjuntos de datos (no registro por registro); E / S pesadas y almacenamiento en caché; etc. Si implementa las estructuras necesarias para una alta concurrencia, obtendrá aún más rendimiento.

Implementación

En lo que se refiere a la implementación, y particularmente dado que le preocupa el rendimiento, hay errores. Una reformulación sería, la implementación no debería intentarse hasta que los datos sean comprendidos y modelados correctamente.

El problema general es que ha agregado un sustituto (no existe la "clave sustituta", es simplemente un sustituto, una identificación de registro físico). Es muy temprano en el ejercicio de modelado; no ha progresado lo suficiente; el modelo no es estable, para agregar sustitutos.

Los sustitutos son siempre una columna adicional más el índice subyacente. Obviamente, eso consume recursos y tiene un costo en inserciones y eliminaciones.
Los sustitutos no proporcionan exclusividad de fila , lo que se exige en una base de datos relacional.
El modelo relacional exige que las claves estén compuestas por los datos. Las claves relacionales proporcionan una singularidad de fila.
Un suplente no se compone de los datos. Por lo tanto, no es una Clave Relacional, y no proporciona ninguna de las cualidades de uno.
Si se utiliza un sustituto, no reemplaza la clave, es además de la clave. Por eso evaluamos la necesidad de sustitutos después, no antes, de modelar los datos. Es un problema de implementación, no de modelado.

Solución

En lugar de ir y venir, permítame brindarle la propuesta, y puede analizarla.

Modelo de Datos de Prueba del Estudiante (Página 1 solamente, para aquellos que siguen la progresión).
Si no está acostumbrado a la notación, tenga en cuenta que cada pequeño tic, muesca y marca, las líneas sólidas o discontinuas, las esquinas cuadradas y las redondas significa algo muy específico. Consulte la notación IDEF1X .
Para test y question. He dejado columnas de id , pero tenga en cuenta que estará mucho mejor con códigos cortos y significativos.
student_id es válido porque tanto el name como el email son demasiado grandes para migrar a las tablas secundarias.
Por favor revisa las Frases verbales cuidadosamente, comprenden un conjunto de Predicados. El resto de los Predicates se puede determinar directamente del modelo. Si esto no está claro, por favor pregunte.
Vea si puede determinar que se trata de una colección de hechos, y cada hecho es discreto precisamente porque otros hechos dependen de él; que no es una colección de registros con campos relacionados.

Su tabla de answers no tiene integridad. Tal como está, las respuestas se pueden registrar contra una pregunta que no se le pidió al estudiante, o para una prueba de que el estudiante no se sentó. La prevención de ese tipo de error es una tarifa ordinaria en una Base de Datos Relacional, y no es posible en un Sistema de Archivo de Registro.

Eso ahora está prevenido. La tabla de answers , ahora denominada student_response, ahora tiene cierta integridad. Un student está registrado para una prueba en student_test, y las student_responses están restringidas a student_test.

Por favor comente / discuta.

Respuesta a los comentarios

Agregaré un tema adicional de la tabla (subject_id, subject_name) y agregaré subject_id en la tabla de preguntas ya que FK está bien?

Sí, por supuesto. Pero eso tiene consecuencias. Algunos consejos para asegurarnos de hacerlo bien, en general:

Como se explicó, no use sustitutos (ID de registro) a menos que sea absolutamente necesario. Los códigos cortos son mucho mejores para los identificadores , tanto para los usuarios como para los desarrolladores.
- Si desea obtener más información sobre los problemas relacionados con las columnas de ID , lea esta respuesta .
El sujeto es importante. Es el contexto en el que (a) existe una question , y (b) existe una test . Existían como elementos independientes (página 1 del DM), pero ahora están subordinados al subject. La adición mejora sustancialmente la integridad de los datos.
El hecho de la matrícula de un estudiante y el hecho de que un estudiante se presente a una prueba son hechos discretos y separados.
Afortunadamente, eso eliminó dos sustitutos question_id y test_id. Los codes cortos como CHAR(2) son más fáciles y más significativos.
Tenga en cuenta la mejora en los nombres de la tabla, una mayor claridad.
He actualizado el modelo de datos de prueba del alumno (página 2 solamente, para aquellos que siguen la progresión).
Sin embargo, eso expone algo (es por eso que modelamos datos, el papel es barato, muchos borradores son normales). Si evaluamos los Predicados (fácilmente visibles en el Modelo de Datos, como se detalla en el documento de Notación IDEF1X ):
each subject_test was taken by 0-to-n student_tests each student_test is [a taking of] 1 subject_test each student took 0-to-n student_tests each student_test is taken by 1 student
esos Predicados no son precisos. Un student puede presentarse para una test en cualquier subject. Dada la nueva tabla de subject , creo que queremos que los students se matriculen para las subjects, y subjects, por student_test tanto, student_test vea limitado a subjects para las cuales el student está registrado.
- Si desea información sobre el importante concepto relacional de Predicados, y cómo se usa para comprender y verificar el modelo, visite esta respuesta , desplácese hacia abajo hasta encontrar la sección Predicado y léalo con atención.
He actualizado el Modelo de Datos de Prueba del Estudiante (Página 3). Ahora tenemos aún más integridad, de modo que student_test está restringido a subjects para las cuales el student está registrado. Los Predicados relevantes son:
each student registered for 0-to-n student_subjects each student_subject is a registration of 1 student each subject attracted 0-to-n student_subjects each student_subject is an attraction of 1 subject each subject_test was taken by 0-to-n student_tests each student_test is [a taking of] 1 subject_test each student_subject took 0-to-n student_tests each student_test is taken by 1 student_subjects
Ahora el modelo de datos parece estar completo.
- El contexto es todo en una base de datos.
- Las jerarquías de datos son claramente visibles en la composición de las claves.
- Tenga en cuenta que son las Claves relacionales, en las tablas secundarias, las que proporcionan Integridad relacional con las tablas padre, a cada nivel superior (padre, abuelo) en la jerarquía.
- En caso de que no sea obvio, observe el poder de las uniones relacionales. Algo que no puede hacer con los sistemas de archivos de registro que tienen campos de ID en cada archivo. P.ej:
  - Únase student_response directamente al subject en subject_code , sin tener que navegar entre los dos niveles intermedios
  - Únase student_response directamente al student en student_id , sin tener que navegar los dos niveles intermedios