DB structure

Masintõlge DB structure

UML diagram

TODO add diagram

Code for recreating DB schema

Translators

CREATE TABLE translators (
    translator_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    translator_title VARCHAR(15) NOT NULL
);

Language pairs

CREATE TABLE language_pairs (
    language_pair_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    language_from VARCHAR(3) NOT NULL,
    language_to VARCHAR(3) NOT NULL
);

Source

CREATE TABLE source (
    source_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_text VARCHAR(5000) NOT NULL,
    language_pair_id INT(10) UNSIGNED,
    FOREIGN KEY (language_pair_id) REFERENCES language_pairs(language_pair_id)
);

Translations

CREATE TABLE translations (
    translation_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    translaion_text VARCHAR(5000) NOT NULL,
    translator_id INT(10) UNSIGNED NOT NULL,
    source_id INT(10) UNSIGNED NOT NULL,
    FOREIGN KEY(translator_id) REFERENCES translators(translator_id),
    FOREIGN KEY(source_id) REFERENCES source(source_id)
);

Best translations

CREATE TABLE best_translations (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_id INT(10) UNSIGNED NOT NULL,
    translator_id INT(10) UNSIGNED NOT NULL,
    FOREIGN KEY(source_id) REFERENCES source(source_id),
    FOREIGN KEY(translator_id) REFERENCES translators(translator_id)
);