Implementando 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”:

• Estudantes
• Professores
• Pais

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:

• EnderecosEstudantes
• EnderecosProfessores
• EnderecosPais
• NumeroTelefoneEstudantes
• NumeroTelefoneProfessores
• NumeroTelefonePais
• CorrespondenciaEstudante
• CorrespondenciaProfessor
• CorrespondenciaPai

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:

• Pessoa
• EndereçoPessoa
• NumeroTelefonePessoa
• CorrespondeciaPessoa

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.

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:

• Você não pode criar um Estudante/Professor/Pai até que você crie primeiramente a "Pessoa". Uma vez que a pessoa é criada, você pode agora adicionar uma linha relacionada para a apropriada tabela, dependendo do seu tipo.
• Nós ainda temos todos os nossos dados em um lugar, mas agora nós temos tabelas configuradas para armazenar atributos e relações específicos para esses tipos diferentes de Pessoas. Um simples JOIN da tabela Estudante para Pessoas nos dá o nome do estudante, suas informações de contato e outros dados.
• Este esquema permite que uma pessoa seja um estudante, professor e pai – ao mesmo tempo! Ele também permite que uma pessoa seja nenhum desses – você simplesmente não adiciona uma linha relacionada em nenhuma das sub-tabelas.
• Se nós queremos exibir uma coluna "Tipo" (Estudante, Professor, Pais) quando consultando a tabela Pessoas, nós devemos fazer um outer join para todas as 3 "sub-tabelas" e determinar se existe alguma linha combinando em alguma das “sub-tabelas”.

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

select s.*, p.*
from estudantes s inner join pessoas p on s.pessoaID = p.pessoaID

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

create table TiposPessoa (TipoPessoaID int primary key, TipoPessoa varchar(10))

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

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:

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.

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

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

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.

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)



)

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)

insert into Professores (PessoaID, DataAdmissao)
values (1,'2007-01-01')

Msg 547, Level 16, State 0, Line 1

update Pessoas set TipoPessoaID=2 where PessoaID = 1

Msg 547, Level 16, State 0, Line 1

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