De acordo com Martin Fowler, existem 3 abordagens para o problema de herança de tabelas:

• Herança de Tabela Única : Uma tabela representa todos os tipos. Atributos não utilizados são NULLed.
• Herança de tabela concreta : uma tabela por tipo concreto, cada coluna da tabela para cada atributo do tipo. Nenhuma relação entre as tabelas.
• Herança de Tabela de Classe : Uma tabela por tipo, cada tabela possui atributos apenas para novos atributos não herdados. As tabelas estão relacionadas refletindo a hierarquia de herança de tipo real.

Você pode começar com eles como ponto de partida para pesquisar os prós e os contras de cada abordagem. A essência disso é que todas as abordagens têm grandes desvantagens e nenhuma tem qualquer vantagem esmagadora. Mais conhecido como incompatibilidade de impedância relacional de objeto , esse problema ainda não encontrou uma solução.

Pessoalmente, acho que o tipo de problema que um design relacional ruim pode causar é muito mais sério do que o tipo de problema decorrente de um design de tipo ruim . O design incorreto do banco de dados leva a consultas lentas, anomalias de atualização, explosão de tamanho de dados, bloqueios e aplicativos que não respondem e dezenas a centenas de Gigabytes de dados afundados no formato errado . O design de tipo ruim dificulta a manutenção e atualização do código , não o tempo de execução. Portanto, em meu livro, o design relacional correto supera qualquer tipo de pureza OO repetidamente.

Na medida em que esta pergunta é uma continuação de Minha implementação do padrão de design de tipo/subtipo (para subclasses mutuamente exclusivas) está correta? , que é uma continuação de Não sei como transformar entidade variável em tabela relacional , eu perguntaria: o que exatamente você está tentando otimizar? Armazenar? O modelo de objeto? Complexidade da consulta? Desempenho da consulta? Existem compensações ao otimizar um aspecto em relação a outro, pois não é possível otimizar todos os aspectos ao mesmo tempo.

Eu concordo completamente com os pontos de Remus em relação a:

• Existem prós e contras para cada abordagem (ou seja, o sempre presente fator "depende") e
• A primeira prioridade é a eficiência do modelo de dados (um modelo de dados ineficiente não pode ser corrigido por um código de aplicativo limpo e/ou eficiente)

Dito isso, a escolha que você enfrenta é entre as seguintes, organizadas em ordem de menos normalização para mais normalização:

• promovendo a propriedade Epara a tabela do tipo base
• mantendo-o em várias tabelas de subtipos
• normalizando totalmente Epara uma nova tabela de subclasse intermediária no mesmo nível que C, isso Ae Bserão diretamente subclasses de ( resposta de @MDCCL )

Vejamos cada opção:

Mover propriedade Epara tabela do tipo base

PROs

• Complexidade de consulta reduzida para consultas que precisam , Emas não X, You Z.
• Potencialmente mais eficiente para consultas que precisam , Emas não X, You Z(especialmente consultas agregadas) devido a nenhum JOIN.
• Potencial para criar um índice (D, E)(e, em caso afirmativo, potencialmente um índice filtrado em (D, E)que EntityType <> C, se tal condição for permitida)

CONs

• Não é possível marcar EcomoNOT NULL
• Precisa de mais CHECK CONSTRAINTna tabela de tipo base para garantir que E IS NULLquando EntityType = C(embora isso não seja um grande problema)
• Necessidade de educar os usuários do modelo de dados sobre por que Edeve ser NULL, e deve até mesmo ser totalmente ignorado, quando EntityType = C.
• Um pouco menos eficiente quando Eé um tipo de comprimento fixo e uma grande parte das linhas são para EntityType de C(ou seja, não usando E, portanto, é NULL) e não usando a SPARSEopção na coluna ou compactação de dados no índice agrupado
• Potencialmente menos eficiente para consultas que não precisam, Epois a presença de Ena tabela do tipo base aumentará o tamanho de cada linha que, por sua vez, diminuirá o número de linhas que podem caber em uma página de dados. Mas isso é altamente dependente do tipo de dados exato de E, o FILLFACTOR, quantas linhas existem na tabela de tipo base, etc.

Mantenha a propriedade Eem cada tabela de subtipo

PROs

• Modelo de dados mais limpo (ou seja, não precisa se preocupar em educar outras pessoas sobre por que a coluna Ena tabela do tipo base não deve ser usada porque "ela realmente não está lá")
• Provavelmente se assemelha mais ao modelo de objeto
• Pode marcar a coluna como NOT NULLse esta fosse uma propriedade obrigatória da entidade
• Não há necessidade de extra CHECK CONSTRAINTna tabela de tipo base para garantir que E IS NULLquando EntityType = C(embora isso não seja um ganho enorme)

