How to design database for polymorphic relationships

Posted on

Question :

I have the following scenerio:

  • There are some categories
  • Each category has attributes
  • An attribute could be of free text or collection of options
  • There is a product which is assigned to category which needs to store values/options of attributes

How can a model be made without any repetition, and which correctly addresses the polymorphic nature of attributes?

Answer :

I would design a meta-data/value based model, as:

enter image description here

Or, where formality is enforced,

enter image description here

Being AttributeCode and ValueCode unique, textual, human-readable identifiers, alongside the corresponding AttributeId and ValueId.

For instance, the hypothetical meta-attribute length, physically stored with AttributeId = 1, would (should!) be referenced in specific queries as AttributeCode = 'LENGTH'.

(of course, AttributeCode and ValueCode could compose the PKs… but I personally prefer PKs columns to be integer-only)

Leave a Reply

Your email address will not be published. Required fields are marked *