# 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()

CREATE VIEW due_terms 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
WHERE DATE(u.time_due) <= CURDATE() AND u.stage < 5
ORDER BY terms.language, terms.field, u.id

• could you add the Schema picture to the question rather than a link? or along with a link?
– Malachi
Dec 17 '13 at 14:41

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.

• I thought about this, but said general table would contain only the language and possibly a surrogate key. The user-language table would still be necessary. But I'm also having a hard time to name the pros and cons. Is this not normalized as it is? Mar 1 '11 at 16:56
• As long as you do use the language-string as an atomic type it's ok. But you can't add additional propertys to a language then (as i assume special_chars is) you add redundancy.
– Fge
Mar 1 '11 at 17:29
• Well, special_chars is a property of the user-language combination, so that table would still be there, if a general language table existed. A user-independent table like terms  and clozetests would only contain the language. One pro would be, though, that if I ever add user-independent properties to languages, I'd have something to build on. However, the only thing I can think of now, is tags, and these would again require a different table. Is this a textbook example for using a natural key? Mar 1 '11 at 17:50
• As said, it's absolutley ok if you are fine with languages being an atomic and unique.
– Fge
Mar 1 '11 at 19:34
• why would you do this? Explaining it (even a little bit) makes for a better review
– Malachi
Dec 17 '13 at 14:40

You have really obscure table aliases, why?

Don't alias your table names just to alias your table names, that isn't the purpose of aliases.

If you have a lengthy table name or you are calling tables from several databases where you need to call the table like database1name.dbo.tablename1 then that is when you should use a table alias, so that you don't have to type the whole table location in the query.

Do not use just one character for the alias, that doesn't tell you what information the table holds, make the alias meaningful.

All of the Left Joins look a little weird to me, but I am sure it is necessary.

Personally I wouldn't use Views, I would probably use Functions or Stored Procedures. but then again I don't normally code in MySQL so I really don't know if that would be best for that Engine.

Still, I would see if I could use functions or stored procedures.