CONs

• Requer JOIN para sub-tipo Tabela(s) para obter esta propriedade
• Potencialmente um pouco menos eficiente ao precisar Ede , devido ao JOIN, dependendo de quantas linhas de A+ Bvocê tem em oposição a quantas linhas Cexistem.
• Um pouco mais difícil/complexo para operações que tratam exclusivamente de entidades Ae B(e não C ) como sendo do mesmo “tipo”. Claro, você pode abstrair isso por meio de uma View que faz um UNION ALLentre uma SELECTdas tabelas JOINed para Ae outra SELECTdas tabelas JOINed para B. Isso reduzirá a complexidade das consultas SELECT, mas não será tão útil para as consultas INSERTand .UPDATE
• Dependendo das consultas específicas e da frequência com que são executadas, isso pode ser uma ineficiência potencial nos casos em que ter um índice (D, E)ativado realmente ajudaria uma ou mais consultas usadas com frequência, pois elas não podem ser indexadas juntas.

Normalize Epara a tabela intermediária entre a classe base e A&B

_{(Observe que eu gosto da resposta de @MDCCL como uma alternativa viável, dependendo das circunstâncias. O que se segue não é uma crítica estrita a essa abordagem, mas um meio de adicionar alguma perspectiva - a minha, é claro - avaliando no mesmo contexto das duas opções que já havia proposto. Isso tornará mais fácil esclarecer o que vejo como a diferença relativa entre a normalização total e a abordagem atual de normalização parcial.)}

PROs

• o modelo de dados é totalmente normalizado (não pode haver nada inerentemente errado com isso, já que é para isso que os RDBMSs foram projetados)
• complexidade de consulta reduzida para consultas que precisam de Ae B, mas não C(ou seja, não há necessidade de duas consultas unidas via UNION ALL)

CONs

• um pouco mais de espaço ocupado (a Bartabela duplica o ID e há uma nova coluna, BarTypeCode) [insignificante, mas algo para estar ciente]
• leve aumento na complexidade da consulta, pois JOINé necessário um adicional para chegar a um AouB
• área de superfície aumentada para bloqueio, principalmente em INSERT( DELETEpode ser manipulado implicitamente por meio da marcação de chaves estrangeiras como ON CASCADE DELETE), pois a transação será mantida aberta um pouco mais na tabela de classe base (ou seja Foo) [insignificante, mas algo para estar ciente]

• nenhum conhecimento direto do tipo real -- Aou B-- dentro da tabela da classe base, Foo; ele só conhece o tipo Brque pode ser Aou B:

  Ou seja, se você precisar fazer consultas sobre as informações básicas gerais, mas precisar categorizar pelo tipo de entidade ou filtrar um ou mais tipos de entidade, a tabela de classe base não possui informações suficientes; nesse caso, você precisa LEFT JOINa Barmesa. Isso também reduzirá a eficácia da indexação da FooTypeCodecoluna.

• nenhuma abordagem consistente para interagir com A& Bvs C:

  Ou seja, se cada entidade se relacionar diretamente com a tabela de classe base, de modo que haja apenas um JOIN para obter a entidade completa, todos poderão criar familiaridade de maneira mais rápida e fácil em termos de trabalho com o modelo de dados. Haverá uma abordagem comum para consultas/procedimentos armazenados que os torna mais rápidos de desenvolver e menos propensos a ter erros. Uma abordagem consistente também torna mais rápido e fácil adicionar novos subtipos no futuro.

• potencialmente menos adaptável a regras de negócios que mudam com o tempo:

  Ou seja, as coisas sempre mudam e é bastante fácil mover E-se para a tabela de classe base se ela se tornar comum a todos os subtipos. Também é bastante fácil mover uma propriedade comum para os subtipos se mudanças na natureza das entidades fizerem com que essa mudança valha a pena. É fácil dividir um subtipo em dois subtipos (basta criar outro SubTypeIDvalor) ou combinar dois ou mais subtipos em um. Por outro lado, e se Emais tarde se tornasse uma propriedade comum de todos os subtipos? Então a camada intermediária da Barmesa não teria sentido e a complexidade adicional não valeria a pena. Claro, é impossível saber se tal mudança aconteceria em 5 ou até 10 anos, então a Bartabela não é necessariamente, nem mesmo altamente provável de ser, uma má ideia (é por isso que eu disse " potencialmente menos adaptável"). Esses são apenas pontos a serem considerados; é uma aposta em qualquer direção.

