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

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 2

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