Início / dba / Perguntas / 34151
Accepted
cubetwo1729
Asked: 2013-02-06 07:12:55 +0800 CST

Como ter uma relação um-para-muitos com uma criança privilegiada?

  • 772

Quero ter um relacionamento um-para-muitos em que, para cada pai, um ou zero dos filhos seja marcado como "favorito". No entanto, nem todos os pais terão um filho. (Pense nos pais como perguntas neste site, filhos como respostas e favoritos como a resposta aceita.) Por exemplo,

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

A meu ver, posso adicionar a seguinte coluna à TabelaA:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

ou a seguinte coluna para TableB:

    IsFavorite    BIT NOT NULL

O problema com a primeira abordagem é que ela introduz uma chave estrangeira anulável, que, pelo que entendi, não está na forma normalizada. O problema com a segunda abordagem é que mais trabalho precisa ser feito para garantir que no máximo um filho seja o favorito.

Que tipo de critério devo usar para determinar qual abordagem usar? Ou existem outras abordagens que não estou considerando?

Estou usando o SQLServer 2012.

sql-server database-design

3 respostas

  1. Best Answer

    Outra forma (sem Nulos e sem ciclos nas FOREIGN KEYrelações) é ter uma terceira tabela para armazenar os "filhos favoritos". Na maioria dos DBMS, você precisará de uma UNIQUErestrição adicional em TableB.

    @Aaron foi mais rápido em identificar que a convenção de nomenclatura acima é bastante complicada e pode levar a erros. Geralmente é melhor (e irá mantê-lo são) se você não tiver Idcolunas em todas as suas tabelas e se as colunas (que são unidas) tiverem os mesmos nomes nas muitas tabelas que aparecem. Então, aqui está uma renomeação:

    Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

    No SQL-Server (que você está usando), você também tem a opção da IsFavoritecoluna de bits que você mencionou. O filho favorito exclusivo por pai pode ser obtido por meio de um índice exclusivo filtrado:

    Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

    E a principal razão pela qual sua opção 1 não é recomendada, pelo menos não no SQL-Server, é que o padrão de caminhos circulares nas referências de chaves estrangeiras apresenta alguns problemas.

    Leia um artigo bastante antigo: SQL By Design: The Circular Reference

    Ao inserir ou excluir linhas das duas tabelas, você se deparará com o problema do "ovo e galinha". Qual tabela devo inserir primeiro - sem violar nenhuma restrição?

    Para resolver isso, você deve definir pelo menos uma coluna anulável. (OK, tecnicamente você não precisa, você pode ter todas as colunas como, NOT NULLmas apenas em DBMS, como Postgres e Oracle, que implementaram restrições adiáveis. Veja a resposta de @Erwin em uma pergunta semelhante: Restrição complexa de chave estrangeira em SQLAlchemy sobre como isso pode ser feito no Postgres). Ainda assim, essa configuração parece patinar no gelo fino.

    Verifique também uma pergunta quase idêntica em SO (mas para MySQL) Em SQL, é possível que duas tabelas se refiram uma à outra? onde minha resposta é praticamente a mesma. O MySQL não possui índices parciais, portanto, as únicas opções viáveis ​​são o FK anulável e a solução de tabela extra.

    • 21

  2. Depende de qual é a sua prioridade. Quer evitar o trabalho ou quer aderir às mais rígidas regras de normalização?

    Pessoalmente, acho melhor ter IsFavoritena tabela filho e estaria disposto a trabalhar para garantir que no máximo um filho para cada pai seja o favorito desse pai. O principal motivo não tem nada a ver com a chave estrangeira anulável: não gosto nem um pouco da ideia de chaves estrangeiras apontando em ambas as direções.

    A sugestão de @ypercube também é um bom compromisso.

    Como um aparte, por favor, por favor, não encha seu esquema com nomes de colunas sem sentido como Id. Eu preferiria ver o Idser nomeado de forma significativa em todo o esquema. Identifica um autor? Ok, chame AuthorID. Representa um produto? Ok, ProductID. É um funcionário e, em alguns casos, faz referência a um gerente? Ok, EmployeeIDe ManagerIDfaz mais sentido para mim do que IDe Parent. Embora possa parecer lógico deixar isso de fora (e redundante para colocá-lo), quando você começar a escrever junções complexas (ou postar consultas aqui), você definitivamente sentirá alguns palavrões quando estiver tentando fazer engenharia reversa de um monte de junções naquele ponto para colunas como a.Parent = b.ID... blecch.

    • 11

  3. Os dados pertencem à tabela filho. Nós o mantemos correto com um gatilho na tabela para garantir que um único registro seja marcado como favorito (ou, no nosso caso, como o endereço preferencial).

    No entanto, a ideia de @ypercube de uma mesa separada também é boa.

    • 2

relate perguntas