• agrupamento potencialmente inapropriado:

  Ou seja, apenas porque a Epropriedade é compartilhada entre os tipos de entidade Ae Bnão significa que Adeva B ser agrupada. Só porque as coisas "parecem" as mesmas (isto é, as mesmas propriedades) não significa que sejam as mesmas.

Resumo

Assim como decidir se/quando desnormalizar, a melhor forma de abordar essa situação específica depende de considerar os seguintes aspectos do uso do modelo de dados e garantir que os benefícios superem os custos:

• quantas linhas você terá para cada EntityType (olhe pelo menos 5 anos adiante, assumindo um crescimento acima da média)
• quantos GB cada uma dessas tabelas (tipo base e subtipos) terá em 5 anos?
• que tipo de dados específico é propriedadeE
• é apenas uma propriedade ou existem algumas, ou mesmo várias, propriedades
• quais consultas você precisará que requerem Ee com que frequência elas serão executadas
• quais consultas você precisará que não precisam Ee com que frequência elas serão executadas

Acho que costumo manter por padrão Eas tabelas de subtipos separadas porque é, no mínimo, "mais limpa". Eu consideraria mudar Epara a tabela de tipo base IF: a maioria das linhas não era para EntityType de C; e o número de linhas era pelo menos na casa dos milhões; e , na maioria das vezes, executei consultas que precisavam Ee/ou as consultas que se beneficiariam de um índice (D, E)ou executam com muita frequência e/ou exigem recursos de sistema suficientes, de modo que ter o índice reduz a utilização geral de recursos ou, pelo menos, evita surtos no consumo de recursos que ultrapassam os níveis aceitáveis ​​ou duram o suficiente para causar bloqueio excessivo e/ou aumento de deadlocks.

ATUALIZAR

OP comentou sobre esta resposta que:

Meus empregadores mudaram a lógica do negócio, removendo completamente o E!

Essa alteração é particularmente importante porque é exatamente o que previ que poderia acontecer na subseção "CONs" da seção "Normalizar Epara a tabela intermediária entre a classe base e A& B" acima (6º marcador). A questão específica é quão fácil/difícil é refatorar o modelo de dados quando essas mudanças acontecem (e sempre acontecem). Alguns argumentarão que qualquer modelo de dados pode ser refatorado/alterado, então comece com o ideal. Mas embora seja verdade em um nível técnico que qualquer coisa pode ser refatorada, a realidade da situação é uma questão de escala.

Os recursos não são infinitos, não apenas CPU/Disco/RAM, mas também recursos de desenvolvimento: tempo e dinheiro. As empresas estão constantemente estabelecendo prioridades em projetos porque esses recursos são muito limitados. E muitas vezes (pelo menos na minha experiência), os projetos para ganhar eficiência (tanto o desempenho do sistema quanto o desenvolvimento mais rápido / menos bugs) são priorizados abaixo dos projetos que aumentam a funcionalidade. Embora seja frustrante para nós, pessoal técnico, porque entendemos quais são os benefícios de longo prazo dos projetos de refatoração, é apenas a natureza do negócio que o pessoal menos técnico e de negócios tenha mais facilidade em ver a relação direta entre novas funcionalidades e novos receita. O que isso significa é: "voltaremos para consertar isso mais tarde" == "

Com isso em mente, se o tamanho dos dados for pequeno o suficiente para que as alterações possam ser feitas muito consulta e/ou você tiver uma janela de manutenção longa o suficiente para não apenas fazer as alterações, mas também reverter se algo der errado errado, então normalizar Epara uma tabela intermediária entre a tabela de classe base e as tabelas Ade Bsubclasse pode funcionar (embora isso ainda o deixe sem conhecimento direto do tipo específico ( AouB) na tabela da classe base). MAS, se você tiver centenas de milhões de linhas nessas tabelas e uma quantidade incrível de código referenciando as tabelas (código que deve ser testado quando as alterações são feitas), geralmente vale a pena ser mais pragmático do que idealista. E este é o ambiente com o qual tive que lidar por anos: 987 milhões de linhas e 615 GB na tabela de classe base, espalhados por 18 servidores. E tanto código atingiu essas tabelas (tabelas de classe base e subclasse) que houve muita resistência -- principalmente da gerência, mas às vezes do resto da equipe -- para fazer qualquer alteração devido à quantidade de desenvolvimento e Recursos de controle de qualidade que precisariam ser alocados.

