title: Theory of storing a number and text in same SQL field
body: <p>I have a three tables</p>
  
  <p><strong>Results:</strong>
  TestID
  TestCode
  Value</p>
  
  <p><strong>Tests:</strong>
  TestID
  TestType
  SysCodeID</p>
  
  <p><strong>SystemCodes</strong>
  SysCodeID
  ParentSysCodeID
  Description</p>
  
  <p>The question I have is for when the user is entering data into the <strong>results</strong> table.</p>
  
  <p>The formatting code when the row gets the focus changes the value field to a dropdown combobox if the testCode is of type SystemList.  The drop down has a list of all the system codes that have a parentsyscodeID of the test.SysCodeID.  When the user chooses a value in the list it translates into a number which goes into the value field.</p>
  
  <p>The datatype of the Results.Value field is integer.  I made it an integer instead of a string because when reporting it is easier to do calculations and sorting if it is a number.  There are issues if you are putting integer/decimal value into a string field.  As well, when the system was being designed they only wanted numbers in there.</p>
  
  <p>The users now want to put strings into the value field as well as numbers/values from a list and I'm wondering what the best way of doing that would be.</p>
  
  <hr>
  
  <p>Would it be bad practice to convert the field over to a string and then store both strings and integers in the same field?  There are different issues related to this one but i'm not sure if any are a really big deal.</p>
  
  <p>Should I add another column into the table of string datatype and  if the test is a string type then put the data the user enters into the different field.</p>
  
  <p>Another option would be to create a 1-1 relationship to another table and if the user types in a string into the value field it adds it into the new table with a key of a number.</p>
  
  <p>Anyone have any interesting ideas?</p>