How to store a numeric value which can have other statuses in a database?

Question

I need to store a set of numbers in a database which are imported from a spreadsheet.

Sometimes a number is just a number. But in other times, a value can be "missing", "N/A", or blank and these all represent different things.

What would be a good approach to store these numbers in the database? Originally I only had to account for N/A. So I made it -1 as I imported them (this only works if the number can never be negative obviously). I could use other negative numbers for other statuses. However, that seems clunky to me.

Should I store the numbers as string then apply conversion at use time? Should I create a matching table that stores different statuses of each value?

Answer 1

You need two fields - status and number

Answer 2

Using -1 was a bad design from the start: you should have been using NULL, and at this point you could add a secondary column to store a status code for why a NULL value is there.

Answer 3

do not store as a string and convert on use, that is the worst, because you will have to expend resources to convert every time (forever) you use the data.

Store two fields:

NumberValue   number
NumberStatus  char(1)  -"M"=missing, "N"=n/a, "B"=blank, "V"=have a numeric value

Answer 4

I agree on needing two fields status and number (so I agree with KM and Scott )

The reasons included that if you have anything but numbers from the spreadsheet (this includes your -1 and also NULLs) then aggregate functions like SUM and average will not give correct answers.

Having an extra field to give the status whose values include missing, N/A and numeric for when you have a number allows you to be able to query for these statuses.

Using NULL is not myuch use here as it would not differentiate between the missing and N/A values