Portanto, mais uma vez, a "melhor" abordagem só pode ser determinada situação por situação: você precisa conhecer seu sistema (ou seja, quantos dados e como as tabelas e o código se relacionam), como realizar a refatoração e as pessoas com quem você trabalha (sua equipe e possivelmente a gerência - você pode obter a adesão deles para esse projeto?). Há algumas mudanças que mencionei e planejei por 1 a 2 anos e fiz vários sprints / lançamentos para obter talvez 85% deles implementados. Mas se você tiver apenas < 1 milhão de linhas e não muito código vinculado a essas tabelas, provavelmente poderá começar do lado mais ideal / "puro" das coisas.

Apenas lembre-se, seja qual for o caminho que você escolher, preste atenção em como esse modelo funciona nos próximos 2 anos, pelo menos (se possível). Preste atenção no que funcionou e no que causou dor, mesmo que pareça a melhor ideia no momento (o que significa que você também precisa se permitir aceitar estragar tudo - todos nós aceitamos - para que você possa avaliar honestamente os pontos problemáticos ). E preste atenção ao motivo pelo qual certas decisões funcionaram ou não, para que você possa tomar decisões com maior probabilidade de serem "melhores" da próxima vez :-).

De acordo com minha interpretação de suas especificações, você deseja encontrar um método para implementar duas estruturas supertipo-subtipo diferentes (mas conectadas ) .

• _{O Dr. EF Codd — o criador do Paradigma Relacional — apresenta e discute relacionamentos desse tipo em seu artigo de 1979 intitulado Extending the Database Relational Model to Capture More Meaning .}

A fim de expor uma abordagem para realizar a tarefa supracitada, adicionarei ao cenário em questão os dois tipos clássicos de entidade hipotéticaFoo chamados e Bar, que detalharei a seguir.

Regras do negócio

Aqui estão algumas declarações que me ajudarão a criar um modelo lógico:

• A Foo is either one Bar or one C
• A Foo is categorized by one FooType
• A Bar is either one A or one C
• A Bar is classified by one BarType

modelo lógico

E então, o modelo lógico IDEF1X ^[1] resultante é mostrado na Figura 1 (e você também pode baixá-lo do Dropbox como um PDF ):

A adição de Foo e Bar

I did not add Foo and Bar to make the model look better, but to make it more expressive. I deem they are important due to the following:

• As A and B share the attribute named E, this feature suggests that they are subentity types of a distinct (but related) sort of concept, event, person, measurement, etc., which I represented by means of the Bar superentity type that, in turn, is a subentity type of Foo, which holds the D attribute at the top of the hierarchy.

• Since C only shares one attribute with the rest of the entity types under discussion, i.e., D, this aspect insinuates that it is a subentity type of another kind of concept, event, person, measurement, etc., so I depicted this circumstance by virtue of the Foo super entity type.

However, these are just assumptions, and since a relational database is meant to reflect the semantics of a certain business context accurately, you have to identify and classify all the things of interest in your specific domain so that you can, precisely, capture more meaning.

Important factors at the design phase

It is quite useful to be aware of the fact that, putting all the terminology aside, an exclusive supertype-subtype cluster is an ordinary relationship. Let us describe the situation in the following way:

• Each exclusive superentity type occurrence is related to only one subentity type complement.

Thus, there is a correspondance (or cardinality) of one-to-one (1:1) in these cases.

As you know from your preceding posts, the discriminator attribute (column, when implemented) plays a paramount role when creating an association of this nature, because it indicates the correct subtype instance with which the supertype is connected. The migration of the PRIMARY KEY from (i) the supertype to (ii) the subtypes is also of prime significance.

Concrete DDL structure

And then I wrote a DDL structure that is based on the logical model presented above:

CREATE TABLE FooType -- Look-up table.
(
    FooTypeCode     CHAR(2)  NOT NULL,
    Description     CHAR(90) NOT NULL, 
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_FooType             PRIMARY KEY (FooTypeCode),
    CONSTRAINT AK_FooType_Description UNIQUE      (Description)
);

