CREATE TABLE "Material"
(
id INTEGER,
expression TEXT,
expression_meaning TEXT,
expression_reading TEXT,
expression_source TEXT
focus_phrase TEXT,
focus_phrase_meaning TEXT,
notes TEXT
)
I love spaced-repetition systems. I've been an extremely early Anki user, all the way back to 2006.
Yet nothing beats rote language drills to build automaticity.
So I've previously written language drilling apps to help me learn languages.
I've experimented with many possible schemas; yet I've come to prefer storing simple sentence-level units in SQL databases.
This to me is the simplest single-table definition schema to hold learning materials across different languages. (It's great because it also works as a reference for Anki deck fields.)
expression
Represents a grammatical phrase, expression or a sentence fragment.
Written in Japanese/other languages.
expression_meaning
Closest translation. Not the 'explanation' (which goes into notes).
expression_reading
Phonetic reading. (For Japanese, in hiragana/katakana.)
expression_source
Reference - e.g. specific texts/documents/websites.
focus_phrase
A specific word, or target grammar point, within the expression
.
If present, it is the focal point of the expression.
focus_phrase_meaning
Meaning of the highlighted phrase/grammar point.
Different from expression_meaning
.
notes
Supplementary details; usage tips, exceptions, or cultural nuances.