gistfile1.txt

DROP TABLE IF EXISTS dictionary;
CREATE TABLE dictionary (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) UNIQUE NOT NULL
) ENGINE=InnoDB;

DROP TABLE IF EXISTS dictionary_batch;
CREATE TABLE dictionary_batch (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dictionary_id INT UNSIGNED NOT NULL REFERENCES dictionary(id),
    name VARCHAR(50) NOT NULL,
    UNIQUE INDEX names_per_dictionary (name, dictionary_id)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS word;
CREATE TABLE word (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    lemma VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    note TEXT
) ENGINE=InnoDB;

DROP TABLE IF EXISTS user;
CREATE TABLE user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
) ENGINE=InnoDB;

DROP TABLE IF EXISTS word_suggestion;
CREATE TABLE word_suggestion (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    word_id INT UNSIGNED NOT NULL REFERENCES word (id),
    suggested_by_user INT UNSIGNED NOT NULL REFERENCES user (id),
    pos TEXT NOT NULL,
    definition TEXT NOT NULL,
    paper BOOL NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
) ENGINE=InnoDB;

--- Note: This part left undecided as definition of what you want
---       to model unclear to me.
--- origin" flag (e.g.
--- the name of the person who suggested it; the fact that 
--- it was found on a scan of other dictionaries; a suggestion
--- from the general public; etc.
--- don't know if this has to be different than "suggestor" (which
--- would only be for internal suggestions)).

DROP TABLE IF EXISTS dictionary_batch_word;
CREATE TABLE dictionary_batch_word (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dictionary_batch_id INT UNSIGNED NOT NULL REFERENCES dictionary_batch(id),
    word_id INT UNSIGNED NOT NULL REFERENCES word(id),
    UNIQUE INDEX words_per_dictionary (dictionary_batch_id, word_id)
)  ENGINE=InnoDB;