如何构建我的数据库，以便构成相同“元素”的两个表可以存储在数据库中。链接到另一个？
我阅读了有关数据库结构化和规范化的内容，并决定重新构建我的学习内容背后的数据库以减少冗余。
我有不同类型的条目可以学习。 间隙文本/完形填空测试（一篇文本，许多间隙）和简单的已知-未知（一个问题，一个答案）类型。
现在我有点困惑：
- 间隙需要用户表中与问题答案类型完全相同的列
- ，但它们需要的列少于问题答案类型（所有信息都在
clozetests表） - 我希望有一个“神奇”外键，它可以同时指向间隙和术语表。当然，他们的 ID 会重叠。我不喜欢在 user_terms 中同时包含 term_id 和 gap_id ，这看起来不太优雅（但这是我在谷歌搜索一段时间后能想到的最优雅的方法，不知道这个pickle的名字）。
- 我不想要
user_gaps与user_terms类似，因为这样当涉及到表user_terms_answers时我就会陷入同样的困境。
我把我的模式的纸板剪纸拼贴画贴出来了。我没有删除与这个问题无关的内容，但如果任何人的困惑都可以得到这样的补救，我可以这样做。我认为它看起来已经超级整洁了。至少比我的心理概念更整洁。
我是否说过任何帮助将不胜感激？回答者可能会发现自己因其智慧而受到称赞。 
背景故事如果您关心的话，它与问题并不真正相关。
在重构之前，我将它们全部放在一张表中（因为我匆忙添加了间隙文本），因此间隙文本是没有答案的“正常”项目，而间隙则是没有问题的项目。该应用程序将它们连接在一起。
编辑
我在提出一些有用的帖子后添加了一个答案。我还没有100%满意。我尝试时不时地为这个设置编写常见查询的视图，我觉得我必须为数据库领域的某些东西提取应用程序逻辑。
I read up on database structuring and normalization and decided to remodel the database behind my learning thingie to reduce redundancy.
I have different types of entries that can be learned. Gap texts/cloze tests (one text, many gaps) and simple known-unknown (one question, one answer) types.
Now I'm in a bit of a pickle:
- gaps need exactly the same columns in the user table as question-answer types
- but they need less columns than question-answer types (all that info is in the
clozeteststable) - I'm wishing for a "magic" foreign key that can point both to the gap and the terms table. Of course their ids would overlap though. I don't like having both a term_id and gap_id in the user_terms, that seems unelegant (but is the most elegant I can come up with after googling for a while, not knowing what name this pickle goes by).
- I don't want a
user_gapsanalogue touser_terms, because then I'd be in the same pickle when it comes to the tableuser_terms_answers.
I put up this cardboard cutout collage of my schema. I didn't remove the stuff that isn't relevant for this question, but I can do that if anyone's confusion can be remedied like that. I think it looks super tidy already. Tidier than my mental concept of this at least.
Did I say any help would be greatly appreciated? Answerers might find themselves adulated for their wisdom.
Background story if you care, it's not really relevant to the question.
Before remodeling I had them all in one table (because I added the gap texts in a hurry), so that the gap texts were "normal" items without answers, while the gaps where items without questions. The application linked them together.
Edit
I added an answer after SO coughed up some helpful posts. I'm not yet 100% satisfied. I try to write views for common queries to this set up now and again I feel like I'll have to pull application logic for something that is database turf.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如评论中提到的，在不了解整个故事的情况下很难回答。所以，这是一个故事和一个相匹配的模型。看看您是否可以将此适应您的示例。
（外语）语言学院提供多个语言水平的考试。学校为每种语言的每个级别保留了许多预制测试 (
LangLevelTestNo)。每个测试包含几个（许多）问题。每个问题可以是简单或封闭文本类型。每个简单问题都会存储正确答案。为每个封闭文本问题的每个间隙存储正确的术语。
学生可以参加语言级别考试，并接受一项预先制作的测试。对于每次学生考试，都会维护考试表格，其中存储学生对考试中每个问题的答案考试。与问题一样，答案可以是简单的闭合文本类型。
As mentioned in the comment, it is hard to answer without knowing the whole story. So, here is a story and a model to match. See if you can adapt this to you example.
School of (foreign) languages offers exams for several levels of language proficiency. The school maintains many pre-made tests for each level of each language (
LangLevelTestNo).Each test contains several (many) questions. Each question can be simple or of the close-text-type. Correct answers are stored for each simple question. Correct terms are stored for each gap of each close-text question.
Student can take an exam for a language level and is presented with one of the pre-made tests. For each student exam, the exam form is maintained which stores students answers for each question of the exam. Like a question, an answer may be of a simple of of a close-text-type.
编辑我的问题后，一些 Stackoverflow 开始向我提出正确的问题。
我知道这是一个常见问题，但我真的找不到它，我猜只是想不出正确的搜索词。
以下线程解决了类似的问题，我将尝试将该逻辑应用到我自己的设计中。他们都建议为类似的项目添加更高级别的描述（在我的例子中是术语和差距）。这是有道理的，反映了我的应用程序背后的逻辑。
应用此功能后，我将用编辑后的架构发回。这样看起来确实更优雅。
编辑架构
After editing my question some Stackoverflow started relating the right questions to me.
I knew this was a common problem, but I really couldn't find it, just couldn't come up with the right search terms, I guess.
The following threads address similar problems and I'll try to apply that logic to my own design. They all propose adding a higher-level description for (in my case terms and gaps) like items. That makes sense and reflects the logic behind my application.
I'll post back with my edited schema once I've applied this. It does seem more elegant like this.
Edited schema