Enforce data integrity with a recursive table

database-designoracleoracle-11g-r2

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the planning I have realized that there are no data integrity checks on parent/child relationships which would make it problematic to let other applications work with the table. I am planning to do validation in the web service but the best practice is to have validation in the database and the web service.

--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
)

These are the problems I have identified:

  • you can insert into CASE_PROPERTY and make a property it's own parent or grandparent
  • you can insert the wrong PROPERTY_TYPE_ID for a PROPERTY_ID into CASE_PROPERTY
  • you can insert into CASE_PROPERTY a PARENT_ID which makes no sense for a PROPERTY_TYPE_ID

I can add a check constraint so that PARENT_ID <> PROPERTY_ID so you cannot be a parent to yourself.

Edit 3:
The real problem is that the tables are not normalized properly which is great for reporting but hard on data validation. CASE_PROPERTY.PROPERTY_TYPE_ID should always be the same as the value in PROPERTY_DEPENDENCY.PROPERTY_TYPE_ID but I don't know how to validate this.

Are there any ways other than triggers to enforce data integrity on CASE_PROPERTY?

Edit: I'll put together a complete example. If I added foreign key constraints on PROPERTY_DEPENDENCY I would verify that only properties with parents were inserted but would they be the correct parents?

Edit 2: Here is a complete example of the inserts that are allowed. The last two inserts are examples of data that is allowed but should not be.

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

Best Answer

Do not know if this will be useful to you because it requires quite a few changes, but the problem is interesting, so I'll try.

These would be the major changes

  • Using the tree closure instead of the adjacency list for the reference hierarchy. The closure table contains paths form each parent to all of it descendants, so all possible parent-child combinations are exposed.

Note that with the tree closure, each ancestor node points to itself as a descendant, meaning that in CaseProperty recursion stops on ID = ParentID instead on ParentID is NULL

It is not clear to me is a parent allowed to be any ancestor or just the one first step up. The closure table exposes ancestor and all descendants, so Level Difference is added to the TreeClosure, which is sub-typed as AllowedCombos for LevelDifference in (0,1).

  • Propagating AK {PropertyID, PropertyTypeID} instead of just PropertyID

  • Using composite key in CaseProperty

enter image description here

Here are main constraints from the model to clarify relationships (you may need to modify syntax)

ALTER TABLE Property ADD
  CONSTRAINT PK_PR  PRIMARY KEY (PropertyID)

, CONSTRAINT AK1_PR UNIQUE (PropertyID ,PropertyTypeID)

, CONSTRAINT FK1_PR FOREIGN KEY (PropertyTypeID)
         REFERENCES PropertyType(PropertyTypeID)
;



ALTER TABLE TreeClosure ADD
  CONSTRAINT PK_TC PRIMARY KEY (AncestorID ,DescendantID ,AncestorTypeID ,DescendantTypeID)

, CONSTRAINT FK1_TC FOREIGN KEY (AncestorID ,AncestorTypeID) 
             REFERENCES Property(PropertyID ,PropertyTypeID)

, CONSTRAINT FK2_TC FOREIGN KEY (DescendantID ,DescendantTypeID)
             REFERENCES Property(PropertyID   ,PropertyTypeID)
;



ALTER TABLE CaseProperty ADD
  CONSTRAINT PK_CP PRIMARY KEY (CaseID, PropertyID, PropertyTypeID)

, CONSTRAINT FK1_CP FOREIGN KEY (CaseID)
                 REFERENCES Case(CaseID)

, CONSTRAINT FK2_CP FOREIGN KEY (PropertyID ,PropertyTypeID)
             REFERENCES Property(PropertyID ,PropertyTypeID)

, CONSTRAINT FK4_CP FOREIGN KEY (ParentCaseID ,ParentPropertyID ,ParentPropertyTypeID)
         REFERENCES CaseProperty(CaseID       ,PropertyID       ,PropertyTypeID)

, CONSTRAINT FK5_CP FOREIGN KEY (ParentPropertyID ,PropertyID   , ParentPropertyTypeID ,PropertyTypeID) 
        REFERENCES AllowedCombos(AncestorID       ,DescendantID , AncestorTypeID       ,DescendantTypeID)

;
Related Question