title: Approach to generic database design
body: <p>An application that I'm facing at a customer, looks like this:</p>
  
  <ul>
  <li>it allows end users to enter "materials".</li>
  <li>To those materials, they can append any number of "properties".</li>
  <li>Properties can have a any value of type: decimal, int, dateTime and varchar (length varying from 5 characters to large chunks of text), </li>
  </ul>
  
  <p>Essentially, the Schema looks like this:</p>
  
  <p><strong>Materials</strong><br/>
  MaterialID int not null PK<br/>
  MaterialName varchar(100) not null</p>
  
  <p><strong>Properties</strong><br/>
  PropertyID<br/>
  PropertyName varchar(100)<br/></p>
  
  <p><strong>MaterialsProperties</strong><br/>
  MaterialID<br/>
  PropertyID<br/>
  PropertyValue varchar(3000)<br/> </p>
  
  <p>An essential feature of the application is the search functionality:
  end users can search materials by entering queries like: </p>
  
  <ul>
  <li>[property] inspectionDate > [DateTimeValue]</li>
  <li>[property] serialNr = 35465488</li>
  </ul>
  
  <p>Guess how this performs over the MaterialsProperties-table with nearly 2 million records in it.</p>
  
  <p>Database was initially created under SQL Server 2000 and later on migrated to SQL Server 2005</p>
  
  <p>How can this be done better?</p>