Imagine que eu tenho uma tabela events, da qual outdoor_eventse indoor_eventsherdamos. Eu tenho activitiesentão outdoor_activitiese indoor_activities. Suponha que faça sentido que ambientes externos e internos sejam estruturas totalmente diferentes, embora eu esteja interessado em formas alternativas de modelá-las além dessa herança.
Se eu quiser preservar a propriedade de que todas as atividades estão vinculadas a um evento, o método mais direto parece ser usar uma chave estrangeira activitiesem events.
Se eu quiser restringir atividades ao ar livre para eventos ao ar livre, e o mesmo para ambientes internos, faz mais sentido colocar uma chave estrangeira em cada um dos filhos. No entanto, o fato de que toda atividade deve ser correlacionada com um evento é deixado para os filhos da tabela implementá-lo.
Eu poderia fazer as duas coisas, mas isso em sua forma mais simples não é uma opção aceitável para mim porque não é normalizado e qual chave usar é ambígua – e se forem diferentes?
Para mim, a segunda opção faz mais sentido, porque soa mais forte e satisfará ambas as condições com uma implementação adequada.
O que posso fazer para garantir que essas duas propriedades sejam satisfeitas? Ou essa estrutura é falha em princípio?
Observação: não acredito que isso seja uma duplicata de Usando a mesma tabela para entidades... porque essa pergunta não aborda o emparelhamento de tabelas herdadas semelhantes.
Com a herança de tabela, muitas vezes há um "tipo" na entidade raiz mostrando qual entidade derivada é preenchida para esta chave. Para este exemplo seria algo como events.event_type com valores "indoor" e "outdoor". As atividades seriam da mesma forma.
Expanda a chave estrangeira (e a chave primária correspondente na tabela referenciada) para incluir este tipo. Torne as colunas de chave estrangeira NOT NULL e o primeiro objetivo será alcançado.
Você diz "Eu quero restringir" e sua escolha de frase é apropriada. A maioria dos DBMS oferece suporte a restrições de tabela nas quais é possível declarar condições booleanas que o sistema aplicará quando os dados forem gravados. Neste caso será algo como
activity.activity_type == activity.event_type_fk. Agora, os tipos de atividade e de evento devem corresponder, ou seja, dentro para dentro ou fora para fora, satisfazendo o segundo requisito.