Digamos que tengo el siguiente diagrama ER:
Ahora, si representé la relación usando una clave externa de School en Student, podría tener NULL valores (porque un Student no es necesario para pertenecer a un School), por ejemplo:
Entonces, la forma correcta (según lo que he leído) es crear una tabla de intersección para representar la relación, por ejemplo:
De esta manera, no NULL los valores pueden estar presentes en la tabla School_has_Student.
Pero, ¿cuáles son los desventajas de usar una clave externa que acepta valores NULL en lugar de crear una tabla de intersección?
Editar:
Elegí por error (school_id, student_id) como clave principal para School_has_Student tabla, que hizo que la relación fuera de varios a varios. La clave principal correcta debería haber sido student_id:
Comentarios
- No hay ‘ s no » forma » correcta. Existe ‘ la mejor forma para sus necesidades.
- Estoy de acuerdo con Doc sobre la premisa falsa, pero tal vez ‘ ¿sigue siendo lo suficientemente claro para responder?
- Hay una premisa falsa, pero es bastante fácil de aclarar y explicar la diferencia.
- Me retracté de mi voto cercano , pero la oración » Entonces, la forma correcta (según lo que he leído) es crear una tabla de intersección para representar la relación » me da la impresión de que debería decirnos qué fuente extraña le dijo que esta es la » correcta «. En todos los libros de texto que he leído antes, la forma canónica para las relaciones 1: n es una única clave externa. ¿O no entendiste algo?
- @Doc Brown No ‘ no recuerdo dónde lo he leído, pero estoy seguro de que dice que una tabla de intersección fue de la manera correcta. De todos modos, ¿puede darme el nombre de un libro que diga que una relación 1: n (con participación opcional en el lado: 1) debe representarse usando una única clave externa, estoy interesado en leer lo que dicen sobre este tema.
Respuesta
Los dos modelos representan relaciones diferentes.
Al usar una tabla de combinación , está modelando una relación de muchos a muchos.
Al usar una clave externa simple, está modelando una relación de uno a muchos.
La desventaja de una clave externa que acepta valores NULL La clave es no poder modelar la relación como muchos a muchos, si eso es lo que está tratando de lograr.
Según su edición de la pregunta, está dividiendo efectivamente la tabla de estudiantes en dos tablas con la misma clave. Por lo general, veo esto en tablas que tienen demasiados campos, por lo que alguien los divide en dos para que sea más manejable (lo llamo poner lápiz labial en un cerdo).
Al dividir la tabla de estudiantes, está haciendo la segunda tabla es opcional porque no es necesario que exista un registro en la segunda tabla. Lo cual es muy similar a un campo que no necesita establecerse porque puede ser nulo.
Si desea una relación de uno a muchos, es mucho mejor usar una sola tabla y permitir la ID de la escuela ser nulo en la tabla de estudiantes. No hay ninguna razón para evitar valores nulos en los campos, incluso para una clave externa. Eso significa que la relación externa es opcional: los desarrolladores y los administradores de bases de datos lo entienden claramente, y el motor de base de datos subyacente ciertamente debería funcionar bien.
Si le preocupan las uniones, no se preocupe. Hay una semántica bien definida sobre cómo funcionan las combinaciones con campos nulos. Al usar una sola tabla, puede unir dos tablas en lugar de tres.
Comentarios
- Entonces, si estoy modelando una relación de uno a muchos (con participación opcional en el lado: 1), debería usar una clave externa a pesar de que puede tener
NULLvalores. - @Tom sí, eso es exactamente cómo modelarlo. Si bien es técnicamente posible usar una tabla de combinación, el modelo de datos permite muchos a muchos, por lo que necesitará desencadenadores y lógica de base de datos para evitarlo. Es mejor restringir la relación de tal manera que sea imposible agregar datos incorrectos.
- Edité mi pregunta.Solo hice
student_iduna clave principal en la tablaSchool_has_Student, que mantuvo la relación como uno a varios. ¿Qué inconvenientes tiene este método con respecto al uso de una clave externa? - @Tom Edité mi respuesta.
Respuesta
Escribiste en un comentario anterior:
el libro «Fundamentos de los sistemas de bases de datos» […] dice [.. .] que se recomienda utilizar una tabla de intersección si hay muchos valores NULL en la columna de clave externa (por ejemplo: si el 98% de los empleados no administran un departamento)
Cuando hay muchos valores NULL en la columna de clave externa, sus programas tendrán que lidiar con esta columna casi vacía para todos y cada uno de los registros que procesan. La columna probablemente ocupará algo de espacio en el disco aunque en el 98% de todos los casos está vacío, consultar la relación significa consultar esa columna que le brinda más tráfico de red, y si está utilizando un ORM que genera sus clases a partir de sus tablas, sus programas también necesitarán más espacio en el cliente lado de lo necesario. Usando un inters La tabla de acciones evita esto, solo habrá registros de enlace necesarios donde la clave externa equivalente no sería NULL de lo contrario.
Opuesto a eso, si no tiene solo unos pocos valores NULL, digamos 50% o más las relaciones no son NULAS, usar una tabla de intersección le da el efecto opuesto: más espacio en el disco, mayor complejidad, lo que resulta en más tráfico de red, etc.
Por lo tanto, usar una tabla de intersección es solo una forma de optimización, solo sensible para un caso específico, y especialmente hoy en día, donde el espacio en disco y la memoria se abarataron, y se necesitaban con mucha menos frecuencia. Tenga en cuenta que «Fundamentals of Database Systems» se escribió originalmente hace más de 20 años (encontré una referencia a la segunda edición de 1994), y supongo que esa recomendación ya estaba allí en ese momento. Antes de 1994, la optimización del espacio era probablemente mucho más importante que en la actualidad, ya que el almacenamiento masivo era aún más caro y las computadoras y las redes eran mucho más lentas que en la actualidad.
Como nota al margen de un comentario exigente: el La declaración anterior sólo trata de anticipar lo que el autor de «Fundamentals of Database Systems» tenía en mente con su recomendación. Supongo que estaba haciendo una declaración general aproximada, válida para la mayoría de los sistemas. En algunas bases de datos hay otras optimizaciones posibles como «columnas dispersas» que hacen que el uso de una tabla de intersección sea aún más obsoleto.
Así que no malinterpretes esa recomendación. El libro no dice prefiere tablas de intersección para {0,1}:n relaciones en general, o, como escribió, que esta es la «forma correcta». Utilice optimizaciones como esta que harán que sus programas sean más complicados solo cuando realmente los necesita.
Comentarios
- Usted ‘ está asumiendo mucho acerca de la implementación del base de datos, especialmente considerando que el OP no ‘ t mencionó uno específico. Es ‘ s más que probable que la base de datos sea lo suficientemente inteligente para usar solo una pequeña cantidad de espacio para columnas dispersas.
- @gardenhead: ¿qué te hace creer que esto es » más que probable «?
- El hecho de que las bases de datos Han existido durante décadas y están altamente optimizados, ya que son un componente crítico de la mayoría de las infraestructuras.
- @gardenhead: me parece que estás haciendo suposiciones injustificadas mucho más pesadas que yo. Sin embargo, vea mi edición.
Respuesta
El modelo conceptual se verá así, que es muy poco ortodoxo para decir menos:
El modelo físico se verá así, que es confuso para decir menos (la gente pensará que es» M: M a menos que vean de cerca):
Mi sugerencia:
Si tiene muchas columnas similares (FK o de otro tipo), que no se aplican a la mayoría de los estudiantes, separe las tablas en tablas de roles con rels 1: 1. Pero eso no es porque sean FK, es porque las columnas no se aplican a la mayoría de las filas.
De lo contrario, , Los FK que aceptan valores NULL son una parte normal de una base de datos y las tablas de unión suelen ser para rels M: M.
Los usos comunes de los rels 1: 1 son para tablas de roles que tienen columnas que se aplican solo si la entidad es de un cierto tipo y extraen columnas BLOB por consideraciones de rendimiento o almacenamiento. Evitar valores nulos en FK no es un uso común para eso.
Respuesta
Además de otras respuestas, me gustaría señalar que un valor nulo para la clave externa es ambiguo. ¿Significa:
1) La escuela del estudiante (si la hay) es desconocida (este es el significado estándar de «nulo» – el valor es desconocido)
2) Es sabe si el estudiante tiene una escuela o no, y ellos no la tienen
Si usa el significado estándar de nulo, ¿cómo representaría «el estudiante no tiene escuela» en su modelo de clave externa. En ese caso, probablemente tendrías que crear una entrada «sin escuela», con su propia identificación en la tabla de la escuela. (No es ideal)
Comentarios
- El libro » Fundamentals of Database Systems » menciona que hay 3 interpretaciones para
NULL, puede significar: 1) Valor desconocido. 2) Valor no disponible o retenido. 3) Atributo no aplicable (creo que esta interpretación significa que puede especificar unNULLpara una clave externa). - Esa ‘ es una lista útil, pero la semántica de nulo (o cualquier valor realmente) es definible por el usuario.puede significar lo que el diseñador diga que significa, no limitado a esa lista. El problema es cómo distinguir diferentes significados cuando se puede requerir más de uno (o incluso guardar involuntariamente)
- Entonces, ¿estás sugiriendo que debería crear una tabla de intersección en lugar de usar una clave externa que acepta valores NULL?
- @Tom Sí, creo que es mejor en este caso
- @BradThomas – para evitar la misma ambigüedad al usar una tabla de intersección, ¿representaría el caso 2 (se sabe que el estudiante no school) por un registro en la tabla de intersección con un School_ID NULL?
Respuesta
Las tablas de la base de datos tienen esto algo agradable llamado restricciones. Así que es muy fácil hacer una tabla de intersección que permite que solo 1 de cada estudiante aparezca en la tabla, pero muchas escuelas en esa tabla. Efectivamente, darle una
Teoría es bueno, pero al final vamos a modelar su base de datos después de las preguntas que está haciendo.
Si desea hacer preguntas frecuentes con la pregunta: «qué estudiantes están en mi escuela», ¿realmente desea consultar toda la tabla de estudiantes o tiene una tabla de intersección fácil.
En bases de datos: optimice para las preguntas que haga.
Respuesta
Allí es un caso de uso en el que utilizar una tercera tabla puede tener sentido. El ejemplo puede parecer puramente hipotético, pero espero que ilustre bien mi punto. Supongamos que agrega más columnas a la tabla students y, en algún momento, decide aplicar la unicidad en los registros mediante un índice compuesto en varias columnas. Es muy probable que «Tendremos que incluir la columna school_id también, y aquí las cosas comienzan a complicarse. Debido a la forma en que se diseñó SQL, se insertaron varios registros idénticos donde school_id es NULL será posible. Tiene perfecto sentido desde una perspectiva técnica, pero es contradictorio y puede conducir a resultados inesperados. Por otro lado, imponer la singularidad La tabla de intersección es fácil.
Recientemente tuve que modelar una relación «opcional» de este tipo, donde el requisito de una restricción de unicidad se debía a una columna de marca de tiempo. Dejar la clave externa que acepta valores NULL en la tabla conduce repentinamente a la posibilidad de insertar registros con la misma marca de tiempo (supongamos que es una predeterminada, establecida en registros que no han sido auditados / aprox. oved todavía), y la única salida era eliminar la columna que aceptaba valores NULL.
Así que, como puede ver, es un caso bastante específico y, como otros señalaron, la mayoría de las veces estaría perfectamente bien con todos los valores NULL. Realmente depende de los requisitos específicos de su modelo.
Responder
Además de las muchas buenas sugerencias ya enviadas, personalmente «No soy un fanático de las claves externas a menos que sean realmente necesarias. Primero, está la relación M: M a la que hace referencia. Además, llamar a una clave externa y, por lo tanto, extraer los datos de la tabla en sus consultas, introduce más complejidad y depende de tamaño de tabla, rendimiento más lento. Como han dicho otros, los campos FK que aceptan valores NULL pueden no ser compatibles y pueden crear problemas de integridad de datos.
Si está definiendo un estado donde la escuela del estudiante es desconocida o está vacía, el NULL no diferenciará esas condiciones (de nuevo volvemos a la integridad de los datos). La sugerencia de tabla de roles de Tulains es elegante y permite valores nulos de forma limpia.