Database design for an online learning application

It is an online learning application with different types of learnable items, that different users learn.

The users learn by spaced repetition (once right, wait a couple days, ask again), success is mirrored in stages and mistakes made.

I intend to use this for scientific data collection in the near future, so there is one table especially, user_terms_answers where data amasses at a granularity not currently used by the application.

I especially had trouble with figuring out how to arrange my learnable types, and I'm not sure if I chose the optimal solution.

Since this is potentially not understood just from looking at the chart, the two basic types are:

• known – unknown: What year did Thomas Edison die? – [1931] - one question & one answer

• cloze tests or gap texts: Slow loris are of the genus [Nycticebus] and of the subfamily [Lorinae] - one question & many answers!

My schema

The part in gray ships with the TankAuth component of CodeIgniter and I'm hoping that this is properly done already.

Database schema

In response to a now-gone comment by l0bo, I visualized this in SchemaSpy as well.

Common queries

These the views that I have so far, they reflect quite well what the application will usually want.

They also reflect how I run into trouble with the way I chose to implement "languages".

``
CREATE VIEW retrieve_user_term_or_gap AS
SELECT
u.id, u.learnable_id, u.user_id, u.stage, u.mistakes, u.time_due, u.added,
terms.language, terms.field, terms.known, terms.unknown, terms.hint,
gaps.cloze_id, gaps.gap,
c.language AS cloze_language, c.field AS cloze_field, c.cloze,
c.hint AS cloze_hint -- this is bad, not 3NF
FROM
user_terms AS u
LEFT JOIN learnables AS l ON u.learnable_id = l.id
LEFT JOIN terms ON l.id = terms.learnable_id
LEFT JOIN gaps ON l.id = gaps.learnable_id
LEFT JOIN clozetests AS c ON c.id = gaps.cloze_id

CREATE VIEW how_many_due` AS
SELECT COUNT(id) FROM user_terms
WHERE stage < 5 AND DATE(time_due) <= CURDATE()

CRE

You do have the field languages several times as a string. I'd create a general table languages with all language-related informations and have 1:m or n:m relations to other tables.

StackExchange Code Review Q#1041, answer score: 2