您可以为每个数据类型单独的表。

以获取整个表单，您将使用表单id执行N路连接，其中N是您支持的不同数据类型的数量（+可能额外根据您想要的信息 - 例如下拉值可能会存储在另一个表/您的字段名查找/等）

但是设计应该也取决于你打算如何使用数据，你没有说什么。这也将取决于这些形式的变化速度有多快。 。 。

Database design question for y'all. I have a form (like, the paper kind) that has several entry points for data. This form has changed, and is expected to change over years. It is being turned into a computer app, so that we can, among other things, quit wasting paper. (And minor things, like have all the data in one central store that can be queried, etc.) I'd like to store all of the forms data in a database, and have it be pretty agnostic as to the changes.

Originally, I was just considering each field to be a string -- and I had a table something like this:

FormId int (FK)
FieldName nvarchar(64)
FieldValue nvarchar(128)

...something like that. It was actually a bit more 3NFy in that FieldName was in another table, associated with an artificial key, so that the field names weren't duplicated all over the place.

However, I'd like to extend this to numeric and drop-down data. I could just store numeric data as strings, but that seems like a pretty crappy idea. Same with drop downs.

I could stop using a table, and actually use columns on the main form table (the one that FormId above references), but that means adding a column for each new item as they come along, and older forms would just be null. (And, unless I stored it, I wouldn't know when that column was created. With the string table above, it's implicit.)

I could extend the table above to something like:

FormId int (FK)
FieldName nvarchar(64)
FieldValueType int -- enum as to which of the columns below are valid (or just let nulls imply that)
FieldValue nvarchar(128)
FieldValueInt int

Combos would have to be in a OTLT (one true lookup table), which I have reservations about, but perhaps it's needed here?

Any advice on ***? I'm using MSSQL, but this is really a more general question.

You could have a separate table for each datatype.

I.e. to fetch an entire form you'd do an N-way join using the form id where N is the number of distinct datatypes you support (+ perhaps extras depending on the info you want - e.g. dropdown values would probably be stored in another table / your fieldname lookup / etc.)

But the design should probably also depend on how you intend to use the data, which you've said nothing about. And it would also depend on just how fast the rate of change is for these forms . . .