How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc

I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields).

The problem is that there are special cases where these values can be strings:

• if the amount of a nutrient in the food is too small, the value will be

"Tr" (trace).

• If the measurement does not apply to that food, the value will be "NA"

and some other cases.

I am currently using strings to store the values, and converting them to numbers in my app. But there is a better way to do this?

Here is a example of what the table looks like:

id, kilocalories, carbohydrate_grams, lipid_grams, protein_grams

'1', '123.5348925', '25.80975', 'Tr', 'NA'

I am using MySQL 8.

Sorry, i think you needed a bit more of information.

I let NULL enabled, but there is no case that the field should be null. I just enabled it because this database can receive new data later, with other conventions. (I am using the brazilian database of foods, but I will insert the american database in the future).

Any field in the table can receive one of these values:

• * means that the tests are being re-evaluated;

• Empty fields means that no lab test was requested to that field;

• NA means not applicable;

• Tr means trace;

This is a dump of the real table with two lines of data:

``
--
-- Table structure for table nutritional_table
--

DROP TABLE IF EXISTS nutritional_table;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!50503 SET character_set_client = utf8mb4 /;
CREATE TABLE nutritional_table (
id int(11) NOT NULL AUTO_INCREMENT,
kilocalories varchar(255) DEFAULT NULL,
carbohydrate_grams varchar(255) DEFAULT NULL,
lipid_grams varchar(255) DEFAULT NULL,
saturados varchar(255) DEFAULT NULL,
monoinsaturados varchar(255) DEFAULT NULL,
poliinsaturados varchar(255) DEFAULT NULL,
protein_grams varchar(255) DEFAULT NULL,
dietary_fiber_grams` varchar(255) DEFAULT NU

TL;DR

Store the numbers as numbers! For 'Trace', 'N/A' &c. have negative integers as codes and deal with the logic that way - CASE and company! OR, use GENERATED fields and let the server do all the conversions from VARCHAR to INT - never have to worry about a mistake again! Check out the use of a CASEstatement in a GENERATED column definition at the bottom of this answer!

I would do this in one of two ways:

First way (the better of the two - by far!):

You have:

CREATE TABLE nutritional_table 
(
  ..
  ..
  carbohydrate_grams varchar(255) DEFAULT NULL,

The gramme/gram is an SI unit: SI unit symbol: g

Anything with gramme/gram should be a pure number. The field/column containing it should be suffixed with _g.

Just as a matter of interest, should that not be ... (255) NOT NULL DEFAULT 0? It beats the COALESCE function - I always to try and avoid NULLs if possible!

monoinsaturados varchar(255) DEFAULT NULL,
poliinsaturados varchar(255) DEFAULT NULL,

Are these all numbers or percentages or what? If pure numbers or %, then store them as pure numbers, or with _pc as a percent suffix.

cholesterol_milligrams varchar(255) DEFAULT NULL,

one-millionth of a kilogram is 1 mg (one milligram),

The milligramme is a recognised unit, albeit not official SI. The suffix should be _mg!


The microgram is typically abbreviated "mcg" in pharmaceutical and nutritional supplement labelling, to avoid confusion, since the "μ" prefix is not always well recognised outside of technical disciplines

All microgram units should be suffixed with _mcg.

retinol_micrograms varchar(255) DEFAULT NULL,

  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=598 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Why you go to the trouble of converting the units back and forth as strings is beyond me!

You should also maintain comments on your tables so that poor devs don't get confused and have no excuse for f**ing up! Make it part of SOP that when a field is added, an explanatory comment is added. Also, the tables themselves should have comments, possibly with references about where to find further information (example - of use later on!).

CREATE TABLE nutritional_content 
(
  nutrient_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY comment 'The Primary Key - doh!!!',
  test_measurement_gr VARCHAR (255) comment 'The weight of M&Ms in Outer Space'
) comment 'This table contains units of measurement as outlined in https://your_server/tables/nutritional_content.html';

Then, one can do stuff like this (table comments):

-- Learnt lots about comments on tables and columns - useful stuff!
-- Check out these links - upvote if you're feeling generous - I was!

-- https://stackoverflow.com/questions/5404051/show-comment-of-fields-from-mysql-table/5404118
-- https://stackoverflow.com/questions/3938966/how-can-i-access-the-table-comment-from-a-mysql-table
-- https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns
-- https://dba.stackexchange.com/questions/59587/changing-mysql-table-comment

SELECT table_comment 
    FROM information_schema.tables 
--    WHERE table_schema='my_cool_database' -- fiddle - don't know schema_name
      WHERE table_name = 'nutritional_content ';

Result:

TABLE_COMMENT
This table contains units of measurement as outlined in https://your_server/tables/nutritional_content.html

Field comments:

SELECT column_name, column_type, column_default, column_comment
FROM information_schema.columns 
WHERE table_name =  'nutritional_content'
-- and `table_schema` = 'db-name'; -- fiddle - don't know schema_name

Result:

COLUMN_NAME COLUMN_TYPE COLUMN_DEFAULT  COLUMN_COMMENT
nutrient_id int(11)     The Primary Key - doh!!!
test_measurement_gr varchar(255)        The weight of M&Ms in Outer Space in grammes

Note the explanation of what units are being used, in case anybody is so slow that they don't realise that the _gr suffix is grammes!

For Trace, 'N/A' &c. - have a code table, say:

CREATE TABLE nutrition_code
(
  code_id SIGNED NOT NULL PRIMARY KEY (CHECK the_code < 0)
  the_code VARCHAR(255) NOT NULL
);

INSERT INTO nutrition_code VALUES (-1, 'Trace'), (-2, 'N/A');

So, I would imagine that most of the time, you're not too interested in traces or not-applicables? This might introduce a level of complexity, but I still think that it's a win-win compared to your current scenario. Take a look at the bottom of the fiddle for how to CASTthese - very hacky - but then it is MySQL! :-)

OK, so far, so good - if you want to overhaul your system, which I advise you to at least set as a long term goal, but better to start ASAP!

But, what you can do - if, say, for legacy reasons you have to keep your table the way it is while you change to a reasonable table structure is this!

The second way (temporary until code refactoring):

This would be to use GENERATED (or COMPUTED or sometime

StackExchange Database Administrators Q#254383, answer score: 3