title: DB Design to store custom fields for a table
body: <p>this question came up based on the responses I got for the question
  <a href="https://stackoverflow.com/questions/2785033/getting-wierd-issue-with-to-number-function-in-oracle">Getting weird issue with TO_NUMBER function in Oracle</a></p>
  
  <p>As everyone suggested that storing Numeric values in VARCHAR2 columns is not a good practice (which I totally agree with), I am wondering about a basic Design choice our team has made and whether there are better way to design.</p>
  
  <p>Problem Statement : We Have many tables where we want to give certain number of custom fields. The number of required custom fields is known, but what kind of attribute is mapped to the column is available to the user</p>
  
  <p>E.g. I am putting down a hypothetical scenario below</p>
  
  <p>Say you have a laptop which stores 50 attribute values for every laptop record. Each laptop attributes are created by the some admin who creates the laptop.</p>
  
  <p>A user created a laptop product lets say lap1 with attributes String, String, numeric, numeric, String</p>
  
  <p>Second user created laptop lap2 with attributes String,numeric,String,String,numeric</p>
  
  <p>Currently there data in our design gets persisted as following</p>
  
  <pre><code>Laptop Table
  Id Name field1  field2 field3  field4 field5
  1  lap1 lappy   lappy  12      13     lappy
  2  lap2 lappy2  13     lappy2  lapp2  12
  </code></pre>
  
  <p>This example kind of simulates our requirement and our design</p>
  
  <p>Now here if somebody is lookinup records for lap2 table doing a comparison on field2, We need to apply TO_NUMBER.</p>
  
  <pre><code>select * from laptop 
  where name='lap2'  
  and TO_NUMBER(field2) &lt; 15
  </code></pre>
  
  <p>TO_NUMBER fails in some cases when query plan decides to first apply to_number instead of the other filter.</p>
  
  <p>QUESTIONS<br>
  Is this a valid design?<br>
  What are the other alternative ways to solve this problem?<br>
  One of our team mates suggested creating tables on the fly for such cases. Is that a good idea?<br>
  How do popular ORM tools give custom fields or flex fields handling?</p>
  
  <p>I hope I was able to make sense in the question.</p>
  
  <p>Sorry for such a long text.. </p>
  
  <p>This causes us to use TO_NUMBER when queryio</p>