Implementando Herança de Tabela no SQL Server

Começaremos neste artigo a implementar herança de tabela no SQL Server.

Quando estamos projetando um banco de dados, algumas vezes nos cruzamos com situações onde existem múltiplos tipos de entidades que nós estamos modelando, mas que nós gostaríamos que elas tivessem certos atributos ou relações em comum. Usar tabelas "sub-tipos" é uma forma simples de implementar herança de tabelas no SQL Server.

Por exemplo, uma questão surgiu recentemente sobre a modelagem das seguintes entidades em um banco de dados chamado “Escola”:

Casa uma dessas entidades possui muitos dos mesmos atributos, tais como primeiro nome, sobrenome e data de nascimento. Porém, precisamos separá-los em múltiplas tabelas porque precisamos armazenar e rastrear dados diferentes para estudantes, professores e pais: estudantes possuem notas, classes e pais, professores possuem classes onde dão aulas, habilidades, informações sobre o emprego e outros.

Além de compartilhar atributos em comum, essas entidades possuem também relacionamentos em comum. Por exemplo, para cada uma dessas entidades nós podemos também armazenar endereços, número de telefones, histórico de correspondência, etc. Para fazer isso em um “agradável” banco de dados normalizado, nós modelaríamos estes dados através de tabelas adicionais:

No topo da redundância, tabelas similares, nós necessitaríamos de uma bagunça completa de elementos redundantes, como stored procedures similares para adicionar/atualizar/deletar/selecionar itens a partir dessas tabelas. Porém mais uma vez, nós iremos precisar de tabelas diferentes para essas diferentes entidades porque cada uma delas possui seu próprio conjunto de relações e atributos.

Existe uma forma mais fácil de modelar isto em um banco de dados relacional? Absolutamente! Vamos ver como?

Criando uma "Tabela Base"

Nós podemos iniciar pelo reconhecimento de que Estudantes, Professores e Pais são todos "Pessoas", e nós podemos notar que faz sentido dizer que todas as Pessoas podem ter endereços, número de telefones e histórico de correspondência:

Na tabela Pessoas, nós gostaríamos de armazenar todos os atributos comuns de Estudantes, Professores e Pais que discutimos anteriormente: nome, data de nascimento e outros. Nós removemos todos esses atributos comuns das tabelas Estudantes, Professores e Pais e colocamos todos eles em um único lugar. Agora, a manutenção número de telefones, endereços, nomes, data de nascimento e correspondência pode ser completamente feita com um conjunto de stored procedures genéricas. A redundância dessas atividades foi agora reduzida, e qualquer mudança nos formatos do número de telefone ou endereço pode ser feita em um lugar. Nós podemos nos referir à tabela Pessoa como uma "tabela base".

Derivando Sub-Tabelas a partir de Tabelas Base

É claro que ainda precisamos das tabelas Estudantes, Professores e Pais – mais agora a chave primária dessas tabelas também se torna uma chave estrangeira para a tabela Pessoas.

Isso ocorre porque qualquer linha nas tabelas Estudantes, Professores ou Pais requerem uma linha relacionada na tabela Pessoas, e ela também compartilha a mesma chave primária como a tabela Pessoas (ou seja, é um relacionamento com cardinalidade 1:1). Neste artigo iremos nos referir às tabelas Estudantes, Professores e Pais como sub-tabelas da tabela Pessoas. Pense nessas sub-tabelas como tabelas que estendem as informações básicas que a tabela Pessoa provê; isso é similar a como herança funciona em Programação Orientada a Objetos (POO). Essencialmente, nós estamos realizando "herança de tabela" fazendo isso; pois todo estudante, professor e pai é por definição também uma "pessoa" e nós estamos garantindo que nós podemos trabalhar com essas entidades da mesma forma se as tratarmos como Pessoa se desejamos, ou nós podemos trabalhar com elas usando seus atributos e relação específicos.

Isso nos dá um esquema como o apresentado na Listagem 1.

Listagem 1. Resumo dos Scripts de Criação da Tabelas.
create table Pessoa(PessoaID int primary key, Nome varchar ... etc ...) create table Estudantes(PessoaID int primary key references Pessoa(PessoaID), ...) create table Professores(PessoaID int primary key references Pessoa(PessoaID), ...) create table Pais(PessoaID int primary key references Pessoa(PessoaID), ...)

Com este esquema particular, note algumas coisas:

Este 3º ponto é importante, pois ele pode não ser bem o que você deseja; nós iremos retornar a isso em um momento futuro. Mais para atender a nossos objetivos, pensamos que faz sentido: supor que uma pessoa é inicialmente um estudante, retorna à escola para trabalhar como professor, e então eventualmente possui filhos na escola. Este esquema nos permite atender a esse cenário.

O 4º ponto é importante de ser considerado. Nós desejamos adicionar uma coluna "TipoPessoa" para a tabela, e criar uma tabela TiposPessoa – mas então nós introduzimos redundâncias e a possibilidade de dados conflitantes. Além disso, como mencionado, uma pessoa pode ser mais que um "tipo" por vez, então a existência desta coluna não faz realmente sentido nesta tabela sem mudar nosso modelo lógico.

