Quais são algumas maneiras de implementar um relacionamento muitos-para-muitos em um data warehouse?

Question

kevinskio

Asked: 2012-09-14 07:03:58 +0800 CST2012-09-14 07:03:58 +0800 CST 2012-09-14 07:03:58 +0800 CST

Essa lógica de negócios pode ser aplicada por uma restrição de banco de dados condicional?

772

Estou tentando duplicar a lógica de negócios incorporada a um aplicativo da Web C# da intranet no banco de dados para que outros bancos de dados possam acessá-lo e trabalhar sob as mesmas regras. Essa "regra" parece difícil de implementar sem o uso de hacks.

CREATE TABLE CASE_STAGE
(
  ID                        NUMBER(9)           PRIMARY KEY NOT NULL, 
  STAGE_ID                  NUMBER(9)           NOT NULL,
  CASE_PHASE_ID             NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  END_REASON_ID             NUMBER(9),
  PREVIOUS_CASE_STAGE_ID    NUMBER(9),
  "CURRENT"                 NUMBER(1)           NOT NULL,
  DATE_CLOSED               TIMESTAMP(6)        DEFAULT NULL
);

e

CREATE TABLE CASE_RECOMMENDATION
(
  CASE_ID                   NUMBER(9)           NOT NULL,
  RECOMMENDATION_ID         NUMBER(9)           NOT NULL,
  "ORDER"                   NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  CASE_STAGE_ID             NUMBER(9)           NOT NULL
);

ALTER TABLE CASE_RECOMMENDATION ADD (
  CONSTRAINT SYS_C00000
 PRIMARY KEY
 (CASE_ID, RECOMMENDATION_ID));

A lógica de negócios pode ser resumida como

When Inserting into CASE_STAGE
If CASE_STAGE.STAGE_ID = 1646
THEN
 CASE_STAGE.PREVIOUS_STAGE_ID must be found in CASE_RECOMMENDATION.CASE_STAGE_ID

Essa lógica pode ser incorporada em uma restrição Check ou um gatilho feio é o único caminho?

Editar:

Para todos os valores de CASE_STAGE.STAGE_ID, o valor de PREVIOUS_STAGE_ID deve ser encontrado em CASE_STAGE.ID
O aplicativo não permite exclusões de CASE_RECOMMENDATION, uma vez que não é mais CURRENT (ou seja, quando o valor de CASE_STAGE.CURRENT é 0, este estágio é fechado e não pode mais ser alterado, quando = 1, este é o estágio, ou linha, que está ativo e pode ser alterado agora.)

Editar: usar todas as excelentes ideias e comentários aqui é uma solução funcional para esse problema

CREATE MATERIALIZED VIEW LOG ON CASE_STAGE
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON CASE_RECOMMENDATION
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW CASE_RECOMMENDATION_MV REFRESH FAST ON COMMIT AS
  SELECT
         cr.ROWID cr_rowid, --necessary for fast refresh
         cs.ROWID cs_rowid, --necessary for fast refresh
         cr.case_id,
         cs.stage_id,
         cr.recommendation_id
         cr.case_stage_id,
         cs.previous_case_stage_id
  FROM   CASE_RECOMMENDATION cr,
         case_stage cs
  WHERE  cs.previous_case_stage_id = cr.case_stage_id (+)
  AND CS.PREVIOUS_CASE_STAGE_ID IS NOT NULL
  AND EXTRACT (YEAR FROM CS.DATE_CREATED) > 2010 --covers non conforming legacy data
  AND CR.RECOMMENDATION_ID IS NULL
  AND cs.stage_id =1646;  
--this last line excludes everything but problem cases due to the outer join

ALTER TABLE CASE_RECOMMENDATION_MV ADD CONSTRAINT CASE_RECOMMENDATION_ck CHECK (
    (previous_case_stage_id IS NOT NULL AND case_stage_id IS NOT NULL)
);

Ao inserir um estágio 1646 usando pacotes existentes sem recomendação, o erro era

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated
ORA-06512: at line 49

Tarefa concluída! Não é o objetivo de uma visão materializada, mas é melhor do que um gatilho.

4 respostas

Voted

Vincent Malgrat · Answer 1 · 2012-09-14T08:11:04+08:00

Se CASE_RECOMMENDATION.CASE_STAGE_IDfor exclusivo, você pode usar a integridade referencial com uma coluna virtual (11g+) para torná-la condicional:

