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?

Disk space is cheap - I think your initial approach (convert to decimal) is optimal, no need to fiddle with null values or different fields. — Arvo, Apr 20 '11 at 06:05

score 6 · Accepted Answer · answered Apr 20 '11 at 02:17

6

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).

answered Apr 20 '11 at 02:17

Remus Rusanu

288,378
40
442
569

I think the first solution is better, I am not sure what kind of overhead that sql_variant will bring to me. – LOK Apr 25 '11 at 21:31

score 2 · Answer 2 · answered Apr 08 '14 at 00:34

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)
)

score 0 · Answer 3 · edited Apr 25 '11 at 21:56

0

By using 3 individual fields that are nullable, I created a view called mixedNumericView that uses CASE to select the right column. I assume this way using CASE is inexpensive, am I right? Any alternative ways to do this? The log frequency can be up to every minute, I want to make sure this is the best way to select data.

SELECT [ID], [logtime]
      ,'value' = 
      CASE
        WHEN [intValue] is not NULL  THEN [intValue]
        WHEN [bitValue] is not NULL  THEN [bitValue]
        WHEN [floatValue] is not NULL THEN [floatValue]
        ELSE NULL
       END
  FROM [dbo].[mixedNumericView]

edited Apr 25 '11 at 21:56

Martin Smith

438,706
87
741
845

answered Apr 25 '11 at 21:40

LOK

349
2
4
15

You can use a computed column on the table itself: `ALTER TABLE ... ADD [value] AS COALESCE(IntValue, COALESCE(bitValue, floatValue))` – Remus Rusanu Apr 25 '11 at 21:43
1

@Remus - Or `COALESCE(IntValue,bitValue, floatValue)` presumably. – Martin Smith Apr 25 '11 at 22:03
1

@Martin: I guess it was the right time for me to learn that coalesce accepts an open list of arguments... – Remus Rusanu Apr 25 '11 at 22:14

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

3 Answers3