How to guarantee integrity of data in a complex one-to-many relationship?

Suppose we have 4 tables as d开发者_运维知识库escribed below:

Table 1: Element  
element_id [integer] (PK)  
element_name [varchar]

Table 2: Element_Property  
property_id [integer] (PK)  
element_id [integer] (FK to Element.element_id)  
data_type [integer]

Table 3: Page_Elements  
page_element_id [integer] (PK)  
element_type_id [integer] (FK to Element.element_id)  
element_name [varchar]

Table 4: Page_Elements_Property_Values  
property_value_id [integer] (PK)  
page_element_id [integer] (FK to Page_Elements.page_element_id)  
property_id [integer] (FK to Element_Property.property_id)  
value [varchar]

The first two tables are definition tables for elements and their values, while the third and fourth tables are instance tables.

Considering the relationship between tables described above: How to guarantee, by design, that each property-value in table 4 (Page_Elements_Property_Values) refers to a property in table 2 (Element_Property) owned by the element type in table 3 (Page_Elements)?

I will give an example to explain it more:

'TextBox' is an element, 'Length' is a property of 'TextBox'. 'Height' is property of some other element. 'MyTextBox' is a page element of type 'TextBox', now... How to guarantee that in table 4 (Page_Elements_Property_Values) we will not have a value of 'Height' referring to 'MyTextBox'?

The real database structure is a little more complex and has some many-to-many relationships, but the idea here is enough to describe the problem.

Any help or guidance is appreciated!

---

The only good way to handle these higher-order integrity is through triggers or exception reporting. If you can make a query to check for the problem, you can almost certainly turn that into a trigger.

---

This is the sort of problem that in-database referential integrity is not suited to handle. RI in the schema definition can enforce direct relationships between entities but it can't do much about consistency of indirect relationships between other records (and their relationships) in the same table.

The way to handle this type of requirement is to use before insert and before update triggers to test the consistency of the record that is about to be written with other records under the same parent. If you try to add a property to a text box instance that doesn't apply to text boxes, then your trigger will reject the update/insert and raise an error.

---

Well, not an easy task. However, it may help if you were to introduce a list of allowed property values for each element type -- a catalogue. Then, propagate ElementTypeID and PropertyID into the table of actual properties. Something like

You may also consider having separate tables for string, integer and numeric properties to enforce data-type for each one.