¿Cómo se puede representar la herencia en una base de datos? [cerrado]

@Bill Karwin describe tres modelos de herencia en su libro SQL Antipatterns , al proponer soluciones al antipatrón SQL Entity-Attribute-Value . Esta es una breve descripción general:

Herencia de tabla única (también conocida como herencia de tabla por jerarquía):

Usar una sola mesa como en su primera opción es probablemente el diseño más simple. Como mencionó, a muchos atributos que son específicos de subtipo se les deberá asignar un NULLvalor en las filas donde estos atributos no se aplican. Con este modelo, tendría una tabla de políticas, que se vería así:

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

Mantener el diseño simple es una ventaja, pero los principales problemas con este enfoque son los siguientes:

• Cuando se trata de agregar nuevos subtipos, tendría que modificar la tabla para acomodar los atributos que describen estos nuevos objetos. Esto puede volverse problemático rápidamente cuando tiene muchos subtipos o si planea agregar subtipos con regularidad.

• La base de datos no podrá imponer qué atributos se aplican y cuáles no, ya que no hay metadatos para definir qué atributos pertenecen a qué subtipos.

• Tampoco puede aplicar NOT NULLatributos de un subtipo que deberían ser obligatorios. Tendrías que manejar esto en tu aplicación, lo cual en general no es ideal.

Herencia de la mesa de hormigón:

Otro enfoque para abordar la herencia es crear una nueva tabla para cada subtipo, repitiendo todos los atributos comunes en cada tabla. Por ejemplo:

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+
                          
--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

Este diseño básicamente resolverá los problemas identificados para el método de tabla única:

• Los atributos obligatorios ahora se pueden aplicar con NOT NULL.

• Agregar un nuevo subtipo requiere agregar una nueva tabla en lugar de agregar columnas a una existente.

• Tampoco existe riesgo de que se establezca un atributo inadecuado para un subtipo particular, como el vehicle_reg_nocampo de una política de propiedad.

• No es necesario el typeatributo como en el método de tabla única. El tipo ahora está definido por los metadatos: el nombre de la tabla.

Sin embargo, este modelo también presenta algunas desventajas:

• Los atributos comunes se mezclan con los atributos específicos del subtipo y no existe una manera fácil de identificarlos. La base de datos tampoco lo sabrá.

• Al definir las tablas, habría que repetir los atributos comunes para cada tabla de subtipo. Definitivamente eso no es SECO .

• Buscar todas las políticas, independientemente del subtipo, se vuelve difícil y requeriría un montón de UNIONanuncios.

Así tendrías que consultar todas las políticas sin importar el tipo:

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

Tenga en cuenta que agregar nuevos subtipos requeriría que la consulta anterior se modifique con una consulta adicional UNION ALLpara cada subtipo. Esto puede provocar fácilmente errores en su aplicación si se olvida esta operación.

Herencia de tabla de clases (también conocida como herencia de tabla por tipo):

Esta es la solución que @David menciona en la otra respuesta . Crea una tabla única para su clase base, que incluye todos los atributos comunes. Luego crearías tablas específicas para cada subtipo, cuya clave primaria también sirve como clave externa para la tabla base. Ejemplo:

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

Esta solución resuelve los problemas identificados en los otros dos diseños:

• Los atributos obligatorios se pueden aplicar con NOT NULL.

• Agregar un nuevo subtipo requiere agregar una nueva tabla en lugar de agregar columnas a una existente.

• No hay riesgo de que se establezca un atributo inadecuado para un subtipo en particular.

• No es necesario el typeatributo.

• Ahora los atributos comunes ya no se mezclan con los atributos específicos del subtipo.

• Podemos permanecer SECO, por fin. No es necesario repetir los atributos comunes para cada tabla de subtipo al crear las tablas.

• Administrar un incremento automático idpara las políticas se vuelve más fácil, porque esto puede ser manejado por la tabla base, en lugar de que cada tabla de subtipo las genere de forma independiente.

• Buscar todas las políticas independientemente del subtipo ahora se vuelve muy fácil: no UNIONes necesario, solo un archivo SELECT * FROM policies.

Considero que el enfoque de la tabla de clases es el más adecuado en la mayoría de situaciones.

Los nombres de estos tres modelos provienen del libro Patterns of Enterprise Application Architecture de Martin Fowler .

Aug 26 '2010 20:08 Daniel Vassallo