alter table CASE_RECOMMENDATION ADD CONSTRAINT unique_case_stage unique (CASE_STAGE_ID);

-- virtual column only defined when stage_id=1646
alter table CASE_STAGE add 
   (case_1646 as (case when stage_id=1646 then previous_case_stage_id end));

-- check that the virtual column is defined when stage_id=1646
alter table case_stage add 
    constraint chk_1646 check ( stage_id!=1646 or previous_case_stage_id is not null);

-- referential integrity
alter table case_stage add 
     constraint fk_1646 foreign key (case_1646) 
     references case_recommendation (case_stage_id);

Vamos checar:

SQL> insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null, default);

1 row(s) inserted.

SQL> insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);

1 row(s) inserted.

SQL> -- fails because previous_case_stage_id is null
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null, default);

ORA-02290: check constraint (VNZ_TEST3.CHK_1646) violated

SQL> -- fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null, default); 

ORA-02291: integrity constraint (VNZ_TEST3.FK_1646) violated - parent key not found

SQL> -- succeeds !
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null, default); 

1 row(s) inserted.

Chris Saxon · Answer 2 · 2012-09-17T06:26:30+08:00

Se você tiver restrições complexas que deseja aplicar "invisivelmente" no banco de dados, poderá fazê-lo criando uma visualização materializada e aplicando restrições a ela.

Nesse caso, você pode fazer isso usando uma junção externa MV CASE_RECOMMENDATION.CASE_STAGE_IDpara CASE_STAGE.PREVIOUS_CASE_STAGE_ID. Uma verificação deve ser feita para que nenhum deles seja nulo quando o CASE_STAGE.STAGE_ID = 1646, assim:

--necessary for fast refresh
create materialized view log on case_stage with rowid;
create materialized view log on case_recommendation with rowid;

create materialized view mv refresh fast on commit as 
  select 
         cr.rowid cr_rowid, --necessary for fast refresh
         cs.rowid cs_rowid, --necessary for fast refresh
         cr.case_id,
         cr.recommendation_id,
         case when cs.stage_id = 1646 then
           'Y'
         else
           'N'
         end do_chk,
         cr.case_stage_id,
         cs.previous_case_stage_id
  from   CASE_RECOMMENDATION cr, 
         case_stage cs
  where  cs.previous_case_stage_id = cr.case_stage_id (+);

alter table mv add constraint mv_ck check (
    (do_chk = 'Y' and previous_case_stage_id is not null and case_stage_id is not null )
    or
    (do_chk = 'N')
);

insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null);

insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);
commit;

insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null);

pro fails because previous_case_stage_id is null
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null); 

pro fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION'
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

pro succeeds !
insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null); 
commit;

pro we can't delete stuff from case recommendation now 
delete CASE_RECOMMENDATION;
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

A restrição de verificação no MV só será invocada quando for atualizada, portanto, para que isso funcione com sucesso, você precisa garantir que isso seja feito no COMMIT. Isso aumentará o processamento do tempo de confirmação, portanto, você precisará ter em mente o seguinte:

A menos que seus conjuntos de dados sejam trivialmente pequenos, o MV deve ser REFRESH FAST. Existem algumas restrições sobre como você constrói seu MV para permitir isso
Se você tiver várias inserções em uma transação, o erro só será lançado quando você confirmar, possivelmente dificultando a identificação da instrução incorreta
Se você tiver um alto nível de inserções simultâneas, isso pode levar a alguns problemas de simultaneidade

Como esta solução implementa as restrições na camada SQL, ela supera alguns dos problemas de simultaneidade discutidos na solução procedural.

ATUALIZAR

Conforme apontado por Vincent, o tamanho do MV pode ser reduzido incluindo apenas as linhas com stage_id = 1646. Pode ser possível reescrever a consulta para não consumir nenhuma linha, mas não consigo pensar em como fazer isso direito agora:

create materialized view mv refresh fast on commit as 
  select 
         cr.rowid cr_rowid, --necessary for fast refresh
         cs.rowid cs_rowid, --necessary for fast refresh
         cr.case_id,
         cr.recommendation_id,
         cr.case_stage_id,
         cs.previous_case_stage_id
  from   CASE_RECOMMENDATION cr, 
         case_stage cs
  where  cs.previous_case_stage_id = cr.case_stage_id (+)
  and    cs.stage_id = 1646;

