Pode ser que meu projeto inicial seja falho, mas vamos começar a partir daí e ver se há uma abordagem boa o suficiente.
Eu tenho duas entidades, digamos, Ae Bque são muito semelhantes, mas não idênticas, então os conjuntos de colunas são diferentes. Por esses motivos, existem duas tabelas distintas, Ae B.
Dito isso, gostaria de criar outra tabela que descreva ambos Ae Bcom um conjunto de campos aplicáveis a Ae B. Vamos chamar esta tabela entity_settings.
Seu esquema não é intuitivo para descobrir:
Minha primeira ideia é usar algum tipo de
entity_typesinalizador eentity_idde A ou B dependendo do tipo com uma restrição UNIQUE em(entity_type, entity_id). Tal definição torna impossível ter uma restrição externa normal porque elaentity_idconsiste em ids de duas tabelas.Outra opção é ter
A_ideB_idque pode ser nulo mas referenciar os campos de ID das tabelas correspondentes. Isso faz sentido e fornece integridade referencial, mas as inserções nesta tabela serão bastante desajeitadas.A única opção que estou considerando também é ter duas tabelas separadas para
AeBpara que cada tabela tenha seu próprio filho. Isso parece um pouco inútil em termos de número de tabelas, mas é provavelmente a opção mais limpa em termos de design.
Como quase sempre, tenho a sensação de que está faltando algo e agradeceria muito se alguém pudesse sugerir outras opções.
EDITAR:
Na segunda opção, também faz sentido adicionar uma restrição CHECK comoCHECK((A_id IS NOT NULL AND B_id IS NULL)) OR (A_id IS NULL AND B IS NOT NULL))
Você não está perdendo nada, este é um problema difícil conhecido em bancos de dados relacionais. O conceito não se presta a modelos como esse naturalmente.
Sua solução 2. é boa, com a restrição de verificação.
A solução 3. também é possível. Talvez você possa usar o particionamento, para que possa ter uma única tabela particionada
entity_settingse as partições tenham chaves estrangeiras paraAeB. Se isso não causar problemas com suas consultas ou outras partes do sistema, é uma solução decente.