To preface, similar questions have been asked, but I have not found any that address a table that has a large number of columns affected by this issue. All the similar seem to address a table that has a small number of columns affected by this issue...
- Proper way to store a value that could be multiple different types
- Best way to store a value which could be multiple types
- Best way to store different types of data in a single column, which should be query friendly - MySql
...but I have a MySQL database table that has many (e.g., 30) columns currently setup as the data type
Now, a new scenario has arisen where I need to enter a
decimal(5,2) OR a
char(2) (i.e., one or the other, but never both) for each of those 30 columns. To give you context, I am entering the quantity of elements detected in a sample and, if the quantity of an element is not detected, then I must be able to identify that an element was tested, but not detected, such as "ND". I also must know if the element was not yet tested. For example, if the column is
DEFAULT NULL, then it may indicate that I have not yet tested the respective element. So, there are three possible situations related to this scenario:
decimal(5,2)value indicates that the element was tested and detected.
char(2)value indicates that the element was tested and not detected.
NULL(...?) value indicates that the element was not tested.
So, my initial thought was to change the change columns' type to a
varchar, in order for the columns to be able to store either a
decimal(5,2) value, but that seems be a dangerous path called
Entity-Attribute_Value, which may require me to build the application layer more to accommodate the limitations caused by it.
So, I read chapter 6 of the book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" (http://rads.stackoverflow.com/amzn/click/1934356557) and several other related posts, and the two most common answers seem to be:
1. Use a design that is based on the type of query you want to perform.
In my case, I want to perform multiple types of queries that compare column values of different rows, determine averages, and possibly unforeseen more.
2. Use the
Class Table Inheritance design.
I would like to implement the
Class Table Inheritance design, but that would require many more tables to be created for each column. It seems like an overly complex solution, given the many columns that need only one of two types of data.
For example, if my understanding about
Class Table Inheritance is correct, then for each of the 30 columns in my base table, I must create a single table for each subtype and make their primary key also serve as a foreign key to the base table, which means that I must create 60 tables. It seems excessive to create 60 tables in order to deal with 30 columns that might have one of two data types. Also, it seems that I would need to figure a way to manage those 30 pairs of tables so they refer to the same column, possibly in the application layer.
One possible solution that comes to mind before I end this post is: Use the
Class Table Inheritance design, but instead of creating 30 pairs of tables for my two data types, create just 2 tables for the 2 data types and add a column that stores a value that relates each pair, such as the name of the base table column that they would relate to. But this does not seem like good practice either.