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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.