What is the best way to store multiple data type into database

Question

I am designing a database using MSSQL server 2005 to store log values from different devices. The datatype of the values can be boolean, int-32 or 64 bit-double

There will be a lookup table called channels:

ID(PK)      | int32

device_name | varchar(32)  

There will be a table called values to store the value. So boolean and int-32, double values will be casted to DECIMAL datatype.

ID(foreign key to ID@channels)  | int32

logtime                         | DATETIME
value                           | DECIMAL

The reason I do that is the end user can select data using a simple select statement on one table, such as select logtime,value from values where ID = 1 without knowing the datatype of the channel.

But this is waste of memory, since now I stored int32 and boolean to Decimal. I am new to database programming, I wonder if anyone know a better way to do this?

Answer 1

You can store 3 individual fields that are nullable. Two will have NULL value, the third will have the actual value (you can even enforce with a CHECK constraint that exactly one of the three is not-NULL). NULL values occupy no space. You can add a computed value that exposes the non-null value as a pseudo-column.

Alternatively you can use a sql_variant field. sql_variant can store multiple types and preserve the type metadata (type, precision, scale).

Answer 2

This is an example of a CHECK constraint that ensures exactly one of the three IS NOT NULL

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_ColumNulls CHECK(
(CASE
   WHEN col1 IS NOT NULL
      THEN 1
   WHEN col2 IS NOT NULL
      THEN 2
   WHEN col3 IS NOT NULL
      THEN 3
   ELSE
      500
END)
=
(CASE
   WHEN col3 IS NOT NULL
      THEN 3
   WHEN col2 IS NOT NULL
      THEN 2
   WHEN col1 IS NOT NULL
      THEN 1
   ELSE
      600
END)
)