数据库设计：将数据从纸质表存储到数据库中 [英] Database design: Store data from paper forms in database

问题描述

y'all的数据库设计问题。我有一个形式（像，纸种），有几个数据的入口点。这种形式已经改变，预计将改变多年。它正在变成一个计算机应用程序，以便我们可以，除其他外，退出浪费纸。 （和小的东西，像在一个中央存储中的所有数据，可以查询等）。我想将所有的表单数据存储在一个数据库，并且它对于变化是非常不可知的。 p>

最初，我只是考虑每个字段是一个字符串 - 我有一个表，像这样：

  FormId int（FK）
 FieldName nvarchar（64）
 FieldValue nvarchar（128）
  

...这样的东西。它实际上有点多3NFy，FieldName在另一个表中，与人工键相关联，以便字段名称不会重复的地方。

但是，我想将其扩展为数字和下拉数据。我可以只存储数字数据作为字符串，但这似乎是一个很肮脏的想法。与下拉列表相同。

我可以停止使用表，实际上使用主表单表（上面引用的FormId）上的列，但这意味着为每个新项目添加列因为他们来了，老的形式将只是null。 （和，除非我存储它，我不知道该列创建的时间。）使用上面的字符串表，它是隐含的。）

类似于：

  FormId int（FK）
 FieldName nvarchar（64）
 FieldValueType int  - 枚举下面的哪些列是有效的（或者只是让null表示）
 FieldValue nvarchar（128）
 FieldValueInt int 
  

Combos必须在一个OTLT（一个真正的查找表），我有保留，但也许这里需要？

有关StackOverflow的任何建议吗？我使用MSSQL，但这是一个更一般的问题。

解决方案

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

以获取整个表单，您将使用表单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 StackOverflow? 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 . . .

这篇关于数据库设计：将数据从纸质表存储到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！