数据库设计：问答灵活存储

发布于 2024-11-07 15:24:16 字数 914 浏览 0 评论 0原文

我有一段时间没有设计数据库了，现在对我的设计没有太大信心。我基本上在数据库中有三个表来代表某种历史记录。我必须更改此系统，以便每次添加记录（例如某些内容成为历史记录）时，可能需要用户输入一些信息。有时他们只会提出一个简单的问题，有时他们会被要求提供五种不同的信息，而这些信息需要由最终用户通过管理前端灵活管理。所以我计划有一个问题表和答案表，以及将它们与其他三个表联系起来的复合表。我正在努力设计答案表，因为每个问题可能需要多种答案。一些响应将是通过外键绑定到另一个数据库的下拉选择。其他可能是文本输入、日期或是/否答案。现在，我有一个答案表，其中包含所有类型的答案以及一系列可为空的字段...

+----------------------------+
| Answer                     |
+----------------------------+
| Id (int)                   |
| QuestionId (int)           |
| ForeignKeyId1* (int)       |
| ForeignKeyId2* (int)       |
| ForeignKeyId3* (int)       |
| Number* (bigint)           |
| DateField* (date)          |
| Text* (varchar 500)        |
| YesNo* (bit)               |
+----------------------------+
  *Nullable

附注：问题表与 QuestionType 相关联，该 QuestionType 将指示（在应用程序内）如何验证用户输入。输入将存储在应答记录中相应的可为空字段中，而其他字段则为空。我认为这比没有数据完整性的 catch all varchar 答案字段更好。

这是一个糟糕的设计吗？什么可以让它变得更好？

原文

I haven't designed a database in a while and I don't have a lot of confidence in my design right now. I basically have three tables in a database that represent a history of sorts. I have to change this system so that each time a record is added (e.g. something becomes historical), some input from the user may be required. Sometimes they will just have one simple question, other times they will be required to give five different pieces of information, and these need to be flexibly managed by an end user through an administrative front-end. So I plan to have a Question table and Answer table, with composite tables to tie these to the other three tables. I am struggling with the design the Answer table, because each question could require a variety of responses. Some responses will be drop-down selections that tie to another database via foreign key. Others could be text input, dates, or Yes/No answers. Right now, I have an answer table that encompasses all types of answers with a series of nullable fields...

+----------------------------+
| Answer                     |
+----------------------------+
| Id (int)                   |
| QuestionId (int)           |
| ForeignKeyId1* (int)       |
| ForeignKeyId2* (int)       |
| ForeignKeyId3* (int)       |
| Number* (bigint)           |
| DateField* (date)          |
| Text* (varchar 500)        |
| YesNo* (bit)               |
+----------------------------+
  *Nullable

Side note: the question table ties to a QuestionType that will dictate (within the application) how to validate the user input. The input gets stored in the appropriate nullable field in the Answer record, with the other fields nulled out. I thought this would be better than having a catch all varchar answer field with no data integrity.

Is this a poor design? What could make it better?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月无声 2024-11-14 15:24:16

根据您的描述，您的解决方案看起来应该可以正常工作。我对表进行了类似的设置，其中的列设置为记录特定类型的数据，允许其他字段可为空。对我来说看起来不错，尽管我不确定您的foreignkeyid字段的用途：\

回复收藏 0 原文

单调的奢华 2024-11-14 15:24:16

对于不同的数据类型有不同的字段是可以的，就像有多个外键一样。这种设计将尽可能多的数据域完整性推入 DBMS。从哲学上讲，这是一个很好的方法，因为大多数人会告诉您，让 DMBS 为您完成工作将节省您编写和维护代码的时间。大多数时候我都会是那些人中的一员。

从可维护性的角度来看，还有另一种方法。按照目前的情况，如果您添加一种新的数据类型（例如浮点数或 GUID），您将必须返回并修改 ANSWER 表的结构。同样，如果您创建另一个需要从新查找表中获取答案的问题类型，则必须返回并添加另一个 FK 字段。

