Avoid sql_variant but use what?

Question

My database holds template forms and real forms having values. Users will be able to create custom template forms with different types of fields. I will keep values in a separate table.

Forms table:

Id | Name | TemplateId

FormFields table:

Id | FormId | Name | ValueType (nvchar)

Values Table

FieldId | Value

When user designs a form it is saved into forms table having TemplateId NULL. Also FormFields table stores the fields that will be used for this form. Later when user creates a real form using that template, the real values of this form (derieved from FormFields) will be stored in Values table. Now, the value column in Values table seems to be sql_variant type. If I don't use sql_variant how can I solve this problem?

Ps: What about creating different tables for each kind of values? TableIntValues, TableBoolValues etc?

http://www.jotform.com/ can be a good sample for my project.

score 0 · Accepted Answer · answered Apr 10 '13 at 12:11

0

I would suggest using separate columns for the separate data types, along these lines:

FieldId | StringValue | IntegerValue  |  DateTimeValue

That way you can have queries run over the data sensibly, and also keep it type safe. You would obviously need logic to ensure that the data gets populated correctly, and a constraint to ensure only one column is populated and not all are NULL.

answered Apr 10 '13 at 12:11

Paul Grimshaw

19,894
6
40
59

Thanks. Is that a general solution or just an idea? – aliassce Apr 10 '13 at 12:53
There may be intricacies in your application that will mean the above pattern falls down, but I have used it successfully for similar work. – Paul Grimshaw Apr 10 '13 at 13:49

Avoid sql_variant but use what?

1 Answers1