sql-server - una - restriccion check sql server

Clave externa que hace referencia a tablas múltiples

Question

Tengo una columna con un identificador único que potencialmente puede hacer referencia a una de las cuatro tablas diferentes. He visto esto hecho de dos maneras, pero ambas parecen una mala práctica.

Primero, he visto una sola columna ObjectID sin declararla explícitamente como una clave externa a una tabla específica. Entonces puedes empujar cualquier identificador único que quieras en él. Esto significa que podría potencialmente insertar ID de tablas que no son parte de las 4 tablas que yo quería.

En segundo lugar, debido a que los datos pueden provenir de cuatro tablas diferentes, también he visto a personas crear 4 claves externas diferentes. Y al hacerlo, el sistema se basa en UNA Y SOLO UNA columna que tiene un valor no nulo.

¿Cuál es un mejor enfoque para hacer esto? Por ejemplo, los registros en mi tabla podrían referirse a Hospitales (ID), Clínicas (ID), Escuelas (ID) o Universidades (ID) ... pero SÓLO a esas tablas.

¡Gracias!

Answer 1

Podría poner un disparador sobre la mesa y aplicar allí la integridad referencial. No creo que haya una característica realmente buena lista para usar para implementar este requisito.

Answer 2

Comencemos en el nivel conceptual. Si pensamos en Hospitales, Clínicas, Escuelas y Universidades como clases de entidades temáticas, ¿existe una superclase que las generalice a todas? Probablemente haya. No voy a tratar de decirte de qué se trata, porque no entiendo tu tema tan bien como tú. Pero voy a proceder como si pudiéramos llamar a todas ellas "Instituciones", y tratar a cada una de las cuatro como subclases de Instituciones.

Como otros encuestados han notado, la extensión de clase / subclase y la herencia no están incorporadas en la mayoría de los sistemas de bases de datos relacionales. Pero hay mucha ayuda, si conoce las palabras de moda correctas. Lo que sigue intenta enseñarle las palabras de moda, en la jerga de la base de datos. Aquí está un resumen de las palabras de moda que vienen: "Generalización de ER", "Especialización de ER", "Herencia de tabla única", "Herencia de tabla de clase", "Clave primaria compartida".

Manteniéndose en el nivel conceptual, el modelado ER es una buena forma de entender los datos a nivel conceptual. En el modelado de ER, existe un concepto, "Generalización de ER", y un concepto de contraparte "Especialización de ER" que se asemeja al proceso de pensamiento que acabo de presentar como "superclase / subclase". La especialización ER le dice cómo diagramar subclases, pero no le dice cómo implementarlas.

Luego bajamos del nivel conceptual al nivel lógico. Expresamos los datos en términos de relaciones o, si se quiere, tablas SQL. Hay un par de técnicas para implementar subclases. Uno se llama "herencia de tabla única". El otro se llama "herencia de tabla de clase". En relación con la herencia de la tabla de clase, hay otra técnica que se conoce con el nombre "Clave primaria compartida".

Avanzando en su caso con la herencia de tablas de clase, primero diseñamos una tabla llamada "Instituciones", con un campo Id, un campo de nombre y todos los campos que pertenecen a las instituciones, sin importar cuál de los cuatro tipos son. Cosas como los campos de direcciones postales, por ejemplo. De nuevo, usted entiende sus datos mejor que yo, y puede encontrar los campos que están en las cuatro tablas existentes. Completamos el campo id de la manera habitual.

A continuación diseñamos cuatro tablas llamadas "Hospitales", "Clínicas", "Escuelas" y "Universidades". Estos contendrán un campo de identificación, más todos los campos de datos que pertenecen solo a ese tipo de institución. Por ejemplo, un hospital puede tener una "capacidad de cama". De nuevo, comprende mejor sus datos que yo, y puede deducirlos de los campos en sus tablas existentes que no llegaron a la tabla de Instituciones.

