March 10, 2011 at 11:41 am
I am designing a new table which will include a RESULTS column. I need to store two different types of data in the RESULTS column, decimal and boolean. What is the best approach to handle this type of issue? Any help will be greatly appreciated.
Sample:
TEST_IDRESULTS
CM_TH0.0002
CM_TRT
March 10, 2011 at 12:15 pm
Redesign your data model 🙂
Just kidding (not really)...boolean in SQL Server is implemented as the BIT data type where 0 = false. As an aside, NULLable bits are allowed too but some think they "make no sense".
You could implement the field as a DECIMAL(n,n) to suit the max potential size of your decimal...and then see 0.0 or 1.0 as True or False...but how would you know the difference between 1.0 as a True and 1.0 as the decimal 1.0?
You could use sentinel values for True / False if you know they will not be possible in the possible range(s) of decimal values...
I keep coming back to my first statement...you should have two columns.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2011 at 12:33 pm
That answers my question. Two columns it is.
Thanks
March 11, 2011 at 12:11 pm
I faced a similar issue recently and used a varchar field of a length that I though would accommodate all my results. I did have some reservations about this.
In my case I was capturing analytic data and my fields were AnalyticType and Datapoint. The type of analytics expand daily so I built this more abstract model rather than having to add a field to the table every time an analytic was added.
Assuming the number of results is known (in your case two) then add a second field. This is good database design. I veered from the accepted norms because I didn't want to change the model everyday.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply