We have a system where analysts can upload various financial metrics about a stock from Excel to SQL2005.

Multiple clients use the system and each require collecting differing metrics.

Currently we have all of the metrics as columns in a table. Each client obviously has a different table structure.

Not all of the metrics are relevant to all sectors so that means that for some stocks we will insert a null value where the metric is not relevant.


I have considered splitting out the metrics into a separate table and then having a matrix table of UploadId, MetricId, MetricValue.


One problem however is that the metrics are not all the same data type, many are numeric but there are some strings as well.

Also for the numeric ones some of them are currency and we now have a new requirement to handle different currencies and so need to start storing the currency unit along with the value in some way.

As far as I can see the options are.

1) Keep the structure as is. Add a new CLR UDT of Currency and store the unit along with the value in one column.

2) Have a matrix table of UploadId, MetricId, MetricValue with sqlvariant datatype.

3) Have several matrix tables of UploadId, MetricId, MetricValue for different datatypes. For the currency one also include a currency unit field. The code to retrieve metric values would then presumably need to either include all look up tables or have some sort of conditional logic to select from the correct one.

I'm unsure which is the "best" option. All suggestions welcome!