¿Cómo puede representar la herencia en una base de datos?

@Bill Karwin describe tres modelos de herencia en su libro SQL Antipatterns, al proponer soluciones al antipattern SQL Entity-Attribute-Value. Este es un breve resumen:

Herencia de Tabla Única (también conocida como Herencia de Tabla Por Jerarquía):

Usar una sola tabla como en su primera opción es probablemente el diseño más simple. Como mencionaste, muchos atributos que son específicos de subtipos tendrán que recibir un valor NULL en filas donde estos atributos no se aplican. Con este modelo, tendría una tabla de políticas, que se vería algo como esto:

+------+---------------------+----------+----------------+------------------+
| 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 alterar la tabla para acomodar los atributos que describen estos nuevos objetos. Esto puede convertirse rápidamente en problemático cuando tiene muchos subtipos, o si planea agregar subtipos en de forma regular.

• 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 NULL en atributos de un subtipo que deberían ser obligatorios. Usted tendría que manejar esto en su aplicación, que en general no es ideal.

Herencia de la Tabla de concreto:

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.

• Para agregar un nuevo subtipo es necesario agregar una nueva tabla en lugar de agregar columnas a una existente.

• Tampoco hay riesgo de que se establezca un atributo inapropiado para un subtipo en particular, como el campo vehicle_reg_no para una directiva de propiedades.

• No es necesario el atributo type 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 viene con algunas desventajas:

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

• Al definir las tablas, tendría que repetir los atributos comunes para cada tabla de subtipos. Eso definitivamente no es SECO .

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

Así es como tendría que consultar todas las políticas independientemente del 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;

Observe cómo agregar nuevos subtipos requeriría que la consulta anterior sea modificado con un UNION ALL adicional para cada subtipo. Esto puede conducir fácilmente a errores en su aplicación si se olvida esta operación.

Herencia de Tabla de Clase (también conocida como Herencia de Tabla Por Tipo):

Esta es la solución que @David menciona en la otra respuesta. Se crea una sola tabla para la clase base, que incluye todos los atributos comunes. Luego crearía tablas específicas para cada subtipo, cuya clave primaria también sirve como una clave externa a la mesa de 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.

• Para agregar un nuevo subtipo es necesario agregar una nueva tabla en lugar de agregar columnas a una existente.

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

• No es necesario el atributo type.

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

• Por fin podemos mantenernos secos. No es necesario repetir los atributos comunes para cada tabla de subtipos al crear las tablas.

• Administrar un incremento automático id para 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 subtipos las genere de forma independiente.

• Buscar todas las políticas independientemente del subtipo ahora se vuelve muy fácil: No se necesitan UNIONs - solo un SELECT * FROM policies.

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

Los nombres de estos tres modelos provienen de El libro de Martin Fowler Patrones de Arquitectura de Aplicaciones Empresariales.