alter table mv add constraint mv_ck check (
    (previous_case_stage_id is not null and case_stage_id is not null)
);

Leigh Riffel · Answer 3 · 2012-09-14T11:36:26+08:00

Colocar a lógica de negócios no banco de dados é admirável e você certamente deve implementar restrições para coisas como essa quando puder. No entanto, um gatilho não é a única alternativa. Você pode resolver o problema no pacote PL/SQL fazendo o insert. Ao fazer isso, você obtém outros benefícios, como código do lado do cliente reduzido, menos trocas de contexto, vinculações automáticas, independência do aplicativo cliente, etc. Aqui está um exemplo incompleto (sem o bloqueio que seria necessário para executar isso simultaneamente).

CREATE OR REPLACE PACKAGE CaseStage As

Procedure InsertCaseStage (
   pId                      In Case_Stage.Id%Type,
   pStage_Id                In Case_Stage.Stage_Id%Type,
   pDate_Created            In Case_Stage.Date_Created%Type DEFAULT Current_Timestamp,
   pEnd_Reason_Id           In Case_Stage.End_Reason_Id%Type,
   pPrevious_Case_Stage_Id  In Case_Stage.Previous_Case_Stage_Id%Type,
   pCurrent                 In Case_Stage."CURRENT"%Type,
   pDate_Closed             In Case_Stage.Date_Closed%Type DEFAULT NULL
   );
END;
/

CREATE OR REPLACE PACKAGE BODY CaseStage As

Procedure InsertCaseStage (
   pId                      In Case_Stage.Id%Type,
   pStage_Id                In Case_Stage.Stage_Id%Type,
   pDate_Created            In Case_Stage.Date_Created%Type DEFAULT Current_Timestamp,
   pEnd_Reason_Id           In Case_Stage.End_Reason_Id%Type,
   pPrevious_Case_Stage_Id  In Case_Stage.Previous_Case_Stage_Id%Type,
   pCurrent                 In Case_Stage."CURRENT"%Type,
   pDate_Closed             In Case_Stage.Date_Closed%Type DEFAULT NULL
   ) 
As
   cPreviousCaseStageCheck Case_Stage.Stage_Id%Type := 1646;   
   vPreviousCount Number(1);
Begin
   If (pStage_Id = cPreviousCaseStageCheck) Then
      SELECT count(*) INTO vPreviousCount FROM Case_Recommendation 
      WHERE Case_Stage_Id = pPrevious_Case_Stage_Id
      AND rownum<=1;
      If (vPreviousCount <> 1) Then
         Raise_Application_Error(-20001,'Previous_Stage_Id must be found in '
            || 'Case_Recommendation. ' || pPrevious_Case_Stage_Id || ' was not.');
      End If;
   End If;

   /* INSERT... */
End;

END;
/

miracle173 · Answer 4 · 2012-09-18T01:57:48+08:00

miracle173

2012-09-18T01:57:48+08:002012-09-18T01:57:48+08:00

Estou perdendo uma tabela como CASE_RECOMMENDATION_LIST(CASE_STAGE_ID NUMBER(9) PRIMARY KEY) em seu design. Cada CASE_RECOMMENDATION é membro do correspondente CASE_RECOMMENDATION_LIST Isso pode ser tratado por uma chave estrangeira. Um CASE_RECOMMENDATION_LIST deve conter pelo menos um CASE_RECOMMENDATION. A criação e exclusão de um CASE_RECOMMENDATION_LIST podem ser tratadas por gatilhos simples: crie um CASE_RECOMMENDATION_LIST para um CASE_STAGE_ID antes que o primeiro CASE_RECOMMENDATION para este CASE_STAGE_ID seja criado, exclua-o após o último CASE_RECOMMENDATION para este CASE_STGE_ID ser excluído. CASE_STAGE faz referência a no máximo um CASE_RECOMMENDATION_LIST usando o CASE_STAGE.PREVIOUS_CASE_STAGE_ID. CASE_STAGE.PREVOUS_STAGE_ID não deve ser nulo se CASE_STAGE.STAGE_ID=1646.

Talvez seja uma maneira melhor de criar uma entidade própria (e, portanto, uma tabela) para os 1646 CASE_STAGEs, mas não vou analisar isso mais adiante.

0

Essa lógica de negócios pode ser aplicada por uma restrição de banco de dados condicional?

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Essa lógica de negócios pode ser aplicada por uma restrição de banco de dados condicional?

4 respostas

relate perguntas