title: Database schema which can support specialized properties
body: <p>I need to store a set of entities, of which there are several specialized versions. They have some common properties, but the specialized ones contain properties specific for that entity.</p>
  
  <h1>Solutions</h1>
  
  <p>The data store is a relational DBMS, and this is not for discussion :-) Specifically, it is the Microsoft SQL Server 2005.</p>
  
  <p>I could easily create a table for the common properties and then a table for each of the specialized versions. However, it is likely that new entities will have to be added to the solution later and I don't want to maintain both an object model <em>and</em> a database schema.</p>
  
  <p><strong>Another</strong> idea is to create a table</p>
  
  <pre><code>reading(&lt;common properties&gt;, extended_properties)
  </code></pre>
  
  <p>and have the <code>extended_properties</code>field be some kind of serialization of the extended properties. I was thinking either JSON or XML. I will most likely be using an ORM framework, but I haven't decided yet.  Either way, the object representation of a specialized entity from the <code>reading</code> could expose a dictionary <code>{extended_property_name, value}</code> containing the parsed key/value pairs from the <code>extended_properties</code> field.</p>
  
  <p>From this <a href="http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx</a> I gather that XML fields, combined with schemas for these, give the notion of typed XML inside the DBMS. Also, queries involving the XML contents in the <code>extended_properties</code>field can take these into account, too.</p>
  
  <h1>What I want</h1>
  
  <p>Feedback on my solution suggestions, primarily the one with the <code>reading</code> table and serialization of the extended properties.</p>
  
  <p>Also, I realize this is one of the limitations of relational DBMS' compared to key/value based stores. However, there surely must be some modelling techniques to accommodate this.</p>
  
  <p>Any feedback is greatly appreciated!</p>