CREATE TABLE Foo -- Supertype
(
    FooId           INT      NOT NULL, -- This PK migrates (1) to ‘Bar’ as ‘BarId’, (2) to ‘A’ as ‘AId’, (3) to ‘B’ as ‘BId’, and (4) to ‘C’ as ‘CId’.
    FooTypeCode     CHAR(2)  NOT NULL, -- Discriminator column.
    D               INT      NOT NULL, -- Column that applies to ‘Bar’ (and therefore to ‘A’ and ‘B’) and ‘C’.
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Foo                 PRIMARY KEY (FooId),
    CONSTRAINT FK_from_Foo_to_FooType FOREIGN KEY (FooTypeCode)
        REFERENCES FooType (FooTypeCode)
);

CREATE TABLE BarType -- Look-up table.
(
    BarTypeCode CHAR(1)  NOT NULL,  
    Description CHAR(90) NOT NULL,  
    CONSTRAINT PK_BarType             PRIMARY KEY (BarTypeCode),
    CONSTRAINT AK_BarType_Description UNIQUE      (Description)
);

CREATE TABLE Bar -- Subtype of ‘Foo’.
(
    BarId       INT     NOT NULL, -- PK and FK.
    BarTypeCode CHAR(1) NOT NULL, -- Discriminator column. 
    E           INT     NOT NULL, -- Column that applies to ‘A’ and ‘B’.
    CONSTRAINT PK_Bar             PRIMARY KEY (BarId),
    CONSTRAINT FK_from_Bar_to_Foo FOREIGN KEY (BarId)
        REFERENCES Foo (FooId),
    CONSTRAINT FK_from_Bar_to_BarType FOREIGN KEY (BarTypeCode)
        REFERENCES BarType (BarTypeCode)    
);

CREATE TABLE A -- Subtype of ‘Bar’.
(
    AId INT NOT NULL, -- PK and FK.
    X   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_A             PRIMARY KEY (AId),
    CONSTRAINT FK_from_A_to_Bar FOREIGN KEY (AId)
        REFERENCES Bar (BarId)  
);

CREATE TABLE B -- (1) Subtype of ‘Bar’ and (2) supertype of ‘A’ and ‘B’.
(
    BId INT NOT NULL, -- PK and FK.
    Y   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_B             PRIMARY KEY (BId),
    CONSTRAINT FK_from_B_to_Bar FOREIGN KEY (BId)
        REFERENCES Bar (BarId)  
);

CREATE TABLE C -- Subtype of ‘Foo’.
(
    CId INT NOT NULL, -- PK and FK.
    Z   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_C             PRIMARY KEY (CId),
    CONSTRAINT FK_from_C_to_Foo FOREIGN KEY (FooId)
        REFERENCES Foo (FooId)  
);

With this structure you avoid the storage of NULL marks in your base tables (or relations), which would introduce ambiguity to your data base.

Integrity, consistency and other considerations

Once you are implementing your database, you must ensure that (a) each exclusive supertype row is always complemented by its corresponding subtype counterpart and, in turn, guarantee that (b) such subtype row is compatible with the value contained in the supertype discriminator column. Therefore, it is quite convenient to employ ACID TRANSACTIONS in order to make sure that these conditions are met in your database.

You should not give up the logical soundness, self-expressivity and accuracy of your database, these are aspects that decidedly make your database more solid.

The two previously posted answers already include pertinent points that are certainly worth taking into account when designing, creating and managing your database and its application program(s).

Retrieving data by way of VIEW definitions

You can set up some views that combine columns of the different supertype-subtype groups, so that you can retrieve the data at hand without, e.g., writing the necessary JOIN clauses every time. In this way, you can SELECT directly FROM the VIEW (a derived relation or table) of interest with ease.

As you can see, “Ted” Codd was, undoubtedly, a genius. The tools he bequeathed are quite strong and elegant, and, of course, are well integrated with each other.

Related resources

If you want to analyze some extensive database which involves supertype-subtype relationships, you would find of value the extraordinary answers proposed by @PerformanceDBA to the following Stack Overflow questions:

• Historical / auditable database.

• [O]ne table or many for many different but interacting events?, which comprises nested subtypes.

Note

_{1. Definição de Integração para Modelagem de Informações ( IDEF1X ) é uma técnica de modelagem de dados altamente recomendável que foi estabelecida como padrão em dezembro de 1993 pelo Instituto Nacional de Padrões e Tecnologia dos Estados Unidos ( NIST ). É solidamente baseado em (a) o material teórico inicial de autoria do Dr. EF Codd; em (b) a visão Entidade-Relacionamento de dados, desenvolvida pelo Dr. PP Chen ; e também sobre (c) a Logical Database Design Technique, criada por Robert G. Brown. Vale a pena notar que o IDEF1X foi formalizado por meio da lógica de primeira ordem.}