I have a table in my database that represents datafields in a custom form. The DataField gives some representation of what kind of control it should be represented with, and what value type it should take. Simplified you can say that I have 2 entities in this table - Textbox taking any string and Textbox only taking numbers.

Now I have the different values stored in a separate table, referencing the datafield definition. What is the best way to store the data value here, when the type differs?

One possible solution is to have the FieldValue table hold one field per possible value type. Now this would certainly be redundant, but at least I would get the value stored in its correct form - simplifying queries later.

FieldValue
----------
Id
DataFieldId
IntValue
DoubleValue
BoolValue
DataValue
..

Another possibility is just storing everything as String, and casting this in the queries. I am using .Net with NHibernate, and I see that at least here there is a Projections.Cast that can be used to cast e.g. string to int in the query.

Either way in these two solutions I need to know which type to use when doing the query, but I will know that from the DataField, so that won't be a problem.

Anyway; I don't think any of these solutions sounds good. Are they? Or is there a better way?