Enforce data integrity with a recursive table

Question

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

• Vincent Malgrat: I'm not sure I understand your model, especially regarding PK and FK between the 3 first tables. You might want to add an example with some typical data. Anyway, why not have your case_property table reference property_dependency?
• Vincent Malgrat: It's still cryptic, sorry. Anyway, I would strongly suggest not using triggers, use procedures instead. It is not possible to implement multi-row integrity constraints in a multi-user environment with triggers.

Answer 1

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

• kevinskio: This looks very interesting! I will try it out Monday and report back to see if this answers the question.
• kevinskio: I'm not able to implement all of your solution as it requires changing existing constraints but I can adapt your solution to solve the problem. Thanks!