Imponha a integridade dos dados com uma tabela recursiva

Eu tenho um esquema de banco de dados oracle 11g existente que funciona com um aplicativo da web. Estou planejando uma expansão para o aplicativo para que um serviço da Web possa fazer operações de dados no banco de dados. Como parte do planejamento, percebi que não há verificações de integridade de dados nas relações pai/filho, o que tornaria problemático permitir que outros aplicativos trabalhem com a tabela. Estou planejando fazer a validação no serviço da web, mas a melhor prática é ter validação no banco de dados e no serviço da web.

--the base lookup table has a table with text values that is not shown.
--Example Red, Green, 
CREATE TABLE PROPERTY
(
  ID                        NUMBER(9)           NOT NULL, --PRIMARY KEY
  TENANT_ID                 NUMBER(9)           NOT NULL
)
-- a property may or may not have a parent property. 
--Example "Weight" of an item is a child of the "Shipping Weight"
CREATE TABLE PROPERTY_DEPENDENCY  --PRIMARY KEY PROPERTY_ID,PROPERTY_TYPE_ID
(
  PROPERTY_ID               NUMBER(9)           NOT NULL,
  PARENT_PROPERTY_ID        NUMBER(9),
  PROPERTY_TYPE_ID          NUMBER(9)           NOT NULL,
  ACTIVE                    NUMBER(1)           NOT NULL
)
--examples "Item Colour", "Item Trim Colour","Shipping Weight", "Weight"
CREATE TABLE PROPERTY_TYPE
(
  ID                        NUMBER(9)           NOT NULL,  --PRIMARY KEY
  VALUE                     VARCHAR2(200 BYTE)  NOT NULL,
  PROPERTY_TYPE             NUMBER(10)          DEFAULT 1   NOT NULL
)

--and the table that you insert and update into
CREATE TABLE CASE_PROPERTY
(
  ID                        NUMBER(9)           NOT NULL, --PRIMARY KEY
  PARENT_ID                 NUMBER(9),          --constraint on PROPERTY
  CASE_ID                   NUMBER(9)           NOT NULL,--foreign key
  PROPERTY_ID               NUMBER(9),          --constraint on PROPERTY
  PROPERTY_TYPE_ID          NUMBER(9)           NOT NULL --constraint on PROPERTY_TYPE
)

Estes são os problemas que identifiquei:

• você pode inserir em CASE_PROPERTY e tornar uma propriedade seu próprio pai ou avô
• você pode inserir o PROPERTY_TYPE_ID errado para um PROPERTY_ID em CASE_PROPERTY
• você pode inserir em CASE_PROPERTY um PARENT_ID que não faz sentido para um PROPERTY_TYPE_ID

Posso adicionar uma restrição de verificação para que PARENT_ID <> PROPERTY_IDvocê não possa ser pai de si mesmo.

Edição 3: O problema real é que as tabelas não são normalizadas corretamente, o que é ótimo para relatórios, mas difícil para validação de dados. CASE_PROPERTY.PROPERTY_TYPE_IDdeve ser sempre igual ao valor em PROPERTY_DEPENDENCY.PROPERTY_TYPE_IDmas não sei como validar isso.

Existem outras maneiras além de gatilhos para impor a integridade dos dados CASE_PROPERTY?

Editar: vou montar um exemplo completo. Se eu adicionasse restrições de chave estrangeira PROPERTY_DEPENDENCY, verificaria se apenas propriedades com pais foram inseridas, mas seriam os pais corretos?

Editar 2: Aqui está um exemplo completo das inserções permitidas. As duas últimas inserções são exemplos de dados que são permitidos, mas não deveriam ser.

ALTER TABLE CASE_PROPERTY ADD  CONSTRAINT CASE_PROPERTY_R01  FOREIGN
KEY (PARENT_ID)  REFERENCES CASE_PROPERTY (ID)  ENABLE  VALIDATE

Insert into PROPERTY    (ID, TENANT_ID)  Values    (2, 1); 
Insert into PROPERTY    (ID, TENANT_ID)  Values    (3, 1); 
Insert into PROPERTY    (ID, TENANT_ID)  Values    (4, 1); 

Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (10, 'Colour', 2);    
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (11, 'Trim Colour', 1);    
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (12, 'Shipping Weight', 1); 
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (13, 'Weight', 3); 

Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (4, 3, 11); 
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (3, NULL, 10);
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (1, NULL, 12);    
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (2, 1, 13); 

--example of a property validated data insert

--item 201 with type 13 is the child of item 200 of type 12
Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (200, NULL, 3000, 1, 12); 
Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (201, 200, 3000, 2, 13); 

--bad data inserts

-- a property is parent to itself with an incorrect property_type_id 

Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (202, 202, 4000, 3, 10);  
--should be 202, null,4000,3,10 

--a property is inserted with a parent that is not allowed 

Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (203, 200, 4000, 2, 13);  
--parent property should be 1 not 2