Storing statistics of multple data types in SQL Server 2008

I am creating a statistics module in SQL Server 2008 that allows users to save data in any number of formats (date, int, decimal, percent, etc...). Currently I am using a single table to store these values as type varchar, with an extra field to denote the datatype that it should be.

When I display the value, I use that datatype field to format it. I use sprocs to calculate the data for reporting; and the datatype field to convert to the appropriate datatype for the appropriate calculations.

This approach works, but I don't like storing all kinds of data in a varchar field. The only alternative that I can see is to have separate tables for each datatype I want to store, and save the record information to the appropriate table based on datatype. To retreive, I run a case statement to join the appropriate table and get the data. This seems to solve. This however, seems like a lot of work for ... what gain?

Wondering if I'm missing something here. Is there a better way to do this?

Thanks in advance!

Yes, anyting that can be normalized is. However, there are varchar fields to enter things like a free-text Description. As such I need to be able to store both numbers and varchars. Two different tables?

Mike 2010-03-07 02:20:59

What I mean is that if a 5 from one user = 10% from another, store them both as 5 or 10 and have a text field on the same table that shows the original text. eg User=1, Vote=5, Text="5"; User=2, Vote=5, Text="10%". Make sense?

pdr 2010-03-07 04:07:54

I see what you're saying... though this assumes that the all the responses are for the same question. Sample Tables: Metric_ID=3, User_ID=2, Value='3-2-2010'; Metric_ID=2, User_ID=4,Value='55.00'; where the metric table would look something like, Metric_ID=3, Metric_Desc='Signoff Date', Type='varchar'; Metric_ID=2, Metric_Desc="Cash", Type='Money'. I want to allow users to be able to cusomize their Metrics to be whatever they need. Then for those that will be used in calcuations, currenly I'm converting the varchar to whatever I need. It works, but feels sloppy, and requires validation.

Mike 2010-03-07 19:09:26

Right, I see what you're trying to do now, and I think you've probably taken the same approach I would.

pdr 2010-03-07 19:20:35

ansaurus

tags:

views:

answers:

Storing statistics of multple data types in SQL Server 2008

related questions