在Sql Server中存储N / A.

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.

我们的客户希望能够在任何这些领域中编写N / A(不适用),并且能够将其留空。

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

有人知道我应该如何设计我的Sql Server表,以便它可以支持值,NULL或N / A?

We are using Sql Server 2008.

我们正在使用Sql Server 2008。

6 个解决方案

#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.

如果N / A和空白是您需要支持的真正不同的值,我会说将字段创建为可空字段(在这种情况下,NULL将表示空值),然后为[FieldName]包含第二个字段NotAvailable或[FieldName]指定。

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.

当然,你将使用两个单独的字段来表示单个逻辑概念,试图将它全部强制到一个字段中导致两个概念存储在同一个字段中(该字段被解释为单向,除非有一些神奇的值,然后它意味着别的东西)。这样,使用单独的字段,字段的预期行为就更清楚了。

#2

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

如果他们只想要一个状态,你可以像其他人一样只是翻译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.

如果发现它们确实希望在列上具有“三态”,则可以创建伴随VARCHAR列。

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

Advantages:

If they change their minds again, you can change the companioncolumn1 value.

如果他们再次改变主意,您可以更改companioncolumn1值。

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.

如果在COALESCE中同时使用这两列,它会在非空时自动获取第一列,如果设置则获取伴随值,第一列为空,并且回退到select中指定的“N / A”。

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

略微复杂的SELECT语句

You have to manage this at the database layer

您必须在数据库层管理它

Type casting and validation will be more complex, but you have that issue already

类型转换和验证将更复杂,但您已经遇到了这个问题

#3

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

我会将答案存储在单个varchar(或nvarchar)字段中。

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...

在这种情况下,您可以具有NULL,空白或N / A,或实际值...

#4

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

正如其他人所提到的,varchar或nvarchar是您最好的解决方案。

#5

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

无论列的数据类型如何,都可以应用默认的null。返回结果的地方你可以使用

Select Isnull(colX,'N/A')

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

或选择合并(collX,'N / A')

#6

The only type I could think of is varchar.

我能想到的唯一类型是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).

您必须拥有基于字段解释数据的代码(即,如果字段的名称是InvoiceDate,则它是日期)。

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).

编辑:阅读您最近的评论后,您可以为每个字段设置一个“IsNA”(位)字段。因此,如果用户为InvoiceDate选择“NA”,则将“IsInvoiceDateNA”设置为true和false(否则)。

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

屏幕视图将根据每个输入字段的but字段进行驱动。

#1

#2