Databases
StackOverflow2013
Postsdbo
POTheory of storing a number and text in same SQL field
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTheory of storing a number and text in same SQL field
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJonathan Leffler
    1. USJaydel Gluckie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTheory of storing a number and text in same SQL field
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you show a few examples of what the data going in might look like.
      singulars
      1. POTheory of storing a number and text in same SQL field
      1. USmadcolor
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload