Theory of storing a number and text in same SQL field

Question

I have a three tables

Results: TestID TestCode Value

Tests: TestID TestType SysCodeID

SystemCodes SysCodeID ParentSysCodeID Description

The question I have is for when the user is entering data into the results table.

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.

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.

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.

---

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.

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.

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.

Anyone have any interesting ideas?

Answer 1

I think the easiest way to do it would be to convert Results.Value to a "string" (char, varchar, whatever). Yes, this ruins the ability to do numeric sorting (and you won't be able to do a cast or convert on the column any longer since text will be intermingled with integer values), but I think any other method would be too complex to maintain properly. (For example, in the 1-1 case you mentioned, is that integer value the actual value or a foreign key to the string table? Now we need another column to determine that.)

Answer 2

I would create the extra column for string values. It's not true normalization but it's the easiest to implement and to work with.

Using the same field for both numbers and strings would work to as long as you don't plan on doing anything with the numbers like summing or sorting.

The extra table approach while good from a normalization standpoint is probably overly complex.

Answer 3

I'd convert the value field to string and add a column indicating what the datatype should be treated as for post processing and reporting.

Answer 4

Sql Server at least has an IsNumeric function you can use:

ORDER BY IsNumeric(Results.Value) DESC, 
         CASE WHEN IsNumeric(Results.Value) = 1 THEN Len(Results.Value) ELSE 99 END,
         Results.Value

Answer 5

Are you saying that they want to do free-form text entry? If that's the case, they will ruin the ability to do meaningful reporting on the field, because I can guarantee that they will not consistently enter the strings.

If they are going to be entering one of several preset strings (for example, grades of A, B, C, etc.) then make a lookup table for those strings which maps to numeric values for sorting, evaluating, averaging, etc.

If they really want to be able to start entering in free-form text and you can't dissuade them from it, add another column along the lines of other_entry. Have a predefined value that means "other" to put in your value column. That way, when you're doing reporting you can either roll up all of those random "other" values or you can simply ignore them. Make sure that you add the "other" into your SystemCodes table so that you can keep a foreign key between that and the Results table. If you don't already have one, then you should definitely consider adding one.

Good luck!

Answer 6

One of two solutions comes to mind. It kind of depends on what you're doing with the numbers. If they just represent a choice of some kind, then pick one. If you need to do math on it (sorting, conversion, etc..) then pick another.

1. Change the column to be a varchar, and then either put numbers or text in it. Sorting numerically will suck, but hey, it's one column.

2. Have both a varchar column for the text, and an int column for the number. Use a view to hide the differences, and to control the sorting if necessary. You can coalesce the two columns together if you don't care about whether you're looking at numbers or text.

Answer 7

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.

It sounds like the users want to add new 'testCodes'. If that is the case why not just add them to your existing testcode table and keep your existing format.

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.

No it's not a big deal. Often PO numbers or Invoice numbers have numbers or a combination of letters and numbers. You are right however about the performance of the database on a number field as opposed to a string, but if you index the string field you end up with the database doing it's scans on numeric indexes anyway.

The problems you may have had with your decimals as strings probably have to do with the floating point data types in which the server essentially estimates the value of the field and only retains accuracy to a certain number of digits. This can lead to a whole host of rounding errors if you are concerned about the digits. You can avoid that issue by using currency fields or the like that have static accuracy of the decimals. lol I learned this the hard way.

Tom H. did a great job addressing everything else.

Answer 8

What about treating Results.Value as if it were a numeric ValueCode that becomes an foreign key referencing another table that contains a ValueCode and a string that matches it.

CREATE TABLE ValueCodes
(
    Value     INTEGER NOT NULL PRIMARY KEY,
    Meaning   VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE Results
(
   TestID     ...,
   TestCode   ...,
   Value      INTEGER NOT NULL FOREIGN KEY REFERENCES ValueCodes
);

You continue storing integers as now, but they are references to a limited set of values in the ValueCodes table. Most of the existing values appear as an integer such as 100 with a string representing the same value "100". New codes can be added as needed.