title: Polymorphic database design : does this approach have a name?
body: <p>I have a base enitiy (items) that will host a vast range of item types (>200) with totaly different properties. I want a clean portable and fast solution and have come up with an idea that maby has a name I'm unaware of.</p>
  
  <p>Here it goes:</p>
  
  <ul>
  <li><p>items-entity holds base class fields + additional fields for subclass fields but with dummie-names, ItemID,ItemNo,ItemTypeID,int1,int2,dec1,dec2,dec3,str1,str2</p></li>
  <li><p>referenced itemtype-record holds name of type and child enity (1:n):</p></li>
  <li><p>itemtypefields [itemtypeid,name,type,realfield]
  example in [53,MaxPressure,dec,dec3]</p></li>
  </ul>
  
  <p>It's limitations:</p>
  
  <ul>
  <li>hard to estimate field requirements in baseclass</li>
  <li>harder to add domains/checkconstraints based on child type</li>
  <li>need application layer to translate tagged sql to real query</li>
  <li>Only possible to query one type at a time since shared attributes may be defined to different "real-fields".</li>
  </ul>
  
  <p>3rd bullet explained:</p>
  
  <pre><code>select ItemNo,_MaxPressure_ from items where ItemTypeID=10 and _MaxPressure_&gt;42
  should translate to:
  select ItemNo,dec3 as MaxPressure from items where ItemType=10 and dec3&gt;42
  </code></pre>
  
  <p>(can't do that with sp's or udf's right - or whould it be possible?)</p>
  
  <p>But benefits of:</p>
  
  <ul>
  <li>Performance</li>
  <li>Ease of CRUD-operations</li>
  <li>Easier to sort/filter at application level.</li>
  </ul>
  
  <p>Now - does it have a name?</p>