How to design database for polymorphic relationships

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?

• Nicolas Durand: Is the free text attribute can't be an option and become a collection with only one option ?

Answer 1

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)