In my table I need to store a physical quantity that can be given either as a numeric value or as a numeric interval. The table below illustrates the idea:
------------------------------
Isotope_ID | Atomic_Weight
------------------------------
1 | 1.00784
2 | [6.938, 6.997]
... | ...
This table is unacceptable because the field Atomic_Weight contains values of different types. What is the best practice in such cases?
Edit1: There are three possible ways to represent information about atomic weight:
These three subtypes cannot be stored in one field because this would violate 1 Normalization Form. This is why the example table is unacceptable. I will try to restate my question more clearly: What are possible ways to store information about atomic weight (that can be given in one of the three different subtypes) in my database?
either as a numeric value or as a numeric interval
In the case of intervals you can store a single value x as [x,x].
In this application it's not like the single values are exact values. They only represent a measurement to a certain accuracy. Even the interval endpoints only represent measurements to a certain accuracy.
This table is unacceptable because the field Atomic_Weight contains values of different types.
The relational model doesn't care what values are in a "type". If no DBMS "type" fits yours then you must encode your ideal table & column into one or more table(s) and/or column(s).
You can encode them as strings. But then the DBMS doesn't know how to optimize queries involving their constituent values as well as for a multiple column encoding. And you must constantly decode and encode them to manipulate per parts.
Weight_string (isotope, weight)
// VALUES (1, '1.00874'), (2, '[6.938, 6.997]')
What is the best practice in such cases?
The main pattern is to have a table for every non-primitive subtype, and to encode the values of a subtype as one or more columns. (If a subtype has a subtype in turn, repeat.)
Weight_single (isotope, weight_single)
// VALUES (1, 1.00874)
Weight_interval(isotope, weight_min, weight_max)
// VALUES (2, 6.938, 6.997)
Another pattern is to encode each value in as many columns as necessary, whether they are used or not.
Weight_1_row_NULL_unused(isotope, single, interval_min, interval_max,
// VALUES (1, 1.00874, NULL, NULL), (2, NULL, 6.938, 6.997)
Weight_1_row_type_tag(isotope, type_tag, if_single, if_finite_min, if_finite_max)
// VALUES (1, 'single', 1.00874, 0, 0),(2, 'interval', 0, 6.938, 6.997)
Search re SQL subtypes/subtyping tables.