SQL: storing mixed data types

I have an SQL application where I need to store different kinds of data. The structure is
  - id: string
  - value: string or number

It seems I have 2 choices. Either keep all id/string pairs in one table (say TabS) and all id/number pairs in another (say TabN), or else convert numbers to strings and store them all together. There will be another table that gives the type of each id.

I'm inclined to the second alternative (convert to strings) but wanted to see if there's something I'm missing. The solution needs to be compatible with MS SQL Server and with MySQL.

Who is Participating?
wildwoodweedConnect With a Mentor Commented:
I would say that your second option is definitely your best bet, for almost nothing more that reasons of simplicity.  You can store all the values as varchar in the table.  Then when you want to retrieve one, you can programmatically convert it back to whatever format you'd like.  I've done this before and it is not as much trouble as it sounds like.  Hope this helps!
BrandonGalderisiConnect With a Mentor Commented:
Well you have this in the MY and MS SQL zones.  So I don't know what you have.

BUT.  If you are using MS SQL Server, you can use a SQL_Variant column which will allow you to store all data types, except Text/nText.  The great thing is that it stores it in it's native format.

create table #t (theVar sql_variant)
declare @date       datetime
       ,@int        int
       ,@nvarchar   nvarchar(4000)
       ,@varchar    varchar(8000)
       ,@numeric    numeric
set @date      = getdate()
set @int       = 1
set @nvarchar  = N'Hello'
set @varchar   = 'goodbye'
set @numeric   = 11.2
insert into #t values(@date)
insert into #t values(@int)
insert into #t values(@nvarchar)
insert into #t values(@varchar)
insert into #t values(@numeric)
select * from #t
drop table #t

Open in new window

BlearyEyeAuthor Commented:
Clever idea, BrandonGalderisi. Unfortunately, the solution has to support by MS and My SQL, which is why I cross-listed it. Looks like I'm stuck with storing numbers as strings.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.