3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ could you add the Schema picture to the question rather than a link? or along with a link? \$\endgroup\$ – Malachi Dec 17 '13 at 14:41
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – Ruben Mar 1 '11 at 16:56
  • \$\begingroup\$ 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. \$\endgroup\$ – Fge Mar 1 '11 at 17:29
  • \$\begingroup\$ 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? \$\endgroup\$ – Ruben Mar 1 '11 at 17:50
  • \$\begingroup\$ As said, it's absolutley ok if you are fine with languages being an atomic and unique. \$\endgroup\$ – Fge Mar 1 '11 at 19:34
  • \$\begingroup\$ why would you do this? Explaining it (even a little bit) makes for a better review \$\endgroup\$ – Malachi Dec 17 '13 at 14:40
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.