title: Relating two hierarchical self referencing tables
body: <p>I have a self referencing table, which represents hierarchical trees of attributes.  There can be multiple roots in the tree (or multiple trees, however you want to think about it):</p>
  
  <p><a href="https://i.stack.imgur.com/Bb5ft.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Bb5ft.png" alt="enter image description here"></a></p>
  
  <p>The table allows me to create definitions of hierarchies such as:</p>
  
  <p><a href="https://i.stack.imgur.com/aK6Dz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aK6Dz.png" alt="enter image description here"></a></p>
  
  <p>Now I need, for a given user, to capture values for attributes within the tree. A user can have multiple sets of attribute values and each attribute ancestor can also have multiple sets of values:</p>
  
  <p><a href="https://i.stack.imgur.com/cOdDc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cOdDc.png" alt="enter image description here"></a></p>
  
  <p>The table allows me to assign values to attributes within a hierarchy where there can be multiple instances of each attributes and their ancestors:</p>
  
  <p><a href="https://i.stack.imgur.com/LDs6D.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LDs6D.png" alt="enter image description here"></a></p>
  
  <p>So effectively, I now have two self referencing tables.  One is a definition of the attribute hierarchies, and the other is a hierarchy of the values.  The value hierarchy relates to the definition hierarchy and a specific user.</p>
  
  <p>My issue is one of referential integrity.</p>
  
  <p>How do I enforce that a UserAttribute record is related to the correct parent UserAttribute, based on the Attribute hierarchy.  If I an attribute B parented to A,  I shouldn't be able to create a UserAttribute related to Attribute B who is parented to a UserAttribute that is related to attribute C.</p>
  
  <p>Originally I thought I would:</p>
  
  <ul>
  <li>Make a composite primary key on the Attribute table based on
  AttributeID and ParentAttributeID.</li>
  <li>Make a composite primary key on the UserAttribute table based on the UserAttributeID and the AttributeID</li>
  <li>Create a self reference on the UserAttribute table using the composite key</li>
  <li>Relate the UserAttribute table to the attribute table using the composite primary key</li>
  </ul>
  
  <p>The issue is that root records int the Attribute table have a null ParentAttributeID and root records in the UserAttribute table have a null ParentUserAttributeID, and columns in a composite key can't have nulls so that puts the kybosh on that plan.</p>
  
  <p>Is there some way to achieve this either to a total different schema or slight tweaks to my existing schema?  Perhaps I am not modelling this appropriately.</p>