0

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

enter image description here

The table allows me to create definitions of hierarchies such as:

enter image description here

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:

enter image description here

The table allows me to assign values to attributes within a hierarchy where there can be multiple instances of each attributes and their ancestors:

enter image description here

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.

My issue is one of referential integrity.

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.

Originally I thought I would:

  • Make a composite primary key on the Attribute table based on AttributeID and ParentAttributeID.
  • Make a composite primary key on the UserAttribute table based on the UserAttributeID and the AttributeID
  • Create a self reference on the UserAttribute table using the composite key
  • Relate the UserAttribute table to the attribute table using the composite primary key

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.

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.

Jeremy
  • 44,950
  • 68
  • 206
  • 332

1 Answers1

0

You are already tracking the attribute's parent in Attribute, so don't track the parent UserAttributeValue in UserAttributeValue. Have UserAttributeValue only contain the information about the user's value for the specified attribute, and navigate the tree on the Attribute side.

On a side note, if there can only be one value per user per attribute, you could also drop the column UserAttributeID and instead use the composite key of UserID, AttributeID.

Jacob Lambert
  • 7,449
  • 8
  • 27
  • 47
  • I think I need to have a parent in UserAttribute, because there can be multiple sets of attribute values. Consider A <- B <- C in Attribute table. In value table I could have multiple instances of B that are parented to A, then each instance of B has a different value for C. – Jeremy Feb 09 '18 at 15:50
  • @Jeremy, regardless, you still don't need the second hierarchy, otherwise they might start contradicting each other. And the PK for the values table should be expanded, of course, so that it will allow multiple instances of the same attribute for the same user. – Roger Wolf Feb 14 '18 at 06:33