sql - relacion - ¿Cómo se puede representar la herencia en una base de datos?

inheritance sql server

Question

Estoy pensando en cómo representar una estructura compleja en una base de datos de SQL Server.

Considere una aplicación que necesita almacenar detalles de una familia de objetos, que comparten algunos atributos, pero tienen muchos otros que no son comunes. Por ejemplo, un paquete de seguro comercial puede incluir responsabilidad, motor, propiedad y cobertura de indemnización dentro del mismo registro de póliza.

Es trivial implementar esto en C #, etc., ya que puede crear una Política con una colección de Secciones, donde la Sección se hereda según sea necesario para los distintos tipos de cobertura. Sin embargo, las bases de datos relacionales no parecen permitir esto fácilmente.

Puedo ver que hay dos opciones principales:

1. Cree una tabla de Políticas, luego una tabla de Secciones, con todos los campos requeridos, para todas las variaciones posibles, la mayoría de las cuales serían nulas.

2. Cree una tabla de Políticas y numerosas tablas de Sección, una para cada tipo de cobertura.

Ambas alternativas parecen insatisfactorias, especialmente porque es necesario escribir consultas en todas las Secciones, lo que implicaría numerosas combinaciones o numerosos controles nulos.

¿Cuál es la mejor práctica para este escenario?

Answer 1

Además, en la solución de Daniel Vassallo, si usa SQL Server 2016, hay otra solución que utilicé en algunos casos sin pérdida considerable de rendimiento.

Puede crear solo una tabla con solo el campo común y agregar una sola columna con la cadena JSON que contiene todos los campos específicos del subtipo.

He probado este diseño para administrar la herencia y estoy muy contento por la flexibilidad que puedo usar en la aplicación relativa.

Answer 2

Con la información provista, modelaría la base de datos para tener lo siguiente:

POLITICAS

• POLICY_ID (clave principal)

PASIVO

• LIABILITY_ID (clave principal)
• POLICY_ID (clave externa)

Propiedades

• PROPERTY_ID (clave principal)
• POLICY_ID (clave externa)

... y así sucesivamente, porque espero que haya diferentes atributos asociados con cada sección de la política. De lo contrario, podría haber una única tabla de SECTIONS y, además de policy_id , habría un section_type_code ...

De cualquier manera, esto le permitirá admitir secciones opcionales por política ...

No entiendo lo que encuentra insatisfactorio sobre este enfoque: así es como se almacenan los datos mientras se mantiene la integridad referencial y no se duplican los datos. El término está "normalizado" ...

Debido a que SQL está basado en SET, es bastante extraño para los conceptos de programación procedural / OO y requiere que el código pase de un reino a otro. A menudo se consideran los ORM, pero no funcionan bien en sistemas complejos de alto volumen.

Answer 3

La otra forma de hacerlo es usar el componente INHERITS . Por ejemplo:

CREATE TABLE person (
id int ,
name varchar(20),
CONSTRAINT pessoa_pkey PRIMARY KEY (id)
);
CREATE TABLE natural_person (
social_security_number varchar(11),
CONSTRAINT pessoaf_pkey PRIMARY KEY (id)
) INHERITS (person);
CREATE TABLE juridical_person (
tin_number varchar(14),
CONSTRAINT pessoaj_pkey PRIMARY KEY (id)
) INHERITS (person);

Por lo tanto, es posible definir una herencia entre tablas.

Answer 4

La tercera opción es crear una tabla de "Política", luego una tabla "SectionsMain" que almacena todos los campos que son comunes en todos los tipos de secciones. Luego, cree otras tablas para cada tipo de sección que solo contenga los campos que no son comunes.

Decidir cuál es mejor depende principalmente de cuántos campos tiene y cómo desea escribir su SQL. Todos trabajarían. Si tiene solo unos pocos campos, entonces probablemente vaya con el n. ° 1. Con "muchos" campos me inclinaría hacia el # 2 o el # 3.

Answer 5

Me inclino por el método n. ° 1 (una tabla unificada de secciones), con el fin de recuperar de manera eficiente políticas enteras con todas sus secciones (que supongo que su sistema hará mucho).

Además, no sé qué versión de SQL Server está utilizando, pero en 2008+ Columnas dispersas ayudan a optimizar el rendimiento en situaciones donde muchos de los valores en una columna serán NULL.

En última instancia, tendrá que decidir qué tan "similares" son las secciones de políticas. A menos que difieran sustancialmente, creo que una solución más normalizada podría ser más problemática de lo que vale ... pero solo tú puedes hacer esa llamada. :)

Answer 6

Mira la respuesta que di aquí

Mapeo NHibernate eficiente con claves sintéticas

Answer 7

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

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

Usar una sola tabla como en su primera opción es probablemente el diseño más simple. Como mencionó, muchos atributos que son específicos de subtipo tendrán que tener un valor NULL 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, debe modificar la tabla para acomodar los atributos que describen estos nuevos objetos. Esto puede convertirse rápidamente en un problema cuando tiene muchos subtipos, o si planea agregar subtipos de manera regular.

• La base de datos no podrá aplicar 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 a los atributos de un subtipo que debería ser obligatorio. Tendría que manejar esto en su aplicación, que en general no es ideal.

Herencia de Mesa 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 .

• Agregar un nuevo subtipo requiere 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 particular, como el campo vehicle_reg_no para una política de propiedad.

• No es necesario el atributo de 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 presenta 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 tampoco lo sabrá.

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

• La búsqueda de todas las políticas independientemente del subtipo se vuelve difícil, y requeriría un montón de UNION .

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;

Tenga en cuenta que agregar nuevos subtipos requeriría que la consulta anterior se modifique con 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 Tabla por Tipo de Herencia):

Esta es la solución que @David menciona en la otra respuesta . Usted crea una única tabla para su clase base, que incluye todos los atributos comunes. Luego, creará tablas específicas para cada subtipo, cuya clave principal 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 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.

• Podemos permanecer secos, finalmente. No es necesario repetir los atributos comunes para cada tabla de subtipos al crear las tablas.

• Gestionar un id incremento automático 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 manera independiente.

• Ahora es muy fácil buscar todas las políticas, independientemente del subtipo: no se necesita UNION , solo una SELECT * FROM policies .

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

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