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 answercloze tests or gap texts: Slow loris are of the genus [Nycticebus] and of the subfamily [Lorinae] - one question & many answers!My schemaThe part in gray ships with the TankAuth component of CodeIgniter and I'm hoping that this is properly done already. Database schemaIn response to a now-gone comment by l0bo, I visualized this in SchemaSpy as well.Common queriesThese 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` ASSELECT   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 3NFFROM  user_terms AS uLEFT JOIN learnables AS l ON u.learnable_id = l.idLEFT JOIN terms ON l.id = terms.learnable_idLEFT JOIN gaps ON l.id =  gaps.learnable_id LEFT JOIN clozetests AS c ON c.id = gaps.cloze_idCREATE VIEW `how_many_due` ASSELECT COUNT(id) FROM user_termsWHERE stage < 5 AND DATE(time_due) <= CURDATE()CREATE VIEW `due_terms` ASSELECT   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 3NFFROM  user_terms AS uLEFT JOIN learnables AS l ON u.learnable_id = l.idLEFT JOIN terms ON l.id = terms.learnable_idLEFT JOIN gaps ON l.id =  gaps.learnable_id LEFT JOIN clozetests AS c ON c.id = gaps.cloze_idWHERE DATE(u.time_due) <= CURDATE() AND u.stage < 5 ORDER BY terms.language, terms.field, u.id
Database design for an online learning application
mysql;sql;codeigniter