database-design - tabla - tipos de restricciones en base de datos

Restricción de clave externa frente a comprobación de integridad

Question

Estoy construyendo un sistema que es un repositorio central para almacenar datos de varios otros sistemas. Se requiere un proceso de sincronización para actualizar el repositorio central cuando se actualizan los otros datos del sistema. Habrá una tabla sync_action para identificar con qué sistema se necesita sincronizar el repositorio central y el tipo de sincronización requerida. Hay un conjunto de acciones definidas que es muy poco probable que cambie. Un sistema adelgazado está abajo.

Según lo veo, puedo abordar esto de dos maneras:

Opción 1 ) Tener una tabla de Action que tenga las 3 acciones disponibles. Tener una tabla sync_action que usa una clave externa para hacer referencia a las acciones requeridas.

Tabla: Sistema

ID Description
 1 Slave System 1
 2 Slave System 2

Tabla: Acción

ID  Description
 1  Insert
 2  Update
 3  Delete

Tabla: Sync_action

ID  Action  System
 1     1       1
 2     2       1

Opción 2 ) En lugar de una clave externa, use una restricción de verificación en la columna sync_action.action para que solo se puedan insertar las acciones Insert/Update/Delete .

Tabla: Sync_action

ID  Action  System
1   Insert    1
2   Update    1

Me gustaría saber qué factores entran en la determinación de cuál es un mejor enfoque al decidir entre restricciones de integridad, restricción de clave externa frente a verificación. Hubo hilos similares pero no los encontré lo suficientemente definitivos. Esto puede deberse a que depende de la interpretación, pero cualquier pensamiento sería apreciado.

Aclamaciones

Answer 1

Creo que estás confundiendo la diferencia entre una restricción de clave externa y una restricción de verificación .

Una restricción de clave externa está ahí para imponer la integridad referencial y una restricción de verificación restringe a una columna para que contenga solo datos válidos. En tu caso, esto puede parecer una diferencia menor, pero si la abstraemos un poco, espero que quede más clara.

Si consideramos una tabla, los users con las columnas user_id, user_name, address_id, join_date, active, last_active_month ; Reconozco que esta no es necesariamente la mejor manera de hacer las cosas, pero servirá para el punto que estoy tratando de hacer.

En este caso, es evidentemente ridículo tener address_id como una restricción. Esta columna podría tener cualquier cantidad de valores. Sin embargo, active , suponiendo que deseemos un booleano y/n , solo puede tener dos valores posibles y last_active_month solo puede tener 12 valores posibles. En ambos casos, es completamente ridículo tener una clave externa. Solo hay una cierta cantidad de valores y, según la definición de los datos que incluye, estos valores no pueden cambiar.

En su caso, si bien podría aplicar una restricción de verificación , a menos que pueda estar absolutamente seguro de que el número de actions nunca cambiará, una clave externa es la correcta.

En un asunto ligeramente separado, y como mencionó @pst, veo que te ha comido el monstruo clave sustituto. Si bien esto puede dar como resultado mejoras en el rendimiento, en una tabla del tamaño que está visualizando (3 valores, insert / update / delete ) o incluso uno más grande, todo lo que sirve para hacer es oscurecer lo que está tratando de lograr.

No es fácil mirar

ID  Action  System
 1     1       1
 2     2       1 

y ver qué está pasando, pero:

ID  Action  System
 1  insert     1
 2  update     1

es mucho más fácil de leer; También es posible que desee considerar hacer lo mismo para la columna del system : probablemente lo haría, aunque el número de valores posibles salta ligeramente en esto. Solo mis pensamientos personales sobre el asunto ...

Answer 2

Los comentaristas parecen estar de acuerdo unánimemente:

En general, es mejor tener una restricción FOREIGN KEY para una tabla de referencia (más o menos estática). Razones:

• La restricción es fácilmente "extensible". Para agregar o eliminar una opción, solo tiene que agregar o eliminar una fila de la tabla de referencia. No tiene que soltar la restricción y volver a crearla. Aún más, si tiene la misma restricción en columnas similares en otras tablas, también.

• Puede tener información adicional adjunta (más columnas), que las aplicaciones pueden leer si es necesario.

• Los ORM pueden manejar mejor (Leer: conocer) estas restricciones. Solo tienen que leer una tabla, no los metadatos.

• Si desea cambiar los códigos de Acción, los efectos de cascada se encargarán de los cambios en otras tablas (posiblemente muchas). No es necesario escribir consultas de ACTUALIZACIÓN.

• Un DBMS particular todavía no ha implementado restricciones CHECK (vergüenza), aunque sí tiene FK.

Como se menciona @pst (y prefiero mucho este enfoque), puede usar un código sensible en lugar de una ID de número entero sustituto. Entonces, tu tabla podría ser:

Tabla: Sistema

SystemID Description
 1        Slave System 1
 2        Slave System 2

Tabla: Acción

ActionCode Description
 I          Insert
 U          Update
 D          Delete

Tabla: SyncAction

ID  ActionCode  SystemID
 1     I          1
 2     U          1