Início / dba / Perguntas / 17309
Accepted
Olivier Pons
Asked: 2012-05-01 06:32:12 +0800 CST

Projeto de banco de dados: como lidar com o problema de "arquivo"?

  • 772

Tenho certeza de que muitos aplicativos, aplicativos críticos, bancos e assim por diante fazem isso diariamente.

A ideia por trás de tudo isso é:

  • todas as linhas devem ter um histórico
  • todos os links devem permanecer coerentes
  • deve ser fácil fazer solicitações para obter colunas "atuais"
  • os clientes que compraram coisas obsoletas ainda devem ver o que compraram, mesmo que este produto não faça mais parte do catálogo

e assim por diante.

Aqui está o que eu quero fazer e explicarei os problemas que estou enfrentando.

Todas as minhas tabelas terão essas colunas:

  • id
  • id_origin
  • date of creation
  • start date of validity
  • start end of validity

E aqui estão as ideias para operações CRUD:

  • create = inserir nova linha com id_origin= id, date of creation=now, start date of validity=now, end date of validity=null (= significa que é o registro ativo atual)
  • atualizar =
    • read = ler todos os registros com end date of validity==null
    • atualize o registro "atual" end date of validity=nulo com end date of validity=agora
    • crie um novo com os novos valores e end date of validity=null (= significa que é o registro ativo atual)
  • delete = atualize o registro "atual" end date of validity=null com end date of validity=agora

Então aqui está o meu problema: com associações muitos-para-muitos. Vamos dar um exemplo com valores:

  • Tabela A (id = 1, id_origin = 1, início=agora, fim=nulo)
  • Tabela A_B (início=agora, fim=nulo, id_A = 1, id_B = 48)
  • Tabela B (id = 48, id_origin = 48, início=agora, fim=nulo)

Agora eu quero atualizar a tabela A, registro id=1

  • Eu marco id de registro=1 com end=agora

  • Eu insiro um novo valor na tabela A e... caramba, perdi minha relação A_B , a menos que eu duplique a relação também... isso terminaria em uma tabela:

  • Tabela A (id = 1, id_origin = 1, início=agora, fim=agora+8mn)

  • Tabela A (id = 2, id_origin = 1, start=now+8mn, end=null)
  • Tabela A_B (início=agora, fim=nulo, id_A = 1, id_B = 48)
  • Tabela A_B (início=agora, fim=nulo, id_A = 2, id_B = 48)
  • Tabela B (id = 48, id_origin = 48, início=agora, fim=nulo)

E... bem, eu tenho outro problema: a relação A_B: devo marcar (id_A = 1, id_B = 48) como obsoleto ou não (A - id=1 é obsoleto, mas não B - 48)?

Como lidar com isso?

Tenho que projetar isso em grande escala: produtos, parceiros e assim por diante.

Qual é a sua experiência sobre isso? Como você faria (como você fez)?

-- Editar

Eu encontrei este artigo muito interessante , mas não lida adequadamente com "obsolescência em cascata" (= o que estou perguntando na verdade)

database-design

4 respostas

  1. Best Answer

    Não está claro para mim se esses requisitos são para fins de auditoria ou apenas uma simples referência histórica, como CRM e carrinhos de compras.

    De qualquer forma, considere ter uma tabela main e main_archive para cada área principal onde isso é necessário. "Main" terá apenas entradas atuais/ativas, enquanto "main_archive" terá uma cópia de tudo que for para main. Inserir/atualizar em main_archive pode ser um gatilho de inserir/atualizar em main. As exclusões em main_archive podem ser executadas por um período de tempo mais longo, se alguma vez.

    Para os problemas referenciais, como o Cliente X comprou o Produto Y, a maneira mais fácil de resolver sua preocupação referencial de cust_archive -> product_archive é nunca excluir entradas de product_archive. Geralmente, o churn deve ser muito menor nessa tabela, então o tamanho não deve ser uma preocupação tão ruim.

    HTH.

    • 4

  2. Isso tem alguma sobreposição com a programação funcional; especificamente o conceito de imutabilidade.

    Você tem uma mesa chamada PRODUCTe outra chamada PRODUCTVERSIONou similar. Quando você altera um produto, não faz uma atualização, apenas insere uma nova PRODUCTVERSIONlinha. Para obter o mais recente, você pode indexar a tabela por número de versão (desc), registro de data e hora (desc) ou pode ter um sinalizador ( LatestVersion).

    Agora, se você tiver algo que faça referência a um produto, poderá decidir para qual tabela ele aponta. Aponta para a PRODUCTentidade (refere-se sempre a este produto) ou para a PRODUCTVERSIONentidade (refere-se apenas a esta versão do produto)?

    Fica complicado. E se você tiver fotos do produto? Eles precisam apontar para a tabela de versão, porque podem ser alterados, mas em muitos casos não o farão e você não deseja duplicar dados desnecessariamente. Isso significa que você precisa de uma PICTUREtabela e um PRODUCTVERSIONPICTURErelacionamento muitos-para-muitos.

    • 2

  3. Eu implementei todas as coisas daqui com 4 campos que estão em todas as minhas tabelas:

    • Eu iria
    • data_criação
    • date_validity_start
    • date_validity_end

    Cada vez que um registro precisa ser modificado, eu o duplico, marco o registro duplicado como "antigo" = date_validity_end=NOW()e o atual como bom date_validity_start=NOW()e date_validity_end=NULL.

    O truque é sobre as relações muitos para muitos e um para muitos: funciona sem tocá-las! É tudo sobre as consultas que são mais complexas: para consultar um registro em uma data precisa (= não agora), tenho para cada junção e para a tabela principal, para adicionar essas restrições:

    WHERE (
  (date_validity_start<=:dateparam AND date_validity_end IS NULL)
  OR
  (date_validity_start<=:dateparam AND date_validity_start>=:dateparam)
)

    Assim, com produtos e atributos (relação muitos para muitos):

    SELECT p.*,a.*

FROM products p

JOIN products_attributes pa
ON pa.id_product = p.id
AND (
  (pa.date_validity_start<=:dateparam AND pa.date_validity_end IS NULL)
  OR
  (pa.date_validity_start<=:dateparam AND pa.date_validity_start>=:dateparam)
)

JOIN attributes a
ON a.id = pa.id_attribute
AND (
  (a.date_validity_start<=:dateparam AND a.date_validity_end IS NULL)
  OR
  (a.date_validity_start<=:dateparam AND a.date_validity_start>=:dateparam)
)

WHERE (
  (p.date_validity_start<=:dateparam AND p.date_validity_end IS NULL)
  OR
  (p.date_validity_start<=:dateparam AND p.date_validity_start>=:dateparam)
)
    • 1

  4. Que tal agora? Parece simples e bastante eficaz para o que fiz no passado. Em sua tabela de "histórico", use um PK diferente. Portanto, seu campo "CustomerID" é o PK em sua tabela Customer, mas na tabela "history", seu PK é "NewCustomerID". "CustomerID" se torna apenas outro campo somente leitura. Isso deixa "CustomerID" inalterado no histórico e todos os seus relacionamentos permanecem intactos.

    • 0

relate perguntas