3

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 clozetests table)
  • 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_gaps analogue to user_terms, because then I'd be in the same pickle when it comes to the table user_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. Cutout of my schema diagram

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.

Ruben
  • 3,452
  • 31
  • 47
  • The main problem here is to tell a story. Who, what, where when and why? It is learnable, but it is test -- well, maybe. Who is taking a test? Is one close-text the whole test? Are there many questions in a test? Can one term be repeated in many gaps? Is there a score? Can the test be taken several times? Languages? What kind of a test is this? – Damir Sudarevic Feb 01 '11 at 20:43
  • @Damir Sudarevic It's "simple" repetition learning. Gaps and Terms are the same "unit". Gaps have a higher order unit, a gap text, in which they are included (so in a way the gap text constitutes the question part of many gaps, while for a term one question maps to one answer. It's not a test for evaluating people. I think I'll put my schema on CodeReview when I'm finished, then I'll explain it better. – Ruben Feb 01 '11 at 22:05

2 Answers2

2

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.

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Thanks, but I don't really recognise my schema in this setup anymore. Maybe the biggest problem in understanding is the term *cloze test*. This was supposed to be a synonym of **gap text**, which is just something like: Corvus cornix is a member of the family `_______` and the order `__________` where the user would have to enter Corvidae and Passeriformes respectively. But the sentence is the "question" for multiple answer-fields. Actually the text in the DB would not contain `_____` but [Corvidae] instead. The app masks it. A term is simply something like: *Fuß in German is in English*: Foot – Ruben Feb 01 '11 at 23:29
  • I'm actually okay with my solution below though. I'll post my full schema (including the users table) on CodeReview, when it's more complete and then I'll **make sure** that I tell a good story. I guess after brooding over this for so long, it seemed obvious. Thanks a lot for your input, even if I can't apply it to my problem anymore. I'd love to have your eyes on it, when I properly lay it out there. – Ruben Feb 01 '11 at 23:37
1

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

enter image description here

Community
  • 1
  • 1
Ruben
  • 3,452
  • 31
  • 47