Aquí es donde entra en juego la "clave primaria compartida". Cuando se crea una nueva entrada en "Instituciones", tenemos que hacer una nueva entrada paralela en una de las cuatro tablas de subclases especializadas. Pero no utilizamos algún tipo de característica de autonumeración para rellenar el campo de identificación. En su lugar, colocamos una copia del campo de identificación de la tabla "Instituciones" en el campo de identificación de la tabla de subclase.

Esto es un poco de trabajo, pero los beneficios bien valen la pena. La clave primaria compartida impone la naturaleza uno a uno de la relación entre las entradas de subclase y las entradas de superclase. Hace que la unión de datos de superclase y datos de subclase sea simple, fácil y rápida. Elimina la necesidad de un campo especial para decirle a qué subclase pertenece una institución determinada.

Y, en su caso, proporciona una respuesta útil a su pregunta original. La clave externa sobre la que originalmente preguntaba ahora es siempre una clave externa a la tabla Instituciones. Y, debido a la magia de shared-primary-key, la clave foránea también hace referencia a la entrada en la tabla de subclases adecuada, sin trabajo adicional.

Puede crear cuatro vistas que combinen datos institucionales con cada una de las cuatro tablas de subclases, por conveniencia.

Busque "Especialización de ER", "Herencia de tabla de clase", "Clave primaria compartida" y tal vez "Herencia de tabla única" en la web, y aquí en SO. Aquí hay etiquetas para la mayoría de estos conceptos o técnicas en SO.

Answer 3

Es posible que desee considerar un modelo de datos Tipo / Subtipo. Esto es muy parecido a la clase / subclases en la programación orientada a objetos, pero mucho más difícil de implementar, y ningún RDBMS (que yo sepa) los soporta nativamente. La idea general es:

• Usted define un Tipo (Edificio), crea una tabla para él, le da una clave principal
• Usted define dos o más subtipos (aquí, Hospital, Clínica, Escuela, Universidad), crea tablas para cada uno de ellos, crea claves primarias ... pero las claves primarias también son claves foráneas que hacen referencia a la tabla Construir
• Su tabla con una columna "Tipo de objeto" ahora se puede construir con una clave externa en la tabla de Construcción. Tendría que unirse a algunas tablas para determinar qué tipo de construcción es, pero tendría que hacer eso de todos modos. Eso, o almacenar datos redundantes.

Has notado el problema con este modelo, ¿verdad? ¿Qué impide que un Edificio tenga entradas en dos o más de las tablas de subtipo? Me alegro de haber preguntado:

1. Agregue una columna, tal vez "BuildingType", a Building, digamos char (1) con valores permitidos de {H, C, S, U} que indiquen (duh) el tipo de construcción.
2. Cree una restricción única en BuildingID + BuildingType
3. Tener la columna BulidingType en las subtablas. Ponga una restricción de verificación para que solo se pueda establecer en el valor (H para la tabla Hospitales, etc.) En teoría, esta podría ser una columna calculada; en la práctica, esto no funcionará debido al siguiente paso:
4. Construya la clave foránea para relacionar las tablas usando ambas columnas

Voila: Dado un conjunto de hileras BUILDING con tipo H, una entrada en la tabla SCHOOL (con tipo S) no se puede configurar para hacer referencia a ese edificio

Recordarás que dije que fue difícil de implementar.

De hecho, la gran pregunta es: ¿vale la pena hacerlo? Si tiene sentido implementar los cuatro (o más, a medida que pasa el tiempo) tipos de construcción como tipo / subtipo (otras ventajas de normalización: un lugar para la dirección y otros atributos comunes a cada edificio, con atributos específicos del edificio almacenados en las subtablas), bien puede valer la pena el esfuerzo adicional para construir y mantener. De lo contrario, volverás al punto de partida: un modelo lógico que es difícil de implementar en el RDBMS promedio actual.