Como você pode representar inheritance em um database?

Estou pensando em como representar uma estrutura complexa em um database do SQL Server.

Considere um aplicativo que precisa armazenar detalhes de uma família de objects, que compartilham alguns atributos, mas muitos outros não são comuns. Por exemplo, um pacote de seguro comercial pode include cobertura de responsabilidade, automóvel, propriedade e indenização dentro do mesmo registro de apólice.

É trivial implementar isso em C #, etc, já que você pode criar uma Política com uma coleção de Seções, onde a Seção é herdada conforme necessário para os vários tipos de cobertura. No entanto, bancos de dados relacionais não parecem permitir isso facilmente.

Eu posso ver que existem duas escolhas principais:

  1. Crie uma tabela de políticas, depois uma tabela de seções, com todos os campos necessários, para todas as variações possíveis, a maioria das quais seria nula.

  2. Crie uma tabela de políticas e várias tabelas de seção, uma para cada tipo de cobertura.

Ambas as alternativas parecem insatisfatórias, especialmente porque é necessário escrever consultas em todas as seções, o que envolveria numerosas junções ou numerosas verificações de nulos.

Qual é a melhor prática para esse cenário?

@Bill Karwin descreve três modelos de inheritance em seu livro SQL Antipatterns , ao propor soluções para o antipadrão de Entidade-Atributo-Valor SQL. Esta é uma breve visão geral:

Herança de tabela única (também conhecida como Tabela por inheritance hierárquica):

Usar uma única tabela como em sua primeira opção é provavelmente o design mais simples. Como você mencionou, muitos atributos que são específicos do subtipo terão que receber um valor NULL nas linhas em que esses atributos não se aplicam. Com este modelo, você teria uma tabela de políticas, que seria algo como isto:

 +------+---------------------+----------+----------------+------------------+ | 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 -----/ 

Manter o design simples é uma vantagem, mas os principais problemas com essa abordagem são os seguintes:

  • Quando se trata de adicionar novos subtipos, você teria que alterar a tabela para acomodar os atributos que descrevem esses novos objects. Isso pode se tornar rapidamente problemático quando você tem muitos subtipos ou planeja adicionar subtipos regularmente.

  • O database não poderá impor quais atributos se aplicam e quais não, já que não há metadados para definir quais atributos pertencem a quais subtipos.

  • Você também não pode aplicar NOT NULL em atributos de um subtipo que deve ser obrigatório. Você teria que lidar com isso em seu aplicativo, o que geralmente não é o ideal.

Herança da Mesa de Concreto:

Outra abordagem para lidar com a inheritance é criar uma nova tabela para cada subtipo, repetindo todos os atributos comuns em cada tabela. Por exemplo:

 --// 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 projeto basicamente resolverá os problemas identificados para o método de tabela única:

  • Atributos obrigatórios agora podem ser aplicados com NOT NULL .

  • Adicionar um novo subtipo requer a adição de uma nova tabela em vez de adicionar colunas a uma existente.

  • Também não há risco de que um atributo inadequado seja definido para um subtipo específico, como o campo vehicle_reg_no para uma política de propriedade.

  • Não há necessidade do atributo type como no método de tabela única. O tipo agora é definido pelos metadados: o nome da tabela.

No entanto, este modelo também vem com algumas desvantagens:

  • Os atributos comuns são misturados com os atributos específicos do subtipo e não há uma maneira fácil de identificá-los. O database também não saberá.

  • Ao definir as tabelas, você teria que repetir os atributos comuns para cada tabela de subtipos. Isso definitivamente não é DRY .

  • Procurar por todas as políticas, independentemente do subtipo, torna-se difícil e exigiria um monte de UNION s.

É assim que você teria que consultar todas as políticas, independentemente do 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 como a adição de novos subtipos exigiria que a consulta acima fosse modificada com um UNION ALL adicional para cada subtipo. Isso pode facilmente levar a erros em sua aplicação se esta operação for esquecida.

Herança da tabela de classs (também conhecida como tabela por tipo de inheritance):

