Diseño de base de datos subtipo Supertype

Tengo una pregunta sobre el diseño de superype-subtipo en una base de datos relacional. Si tuviera un supertipo con dos tablas de subtipos, tendría el PK del supertipo relacionado con el PK de las dos tablas de subtipo como FK. Digamos que tuve algo como esto:

Tipo

TypeID PK

SuperType

ID PK TypeID FK

Subtipo A

ID PK, FK

Subtipo B

ID PK, FK

En el lado de la base de datos, ¿cómo me aseguraré de que las ID de Supertype de un tipo dado solo se incluyan en la tabla de subtipos adecuada? Por ejemplo, no me gustaría que una ID de Supertype con Tipo A se coloque en la tabla SubtypeB. ¿Hay alguna manera de evitar fácilmente que esto suceda en el lado de la base de datos? Sé que esto podría manejarse en código, pero ¿y si el código tuviera errores? ¿O qué pasa si alguien ingresa manualmente el ID incorrecto en una de las tablas de subtipo? Supongo que estoy buscando alguna manera de hacer esto imposible en el lado de la base de datos.

¿Algunas ideas? Tal vez el PK en la tabla Supertype debería ser la combinación ID y TypeID con una restricción única en la columna ID para evitar un registro con ambos tipos en la tabla SuperType … y luego las tablas Subtype tendrían la ID combinada y TypeID PK con una restricción en el TypeID para que solo sea del tipo que debería ser para la tabla de subtipos apropiada?

En el lado de la base de datos, ¿cómo me aseguraré de que las ID de Supertype de un tipo dado solo se incluyan en la tabla de subtipos adecuada?

En un DBMS que admite restricciones diferidas, podría hacer algo como esto:

enter image description here

Con la siguiente restricción en SuperType :

 CHECK ( ( (SubtypeAId IS NOT NULL AND SubtypeAId = SuperTypeId) AND SubtypeBId IS NULL ) OR ( SubtypeAId IS NULL AND (SubtypeBId IS NOT NULL AND SubtypeBId = SuperTypeId) ) ) 

Estos peculiares FK circulares 1 combinados con CHECK aseguran la exclusividad y la presencia del niño (el CHECK garantiza exactamente uno de los siguientes: SuprerType.SubtypeAId , SuprerType.SubtypeBId no es NULL y coincide con el SuperTypeId ). Postergue las FK secundarias (o el CHECK si su DBMS lo admite) para resolver el problema de la gallina y el huevo al insertar datos nuevos.

1 SubtypeA.SubtypeAId referencia a SuperType.SuperTypeId y SuperType.SubtypeAId referencia a SubtypeA.SubtypeAId , ídem para el otro subtipo.

Si su DBMS no admite restricciones diferidas, puede permitir (en CHECK) que ambos campos sean NULL y renunciar a la ejecución de la presencia del niño (aún conserva la exclusividad).


Alternativamente, solo la exclusividad (pero no la presencia) también se puede aplicar de esta manera:

enter image description here

NOTA: Es posible que necesite agregar UNIQUE redundante en SuperType {SuperTypeId, TypeId} si el DBMS no admite FK “fuera de clave”.

Con la siguiente restricción en SubtypeA :

 CHECK(TypeId = 1) 

Y la siguiente restricción en SubtypeB :

 CHECK(TypeId = 2) 

Usé 1 y 2 para denotar subtipos específicos: puedes usar cualquier cosa que desees, siempre y cuando seas consistente.

Además, podría considerar el ahorro de espacio de almacenamiento mediante el uso de una columna calculada para TypeId del subtipo (como las columnas virtuales Oracle 11).


Por cierto, hacer cumplir la presencia y la exclusividad a través de la lógica de la aplicación no se considera una mala estrategia general. La mayoría de las veces, debe esforzarse por aplicar la mayor cantidad posible de integridad en la base de datos, pero en este caso particular, hacerlo a nivel de aplicación a menudo se considera justificado para evitar las complicaciones anteriores.


Y, por último, “todas las clases en tablas separadas” no es la única estrategia para implementar la herencia. Si implementa herencia utilizando “todo en una tabla” o “clases concretas en tablas separadas”, hacer que tanto la presencia como la exclusividad de los subtipos se vuelvan mucho más fáciles.

Eche un vistazo a esta publicación para obtener más información.

Use un disparador para propagar la nueva entrada en la tabla de supertipos a la tabla de subtipos apropiada.