您可以更改您的 ANSWER 表以使用包罗万象的方法，这样它看起来更像这样：

ANSWER
( Id (int)
, QuestionId (int)
, Part (int)
, Value (nvarchar 1000)
)

如果我正确理解您的问题，一个问题可能有一个多部分答案。假设单个问题可能有多个答案，并且您的问题控制表知道哪个部分是哪个部分，您将使用“部分”字段来区分这些部分。如果每个问题都有零个或一个答案，那么您不需要答案表，只需将答案添加到问题中即可。

那么，为什么您可能想要放弃让数据库强制执行数据域完整性呢？以下是相对的优缺点：

多个单独答案字段的优点：

DBMS 不会让您将错误类型的数据放入字段中，因此您无需写入或调用任何数据存储数据之前的域完整性函数（除非您想在 GUI 级别捕获这些错误）
对于如何解释答案的内容没有任何混淆（例如，此日期答案中的月份和日期...... )

Catch All 的优点答案字段：

您不必编写额外的代码
查看问题类型即可了解
ANSWER 表中的哪一列
阅读任何给定类型的问题。
你不必改变你的
数据库模式/物理数据库
每次新查找时的人口数量
添加表或答案数据类型。

无论哪种情况，您都必须编写一些代码来处理在一个数据存储中存储多个问题的语义上不同的答案这一事实。您必须决定要编写哪种类型的额外代码，哪种类型可以确定在哪里放置和查找每种类型的答案，或者哪种类型可以确定如何使用通用表示（即字符串）来存储和解释不同的数据类型）。

考虑到修改包含数据的表是一件痛苦的事情，并且考虑到大多数编程语言都内置了相当强大的 .ToString() / .TryParse() 类型功能，如果我的主要方法是使用包罗万象的方法，我会倾向于使用包罗万象的方法关心的是可维护性。

Having different fields for different data types is OK, as is having multiple foreign keys. This design pushes as much of the data domain integrity as possible into the DBMS. This is philosophically a good way to go, as most people would tell you that letting the DMBS do the work for you will save you writing and maintaining code. I would be one of those people most of the time.

There is another way to look at this from a maintainability aspect. As it stands, you will have to go back and modify the structure of your ANSWER table if you add either a new kind of data type, like a floating point number or a GUID for example. Similarly, you'll have to go back and add another FK field if you make another question type that requires an answer from a new lookup table.

You could change your ANSWER table to look to use the catch-all approach so it would look more like this:

ANSWER
( Id (int)
, QuestionId (int)
, Part (int)
, Value (nvarchar 1000)
)

If I understood your question correctly, a question could have a multi-part answer. Assuming that there are potentially multiple answers for a single question, and that your question control tables know which part is which, you would use the Part field to distinguish the parts. If each question has exactly zero or one answers, then you don't need an answer table, you can just add the answer to the question.

So, why might you want to give up on letting the database enforce data domain integrity? Here are the relative pros and cons:

Advantages of Multiple Separate Answer Fields:

DBMS won't let you put the wrong kind of data into a field, so you don't need to write or call any data domain integrity functions before storing the data (unless you want to trap these errors at the GUI level)
There's no confusion about how to interpret the contents of an answer (e.g. which is the month and which is the day in this date answer...)

Advantages of a Catch All Answer Field:

You don't have to write extra code to
look at the question type to find out
which column in the ANSWER table to
read for any given type of question.
You don't have to change your
database schema/physical database
population every time a new lookup
table or answer data type is added.

In either case you have to write some code to handle the fact that you are storing semantically different answers to multiple questions in one data store. You have to decide which type of extra code you want to write, the kind that figures out where to put and find each type of answer, or the kind that figures out how to store and interpret different data types using a common representation (i.e. string).

Given that modifying a table which has data in it is a pain, and given that most programming languages have pretty robust .ToString() / .TryParse() type functionality built in, I would lean towards using the catch-all approach if my primary concern were maintainability.

回复收藏 0 原文

~没有更多了~