Usar este esquema é fácil; nós podemos rapidamente pegar todas as informações para todos os estudantes com um simples JOIN (Listagem 2).

Listagem 2. Consultando os dados de Estudante.
select s.*, p.* from estudantes s inner join pessoas p on s.pessoaID = p.pessoaID

Você pode substituir * com as colunas reais que você precisa, obviamente

Então, tudo funciona perfeitamente. Nós temos nossa tabela base, nós temos nossas sub-tabelas e a herança nos permite trabalhar com todas as entidades diferentes da mesma forma, mas também para tratar cada uma delas de forma especifica como necessário.

Modelando Restrições Um-para-Muitos

No entanto, o que acontece se desejarmos configurar nosso modelo para que uma Pessoa possa ser somente de um tipo, por exemplo, um Estudante OU um Professor OU um Pai? Isto é uma implementação mais correta de herança de um banco de dados, pois em POO você não pode criar uma instância de algo que é mais que uma sub-classe ao mesmo tempo – você deve escolher uma ou outra (ou potencialmente a classe base em si, se for o que você deseja).

Modelar isso pode ser uma armadilha, e pode ser feito de diversas formas. Aqui será apresentada uma técnica que achamos interessante, pois ela não requer uma trigger, é fácil de ser implementada e usa integridade referencial padrão sem restrições de verificação complicadas.

Vamos lá: primeiro crie uma tabela de TiposPessoa (Listagem 3):

Listagem 3. Criando a tabela TiposPessoa.
create table TiposPessoa (TipoPessoaID int primary key, TipoPessoa varchar(10))

E vamos inserir alguns valores nesta tabela... um por “sub-tabela” (Listagem 4):

Listagem 4. Inserindo dados de exemplo na tabela TiposPessoa.
insert into TiposPessoa select 1,'Estudante' union all select 2,'Professor' union all select 3,'Pai'

Esta é a tabela que define os diferentes tipos de pessoa que o nosso sistema irá permitir. Existirá um registro nesta tabela para cada "sub-tabela" que criamos. Se você desejar permitir que uma pessoa seja apenas uma “Pessoa” genérica sem qualquer sub-tipo específico, nos podemos adicionar uma linha com o item “Outro”.

Com esta tabela definida, nós iremos agora adicionar a coluna TipoPessoa na nossa tabela Pessoas. Para este exemplo, vamos apenas definir uma coluna genérica Nome para a nossa tabela Pessoas:

Listagem 5. Criando a tabela Pessoas com o campo TipoPessoa.
create table Pessoas ( PessoaID int primary key, TipoPessoaID int references TipoPessoas(TipoPessoaID) not null, Nome varchar(10) not null )

Sendo assim, nós temos tipos diferentes de Pessoas que estamos modelando, e cada Pessoa deve ser associada a um tipo.

E agora vem a armadilha: nós iremos adicionar uma coluna extra TipoPessoa para cada uma de nossas sub-tabelas, e forçar que o valor nesta coluna seja igual ao valor da coluna TipoPessoaID na linha referente na tabela Pessoas. No SQL 2000, nós podemos simplesmente adicionar um valor padrão e uma restrição de verificação, mas no SQL 2005 nós podemos usar uma coluna calculada persistida. Então, nossa tabela Estudantes terá um valor de TipoPessoa constante igual a 1, a tabela Professores um valor igual a 2 e a tabela Pais um valor igual a 3.

Então, nós simplesmente criamos uma relação de chave estrangeira de volta para a tabela Pessoas – mais ao invés de criar a chave apenas na coluna PessoaID, nós criamos a restrição (chave estrangeira) nas colunas PessoaID e TipoPessoaID. Com essas restrições definidas, nós garantimos que cada Pessoa terá apenas um tipo e que elas podem ter somente possuir dados na sub-tabela referente ao seu tipo.

No entanto, existe um porém: nós não podemos criar nossas sub-tabelas dessa forma, usando chaves estrangeiras; se tentarmos, receberemos a mensagem de erro apresentada na Listagem 6.

Listagem 6. Tentativa de criar a tabela Estudantes com chave estrangeira.
create table Estudantes ( PessoaID int primary key, TipoPessoaID as 1 persisted, -- estudante DataMatricula datetime, foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID) )
Msg 1776, Level 16, State 0, Line 1

Não existem chaves primárias ou candidates na tabela referenciada 'Pessoas' que combine com a lista de colunas referenciadas na chave estrangeira 'FK__Students__1F98B2C1'.

Msg 1750, Level 16, State 0, Line 1

A restrição não pôde ser criada. Verifique os erros anteriores.

Isto ocorre porque a restrição de unicidade na tabela Pessoas é apenas na coluna PessoaID, mas estamos tentando configurar uma restrição de chave estrangeira na combinação das colunas PessoaID/TipoPessoaID para uma sub-tabela. Para resolver isso, nós simplesmente adicionamos uma restrição de unicidade adicional para a tabela Pessoas, cobrindo ambas as colunas PessoaID e TipoPessoaID (ver Listagem 7).

