What's the best way to handle storing 'N/A' for a numeric field in SQL Server?-CodePudding

Business has a number of numeric fields where they also need to be able to flag the field as 'N/A'. 'N/A' means the user has looked into putting a value in the field but has determined that the field is not applicable in this case.

This as opposed to NULL which simply means the value is not defined. When we see a NULL we assume that no one has entered a value yet, and so action is required.

Furthermore, we don't wish to create helper fields for all these or put a burden of extra logic on the interface -- it will be for a simple CRUD app.

Would it be considered bad practice in this case to use string/ varchar data types for these numeric fields? No heavy computation is required with the values.

CodePudding user response：

If the value is numeric and will be used as a numeric, you really, really want to store it as a numeric value so as to avoid amazingly irritating future coding hassles.

As described, your column values have three states:

Value has been reviewed and set to a discrete value
Vaue has been reviewed and determined to be unnecessary (your “N/A”)
Values has not yet been reviewed

Do these three states need to be tracked independently for each of these columns? Or is a “review” done once and applied to all three (i.e. “review” is done for all columns at the same time)?

It’s ugly but valid to use NULL to represent one of the “non-value” states, but if you need to track two distinct “non-value” states, you’re going to need something else. Having a “ColumnReviewState” column for each “Column” might do if each column needs to be tracked separately, or a “RowReviewState” if it’s all-or-nothing.

CodePudding user response：

Would it be considered bad practice in this case to use string/ varchar data types

Yes, because you cannot have two datatypes in one column, if the column is an int it can only be an int, and you should use int (to be able to make basic calculations). Adding a second column wont be a good idea, (what would be in first column when second column is N/A? And what would be in second column when first column is not empty?).

Now what you should do? Or you should use null, or if possible you can use 0, but 0 wont be applicable for something like age etc.

I don't understand why null shouldn't work? If you see they entered information in other columns, and this column is null, that means it's N/A.

CodePudding user response：

There are drawbacks to this approach and you must forego the benefits of strongly typed data. In the end it may make sense, but you should consider what you are losing.

You lose the built-in validations that come with having a numeric field. You may think it's easier on the CRUD app to make it a string, but what if they type in the letter 'O' instead of a zero (0)?

And then of course there is the logic that's need every time you need to evaluate the number or use it to calculate a value. You may need to use TRYCAST. One useful option is to use it to create a calculated field. You can encapsulate this logic {CASE WHEN 'N/A' THEN NULL WHEN TRYCAST(...) ELSE NULL END etc...}, and have a numeric field at your fingertips. The downside is that this approach may have performance impacts.

If after considering the all the downstream issues and impacts upon apps, if it gives Business what they need, and if the CRUD app cannot deliver the needed logic in a reasonable amount of time, and if it turns out to be less work overall, then it should be considered a reasonable solution.