30 lines
843 B
SQL
30 lines
843 B
SQL
CREATE TABLE words (
|
|
id TEXT PRIMARY KEY,
|
|
headword TEXT NOT NULL,
|
|
language_code TEXT NOT NULL,
|
|
pos TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
UNIQUE(headword, language_code, pos)
|
|
);
|
|
|
|
CREATE TABLE senses (
|
|
id TEXT PRIMARY KEY,
|
|
word_id TEXT NOT NULL REFERENCES words(id),
|
|
sense_index INTEGER NOT NULL DEFAULT 0,
|
|
difficulty TEXT NOT NULL,
|
|
definitions TEXT NOT NULL DEFAULT '[]',
|
|
examples TEXT NOT NULL DEFAULT '[]',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
UNIQUE(word_id, sense_index)
|
|
);
|
|
|
|
CREATE TABLE translations (
|
|
id TEXT PRIMARY KEY,
|
|
sense_id TEXT NOT NULL REFERENCES senses(id),
|
|
target_language_code TEXT NOT NULL,
|
|
translation TEXT NOT NULL,
|
|
gender TEXT,
|
|
difficulty TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
UNIQUE(sense_id, target_language_code, translation)
|
|
);
|