-2

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.

In his books on SQL Server, Itzik Ben-Gan states that NULL represents a missing or unknown value. We follow that in our design, and when we see a NULL we assume that no one has entered a value yet, and so action is required.

'N/A' on the other hand means the user has looked into it and determined the value does not apply. For example, Year of Renovation would not apply to newly built buildings that have not been renovated.

We don't wish to create helper fields or a new table 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.

TK Bruin
  • 472
  • 4
  • 15
  • 3
    For decades "magic" values were used for cases like this. Back then it was more justifiable than including additional "metadata" due to much more severe limits on memory and storage. That's not really the case anymore. But if you insist, what you need to do is determine a value *that cannot ever be part of the business domain*, and use that value to mean "N/A". Is this actually less work though? It means having to change code everywhere to check for the magic value. And that same effort would be needed if you switched to char and used an empty string or "N/A". Just add the metadata. – allmhuran Jul 13 '22 at 17:15
  • 1
    "NULL which simply means" Null is a value different from typical values & doesn't mean anything in particular. When it appears in a row it means what the DBA said it means for it to have been put there per what it means for the row to have been put there rather than having been leff out of the table. It is treated in certain ways by certain operators/functionality that fragmentally support certain uses. There is no particular problem using null to indicate what could reasonably described as a column being n/a & that is probably its most common use. – philipxy Jul 13 '22 at 20:42
  • Where are you stuck in what published presentation of what design method? PS Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Jul 13 '22 at 20:55
  • [What to use instead of Null if no data is present in SQL?](https://stackoverflow.com/q/36865511/3404097) [When to use Null vs. N/A in columns?](https://stackoverflow.com/q/28854903/3404097) etc etc – philipxy Jul 13 '22 at 23:07

3 Answers3

1

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.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • So if I have 50 such columns on a table, then I need 100 columns of data? And how would that appear on a CRUD app? – TK Bruin Jul 13 '22 at 21:13
  • @TKBruin A more "pure" solution (IMO) would not be to add a "flag" column for each possibly-"N/A" column, but rather to move the possibly "N/A" column into its own table, in a "0 or 1 to 1" relationship with the original table. (ie, it uses the same primary key). If a row exists in the new table the user has entered a value. If the value is null, it means "N/A". If there is no row, then no data entry has been done. If you have 50 such columns and they are independent of each other, this would mean 50 such additional tables. This is obviously not less work, but it's cleaner. – allmhuran Jul 14 '22 at 00:19
  • Do you have a fixed set number of these columns? Will more be added over time? Do you need EACH of them every time for every users are working with? Depending on the answers to the aboves, @allmhuran's idea seems solid: one row per (relevant) attribute of the , with no row meaning one thing, row-with-null meaning another, and row-with-data meaning use this value. As for how the UI looks, it looks like whatever it needs to look like to be usable. The application transforms the user input into a form storable in the DB, and transforms the data in the DB to presentable form. – Philip Kelley Jul 14 '22 at 18:34
0

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.

Shmiel
  • 1,201
  • 10
  • 25
  • "If you see they entered information in other columns, and this column is null, that means it's N/A." -- That's an assumption we cannot make. – TK Bruin Jul 13 '22 at 17:41
  • @TKBruin - _we_ can't but it's not an unreasonable design decision to make (and document). – Ben Thul Jul 13 '22 at 20:49
  • 1
    @BenThul - I get what you are saying and you are correct. In this case, the data the requirement involves data that is entered at different points in time. So we cannot assume that if other fields have a value that someone already checked this field. And where do we place the bar? If there is data in one field then all the other fields have been reviewed? – TK Bruin Jul 13 '22 at 21:06
  • @BenThul That's why I didn't delete the answer after seeing the comment. – Shmiel Jul 14 '22 at 12:57
0

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.

TK Bruin
  • 472
  • 4
  • 15