Listagem 7. Adicionando uma restrição de unicidade na tabela Pessoas.
drop table Pessoas go create table Pessoas ( PessoaID int primary key, TipoPessoaID int references TipoPessoa(TipoPessoaID), Nome varchar(10) constraint People_AltPK unique (PessoaID,TipoPessoaID) )

Com isso definido, agora estamos aptos a criar a restrição de chave estrangeira nas sub-tabelas, como é apresentado na Listagem 8 (ver Nota 1).

Listagem 8. Criando as sub-tabelas com chave estrangeira.
create table Estudantes ( PessoaID int primary key, TipoPessoaID as 1 persisted, -- estudante DatMatricula datetime, foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID) ) create table Professores ( PessoaID int primary key, TipoPessoaID as 2 persisted, -- professor DataAdmissao datetime, foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID) ) create table Pais ( PessoaID int primary key, TipoPessoaID as 3 persisted, -- pais GrauDificuldade int, foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID) )
Command(s) completed successfully.
Nota 1. Uso da versão SQL Server 2000

Se você estiver usando o SQL Server 2000, ao invés de usar colunas computadas para as constantes TipoPessoaID, você deveria usar um valor padrão (default) e uma restrição de verificação, pois essa versão anão permite que colunas computadas sejam usadas em restrições de chaves estrangeiras:

create table Professores – Versão SQL 2000 ( PessoaID int primary key, TipoPessoaID int not null default 2 check (TipoPessoaID = 2), -- tipo professores DataAdmissao datetime, foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID) )

Agora iremos testar a solução que demos. Vamos criar uma nova Pessoa, chamada "Claudio", e vamos torná-lo um estudante. Primeiramente, iremos adicionar uma linha à tabela Pessoas, e então uma linha correspondente na tabela Estudantes (ver Listagem 9).

Listagem 9. Realizando Testes – criação de um novo estudante.
insert into Pessoas (PessoaID, TipoPessoaID, Nome) values (1,1,'Claudio') (1 row(s) affected) insert into Estudantes (PessoaID, DataMatricula) values (1,'2007-01-01') (1 row(s) affected)

Note que nós não podemos adicionar informações de professores a Claudio, pois ele é apenas um estudante (Listagem 10):

Listagem 10. Realizando Testes – tentativa de adicionar dados de professor a um estudante.
insert into Professores (PessoaID, DataAdmissao) values (1,'2007-01-01')
Msg 547, Level 16, State 0, Line 1

A declaração de INSERT conflitou com a restrição de FOREIGN KEY "FK__Teachers__31B762FC". O conflito ocorreu no banco de dados "Escola", tabela "dbo.Pessoas". A declaração foi concluída.

Além disso, é claro que não podemos mudar o tipo de pessoa que é Claudio de estudante para um professor, pois já existe uma linha na tabela Estudantes para ele (ver Listagem 11).

Listagem 11. Realizando Testes – tentativa de alterar o tipo de uma pessoa.
update Pessoas set TipoPessoaID=2 where PessoaID = 1
Msg 547, Level 16, State 0, Line 1

A declaração UPDATE conflitou com a restrição de REFERENCE "FK__Students__2645B050". O conflito ocorreu no banco de dados "Escola", tabela "dbo.Estudantes". A declaração foi concluída.

A única forma de mudar o tipo de pessoa que é Claudio é removendo primeiro a linha na tabela de Estudantes (ver Listagem 12):

Listagem 12. Realizando Testes – alterando o tipo de uma pessoa.
-- deletando atributos de estudante: delete from Estudantes where PessoaID=1 -- mudando o tipo de um estudante para um professor update Pessoas set TipoPessoaID=2 where PessoaID = 1 -- adicionando atributos de professor: insert into Professores (PessoaID, DataAdminissao) values (1,'2007-01-01')(1 row(s) affected) (1 row(s) affected) (1 row(s) affected)

E pronto, conseguimos o que queríamos! Uma restrição "um-para-muitos", onde cada linha em uma tabela possui uma linha relacionada em somente uma das tabelas possíveis, sem a necessidade de qualquer trigger ou qualquer programação T-SQL complicada para manter a integridade dos dados.

Conclusão

Uma vez que tudo foi definido, nós temos um simples modelo de dados com poucas tabelas e stored procedures, e nós estamos garantindo consistência quando estamos tratando com atributos e relacionamentos comuns de "Pessoas" no banco de dados. Nós podemos facilmente criar novos tipos de Pessoas simplesmente através da adição de uma linha na tabela TiposPessoa e então criar uma nova "sub-tabela". Assim que a nova sub-tabela é criada, este novo tipo imediatamente possui todos os atributos e relacionamentos de uma Pessoa – sem codificar qualquer coisa ou fazer quaisquer outras mudanças no banco de dados!

Artigos relacionados