Storing N/A in Sql Server

Question

We have a form that has multiple fields of different data type, including string, datetime and numbers.

Our customer want to be able to write N/A (Not Applicable) in any of these fields, as well as being able to leave it blank.

Does someone has an idea on how I should design my Sql Server table so it can support a value, NULL or N/A?

We are using Sql Server 2008.

Answer 1

The only type I could think of is varchar.

You will have to have code to interpret data based on the field (i.e if the name of the field is InvoiceDate, it is a date).

EDIT: After reading your recent comment, you can have a "IsNA" (bit) field for each of the field. So, if the user chooses "NA" for InvoiceDate, set the "IsInvoiceDateNA" to true & false (otherwise).

The view of the screen will be driven as per the but field for each of the input field.

Answer 2

I would store the answer in a single varchar (or nvarchar) field.

Application code can verify and interpret the input to be a number or date, etc. The meta information about the field will tell you how to interpret it.

In this scenario, you can have NULL, or blank, or N/A, or a real value...

Answer 3

As others have mentioned, the varchar or nvarchar is your best solution.

Answer 4

Regardless of data type of the column, default null can be applied. Where returning the results you could either use

Select Isnull(colX,'N/A')

or Select Coalesce(collX,'N/A')

Answer 5

If N/A and blank are truly different values you need to support, I would say to create the field as a nullable field (in which case the NULL would represent the blank value), and then include a second bit field for [FieldName]NotAvailable or [FieldName]Specified.

Granted, you would be using two separate fields to represent a single logical concept, trying to force it all into one field causes two concepts to be stored in the same field (the field is interpreted one way, unless there is some magical value, then it means something else). This way, with the separate field, it is far more clear what the expected behavior of the fields is.

Answer 6

If they only want one state, you can, as other folks indicate simply translate the null:

SELECT COALESCE(thecolumn1,'N/A') AS mycolumnalias

IF it is discovered that they really desire to have the 'tri-state' on the column, you can create a companion VARCHAR column.

SELECT COALESCE(thecolumn1, companioncolumn1,'N/A') AS mycolumnalias

Advantages:

• If they change their minds again, you can change the companioncolumn1 value.
• If you use both columns in the COALESCE, it automatically gets the first column when it is not null, gets the companion value if set and the first is null, and has a fallback to the 'N/A' specified in the select.
• You can manage this at the database layer
• You maintain the data type of the origial column

Disadvantages:

• Has an extra column in database to manage.
• Slightly more complex SELECT statements
• You have to manage this at the database layer
• Type casting and validation will be more complex, but you have that issue already