none
relacionamento muitos para muitos- é necessário criar primary que?

    Question

  • Olá
    boa tarde a todos
    num relacionamento muito para muitosm,  no sqlServer, implementamos com terceira tabela, vejamemos um cenário de exemplo: tabela tabA (PK tabA_ID)  e tabB (PK tabB_ID) e tabela de relacuionamento tabAB  (tabA_ID,tabB_ID), aqui a dúvida: como implmentaria o PRIMARY KEY desta tabela tabAB?, pensei em algumas alternativas:
    PRIMARY KEY COMPOSTO:
    tabA_ID + tabB_ID  com foreign  key para B_id
    ou
    tabB_ID + tabA_ID com foreing key para A_ID
    ou só defino constrain foreign key para tabA_ID e tabB_ID, sem definir PK para esta tabela.
    ou ainda, defino dois indices, um para tabA_ID e outro para tabB_ID
    bem, qual a melhor solução ou a melhor prática praticada pela comunidade experiente?
    grato
    takeo

    Tuesday, August 12, 2008 6:29 PM

Answers

  • Boa Noite Takeo,

     

    Como uma dúvida simples pode levar a tantas possibilidades passíveis de discussão... Vamos às explicações e ao meu ponto de vista. Vou utilizar para o meu exemplo o clássico relacionamento entre pedidos e produtos. Ex:

     

    Um pedido tem 1 ou vários produtos

    Um produto está em 1 ou vários pedidos

     

    As afirmações acima caracterizam um relacionamento N x M entre pedidos e produtos e naturalmente (utilizando uma tecnologia relacional) teremos de recorrer a tabela associativa para tornar fisicamente possível esse relacionamento. Isso não é válido para apenas o SQL Server, mas para qualquer implementação relacional. Assim sendo podemos ter as seguintes tabelas:

    • Pedido (IDPedido, DataPedido, <Outros Atributos de Pedidos>)
    • Produto (IDProduto, PreçoProduto, <Outros Atributos de Produto>)
    • ItemPedido (IDPedido, IDProduto, <Outros Atributos Pertinentes>)

    Como o Jr. bem disse, é uma prática recomendada para a esmagadora maioria dos casos, a presença de uma chave primária. As tabelas Pedido e Produto já possuem chaves primárias definidas, mas e qual seria a chave primária de ItemPedido ?

     

    Inevitavelmente IDPedido e IDProduto já serão FKs para as tabelas de Pedidos e Produtos respectivamente. É através dessa FK que poderemos mapear que produtos estão em qual pedidos e vice-versa.

     

    Se colocarmos apenas IDPedido como chave primária iremos causar uma grave limitação em nosso modelo. Se IDPedido for chave e a chave não se repete, poderemos ter apenas um Item por pedido o que negocialmente não é aceitável (afinal definimos uma situação N x M entre pedidos e produtos). Pela mesma razão IDProduto não pode ser chave.

     

    A tendência natural (e possivelmente a mais correta) é colocar uma chave primária composta pelos campos IDPedido e IDProduto. Isso permite que o IDPedido se repita múltiplas vezes mas sem repetir o mesmo produto. Da mesma forma o IDProduto pode se repetir múltiplas vezes desde que o IDPedido não se repita. Isso nos leva a crer que muitos pedidos são feitos contendo o mesmo produto e que um mesmo pedido pode conter vários produtos, mas nunca teremos o mesmo produto no mesmo pedido.

     

    Concordo que tal implementação é possivelmente a mais indicada e tida como a única por diversos projetistas, AD e implementadores. Quanto a ordem (colocar IDProduto na frente ou IDPedido na frente) é uma questão meramente física já que logicamente não há diferença. Para garantir um bom desempenho, é preciso escolher primeiro a coluna mais seletiva (possivelmente IDPedido).

     

    Uma outra possibilidade seria fazer o seguinte:

    • ItemPedido (IDItemPedido, IDPedido, IDProduto, <Outros Atributos Pertinentes>)

    Nesse caso as FKs para Pedido e Produto permanecem (não podemos abrir mão delas), mas criaríamos uma chave artificial para identificar o ItemPedido. Colocar uma coluna como chave primária economiza espaço, pois, o índice seria com base em uma única coluna e não com base em duas.

     

    O primeiro questionamento básico dessa implementação é a garantia da regra de negócio. Se IDPedido e IDProduto não são chave primária então eles podem se repetir livremente e temos margem a que um produto se repita várias vezes para o mesmo produto. Nesse caso acabaríamos criando uma constraint (ou um índice Unique) para garantir que a combinação IDPedido e IDProduto seja única (não podemos colocar como chave primária se IDItemPedido já é uma chave primária).

     

    Só que criar um índice ou uma coluna Unique sobre essas chaves já mata o benefício do espaço anteriormente levantado e fatalmente essa implementação seria questionada. Há ainda um segundo motivo que justificaria essa implementação. Imagine que a cada Item no Pedido você queria controlar variações no estoque. Então talvez você tivesse uma quarta tabela como a tabela abaixo:

    • VariacaoEstoque (IDItemPedido, ValorEstoque)

    Para cada produto que fosse incluído em um pedido, você faria uma auditoria para saber como ficou o estoque daquele produto após o produto ter sido incluído em determinado pedido, nesse caso é preciso que IDItemPedido seja repassado a essa tabela como FK. Se a PK de ItemPedido fosse composta teríamos a seguinte implementação

    • VariacaoEstoque (IDPedido, IDProduto, ValorEstoque)

    Nesse caso, os registros em VariacaoEstoque seria mais largos, pois, agora temos uma coluna a mais e talvez a chave artificial justificasse a implementação.

     

    Apresentei duas alternativas (uma bem usual e outra nem tanto), mas não posso afirmar categoricamente qual seria melhor. A partir do momento que você garante alguns benefícios no seu modelo de dados como eliminação da redundância, desempenho e facilidade na elaboração das consultas qualquer alternativa é potencialmente válida. Manter a chave simples poderá dar-lhe economias de espaço em diversas situações, mas os JOINs podem ficar um pouco mais custosos.

     

    Como o desempenho das consultas muitas vezes é primordial, manter a chave composta da tabela associativa pode ser mais vantajoso (e usualmente é assim que acontece).

     

    [ ]s,

     

    Gustavo

    Wednesday, August 13, 2008 12:36 AM
    Moderator