Esta é a solução que @ David menciona na outra resposta . Você cria uma única tabela para sua class base, que inclui todos os atributos comuns. Em seguida, você criaria tabelas específicas para cada subtipo, cuja chave primária também serve como uma chave estrangeira para a tabela base. Exemplo:

 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 solução resolve os problemas identificados nos outros dois projetos:

  • Atributos obrigatórios podem ser aplicados com NOT NULL .

  • Adicionar um novo subtipo requer a adição de uma nova tabela em vez de adicionar colunas a uma existente.

  • Não há risco de que um atributo inadequado seja definido para um subtipo específico.

  • Não há necessidade do atributo type .

  • Agora os atributos comuns não são mais misturados com os atributos específicos do subtipo.

  • Nós podemos ficar secos, finalmente. Não há necessidade de repetir os atributos comuns para cada tabela de subtipos ao criar as tabelas.

  • O gerenciamento de um id incremento automático para as políticas fica mais fácil, porque isso pode ser manipulado pela tabela base, em vez de cada tabela de subtipo gerando-os independentemente.

  • Procurar por todas as políticas, independentemente do subtipo, agora se torna muito fácil: Não é necessário UNION – apenas as SELECT * FROM policies .

Considero a abordagem da tabela de classs como a mais adequada na maioria das situações.


Os nomes desses três modelos vêm do livro de Martin Fowler, Patterns of Enterprise Application Architecture .

A terceira opção é criar uma tabela “Policy” e, em seguida, uma tabela “SectionsMain” que armazena todos os campos que estão em comum entre os tipos de seções. Em seguida, crie outras tabelas para cada tipo de seção que contenha apenas os campos que não estão em comum.

Decidir qual é o melhor depende principalmente de quantos campos você possui e como deseja escrever seu SQL. Eles todos trabalhariam. Se você tem apenas alguns campos, então eu provavelmente iria com # 1. Com “lotes” de campos eu iria inclinar-se para # 2 ou # 3.

Com as informações fornecidas, eu modelaria o database para ter o seguinte:

POLÍTICAS

  • POLICY_ID (chave primária)

RESPONSABILIDADES

  • LIABILITY_ID (chave primária)
  • POLICY_ID (chave estrangeira)

PROPRIEDADES

  • PROPERTY_ID (chave primária)
  • POLICY_ID (chave estrangeira)

… e assim por diante, porque espero que haja atributos diferentes associados a cada seção da política. Caso contrário, poderia haver uma única tabela policy_id e, além do policy_id , haveria um section_type_code

De qualquer forma, isso permitiria o suporte a seções opcionais por política …

Eu não entendo o que você acha insatisfatório sobre esta abordagem – é assim que você armazena dados enquanto mantém a integridade referencial e não duplica dados. O termo é “normalizado” …

Como o SQL é baseado em SET, ele é um pouco estranho aos conceitos de programação procedural / OO e requer um código para fazer a transição de um domínio para outro. Geralmente, os ORMs são considerados, mas não funcionam bem em sistemas complexos de alto volume.

A outra maneira de fazer isso é usando o componente INHERITS . Por exemplo:

 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); 

Assim, é possível definir uma inheritance entre tabelas.

Confira a resposta que eu dei aqui

Mapeamento one-to-one Fluent NHibernate com chaves sintéticas

Eu me inclino para o método # 1 (uma tabela de seção unificada), para recuperar eficientemente políticas inteiras com todas as suas seções (que eu suponho que seu sistema estará fazendo muito).

Além disso, não sei qual versão do SQL Server você está usando, mas em 2008, as colunas esparsas ajudam a otimizar o desempenho em situações em que muitos dos valores em uma coluna serão NULL.

Em última análise, você terá que decidir o quão “similar” as seções de política são. A menos que eles diferem substancialmente, acho que uma solução mais normalizada pode ser mais problemática do que vale a pena … mas só você pode fazer essa binding. 🙂

Além disso, na solução Daniel Vassallo, se você usar o SQL Server 2016, há outra solução que usei em alguns casos sem perda considerável de desempenho.

Você pode criar apenas uma tabela com apenas o campo comum e adicionar uma única coluna com a string JSON que contém todos os campos específicos do subtipo.

Eu testei esse design para gerenciar inheritance e estou muito feliz com a flexibilidade que posso usar no aplicativo relativo.