All replies

  • Takeo,

     

    É recomendado que todo table, possua uma primary key, por questões de organização dos dados e principalmente para facilitar a busca das informações.

     

    Eu particularmente criar a table com uma primary key, e utilizaria para cada table que esta sendo relacionada, uma foreign key especifica para cada table.

    Tuesday, August 12, 2008 6:52 PM


  •  Bom takeo,

    No meu caso faria assim

    Um exemplo real:

     Tabela: Produtos 
     Tabela: Familias
     Tabela: Departamentos


    Produtos: ProdID(PK), ProdName, ProdFamID(FK), ProdDepID(FK)  => Relationships: Uma Familia pode conter N's Produtos
    Familias: FamID(PK), FamName : ==>  Relationships: Um Produto só pode fazer parte de uma familia
    Departamentos: DepID(PK), DepName  ==> Relationships: Um produto so pode fazer parte de um departamento.



    Trabalho assim e funciona legal.
        









    Tuesday, August 12, 2008 9:20 PM
  • Boa Noite Takeo,

     

    Como uma dúvida simples pode levar a tantas possibilidades passíveis de discussão... Vamos às explicações e ao meu ponto de vista. Vou utilizar para o meu exemplo o clássico relacionamento entre pedidos e produtos. Ex:

     

    Um pedido tem 1 ou vários produtos

    Um produto está em 1 ou vários pedidos

     

    As afirmações acima caracterizam um relacionamento N x M entre pedidos e produtos e naturalmente (utilizando uma tecnologia relacional) teremos de recorrer a tabela associativa para tornar fisicamente possível esse relacionamento. Isso não é válido para apenas o SQL Server, mas para qualquer implementação relacional. Assim sendo podemos ter as seguintes tabelas:

    • Pedido (IDPedido, DataPedido, <Outros Atributos de Pedidos>)
    • Produto (IDProduto, PreçoProduto, <Outros Atributos de Produto>)
    • ItemPedido (IDPedido, IDProduto, <Outros Atributos Pertinentes>)

    Como o Jr. bem disse, é uma prática recomendada para a esmagadora maioria dos casos, a presença de uma chave primária. As tabelas Pedido e Produto já possuem chaves primárias definidas, mas e qual seria a chave primária de ItemPedido ?

     

    Inevitavelmente IDPedido e IDProduto já serão FKs para as tabelas de Pedidos e Produtos respectivamente. É através dessa FK que poderemos mapear que produtos estão em qual pedidos e vice-versa.

     

    Se colocarmos apenas IDPedido como chave primária iremos causar uma grave limitação em nosso modelo. Se IDPedido for chave e a chave não se repete, poderemos ter apenas um Item por pedido o que negocialmente não é aceitável (afinal definimos uma situação N x M entre pedidos e produtos). Pela mesma razão IDProduto não pode ser chave.

     

    A tendência natural (e possivelmente a mais correta) é colocar uma chave primária composta pelos campos IDPedido e IDProduto. Isso permite que o IDPedido se repita múltiplas vezes mas sem repetir o mesmo produto. Da mesma forma o IDProduto pode se repetir múltiplas vezes desde que o IDPedido não se repita. Isso nos leva a crer que muitos pedidos são feitos contendo o mesmo produto e que um mesmo pedido pode conter vários produtos, mas nunca teremos o mesmo produto no mesmo pedido.

     

    Concordo que tal implementação é possivelmente a mais indicada e tida como a única por diversos projetistas, AD e implementadores. Quanto a ordem (colocar IDProduto na frente ou IDPedido na frente) é uma questão meramente física já que logicamente não há diferença. Para garantir um bom desempenho, é preciso escolher primeiro a coluna mais seletiva (possivelmente IDPedido).

     

    Uma outra possibilidade seria fazer o seguinte:

    • ItemPedido (IDItemPedido, IDPedido, IDProduto, <Outros Atributos Pertinentes>)

    Nesse caso as FKs para Pedido e Produto permanecem (não podemos abrir mão delas), mas criaríamos uma chave artificial para identificar o ItemPedido. Colocar uma coluna como chave primária economiza espaço, pois, o índice seria com base em uma única coluna e não com base em duas.

     

    O primeiro questionamento básico dessa implementação é a garantia da regra de negócio. Se IDPedido e IDProduto não são chave primária então eles podem se repetir livremente e temos margem a que um produto se repita várias vezes para o mesmo produto. Nesse caso acabaríamos criando uma constraint (ou um índice Unique) para garantir que a combinação IDPedido e IDProduto seja única (não podemos colocar como chave primária se IDItemPedido já é uma chave primária).

     

    Só que criar um índice ou uma coluna Unique sobre essas chaves já mata o benefício do espaço anteriormente levantado e fatalmente essa implementação seria questionada. Há ainda um segundo motivo que justificaria essa implementação. Imagine que a cada Item no Pedido você queria controlar variações no estoque. Então talvez você tivesse uma quarta tabela como a tabela abaixo:

    • VariacaoEstoque (IDItemPedido, ValorEstoque)

    Para cada produto que fosse incluído em um pedido, você faria uma auditoria para saber como ficou o estoque daquele produto após o produto ter sido incluído em determinado pedido, nesse caso é preciso que IDItemPedido seja repassado a essa tabela como FK. Se a PK de ItemPedido fosse composta teríamos a seguinte implementação

    • VariacaoEstoque (IDPedido, IDProduto, ValorEstoque)

    Nesse caso, os registros em VariacaoEstoque seria mais largos, pois, agora temos uma coluna a mais e talvez a chave artificial justificasse a implementação.

     

    Apresentei duas alternativas (uma bem usual e outra nem tanto), mas não posso afirmar categoricamente qual seria melhor. A partir do momento que você garante alguns benefícios no seu modelo de dados como eliminação da redundância, desempenho e facilidade na elaboração das consultas qualquer alternativa é potencialmente válida. Manter a chave simples poderá dar-lhe economias de espaço em diversas situações, mas os JOINs podem ficar um pouco mais custosos.

     

    Como o desempenho das consultas muitas vezes é primordial, manter a chave composta da tabela associativa pode ser mais vantajoso (e usualmente é assim que acontece).

     

    [ ]s,

     

    Gustavo

    Wednesday, August 13, 2008 12:36 AM
    Moderator
  • Oi Gustavo,
    Sua exposição valeu como uma aula, com certeza ajudará muitas pessoas que acessarem o forum. Mas tem algums pontos que preciso de melhores esclarecimentos, vamos a ela: Onde você fala da criação de índice artifificial ,  vou rerproduzir o texto:

    Tongue Tiede IDPedido e IDProduto não são chave primária então eles podem se repetir livremente e temos margem a que um produto se repita várias vezes para o mesmo produto ( produto mesmo ou itemPedido).

    Em um outro texto que tambem reproduzo aqui:

    Nesse caso acabaríamos criando uma constraint (ou um índice Unique) para garantir que a combinação IDPedido e IDProduto seja única (não podemos colocar como chave primária se IDItemPedido já é uma chave primária).

    Não entendi bem este texto. Ao definirmos constraint unique tanto para coluna IDPedido e IDProduto, garante a unicidade da combinação (IDPedido + IDProduto)? ou ao criarmos o índice unique não teríamos que cria-lo composto (IDPedido + IDProduto)?

    abraço

    takeo  

    Wednesday, August 13, 2008 5:42 AM
  • Bom Dia Takeo,

     

    Sim. Se você colocar uma Constraint Unique para IDPedido e IDProduto você estará garantindo unicidade dessa combinação. Isso é útil se essa combinação não for a chave primária. Se você criar o índice Unique você terá o mesmo efeito. Na verdade ao criar a Constraint, fisicamente é criado um índice Unique.

     

    Recomendo optar pela Constraint ao invés do índice Unique. Se algum DBA precisar de espaço ou achar o índice inútil ele poderá dropá-lo. Se algum DBA ver uma constraint Unique ficará receoso, pois, usualmente elas são utilizadas para garantir alguma regra de negócio.

     

    [ ]s,

     

    Gustavo

    Wednesday, August 13, 2008 11:49 AM
    Moderator
  • Takeo,

     

    Após esta grande aula do Gustavo, realmente aprendemos muito.

     

    Mas o que eu gosto de dizer para os meus alunos de Universidade, é o seguinte:

     

    " Antes de querer modelar um banco de dados dentro de um SGBD(SQL Server), faça um desenho do Banco de dados, utilizando técnicas de Modelagem como Entidade Relacionamento, para que você possa visualizar melhor como o seu banco de dados poderá ser desenvolvido."

     

    "Procure respeitar, as regras de normalização para evitar redundância de informações, como também relacionamentos desnecessários".

    Wednesday, August 13, 2008 12:10 PM
  • Olá Gustavo / Junior e depais que responderam ao post
    Quero agradecer mais uma vez pela grande ajuda pelos esclarecimentos das minha dúvidas. Agora estou mais consiciente de como implementar um relacionamento n x m. Posso disser que esta ´dúvida vinha já há longo tempo martelando na minha consicência. Mas agora está bem mais clareada.
    um grande abraço a todos
    takeo

    Wednesday, August 13, 2008 6:19 PM
  • Olá Takeo,

     

    Que bom que os esclarescimentos foram valisosos. Quando puder classifique a resposta.

    Sua classificação colocará essa resposta como prioritária no mecanismo de busca podendo ajudar outros com a mesma dúvida.

     

    [ ]s,

     

    Gustavo

     

    Wednesday, August 13, 2008 6:44 PM
    Moderator
  • Takeo,

     

    Obrigado pelo retorno.

     

    Wednesday, August 13, 2008 7:00 PM