সম্মিলিত কাজের জন্য একটি প্রস্তাবনা 5 পাতা থেকে পাতা 2

# ---------------------------------------------------------------------- #
# Project file:          beng_dict.dez                                   #
# Project name:          beng_eng_dict                                   #
# Target DBMS:           MySQL 5                                         #
# Author:                invarBrass                                      #
# Created on:            2010-10-23 20:23                                #
# ---------------------------------------------------------------------- #


# ---------------------------------------------------------------------- #
# Tables                                                                 #
# ---------------------------------------------------------------------- #

# ---------------------------------------------------------------------- #
# Add table "dictionary"                                                 #
# ---------------------------------------------------------------------- #

CREATE TABLE `dictionary` (
    `dict_id` INTEGER NOT NULL AUTO_INCREMENT,
    `dict_name` VARCHAR(40) NOT NULL UNIQUE,
    CONSTRAINT `PK_dictionary` PRIMARY KEY (`dict_id`)
);

CREATE INDEX `IX_DICT_NAME` ON `dictionary` (`dict_name`);

# ---------------------------------------------------------------------- #
# Add table "words_main"                                                 #
# ---------------------------------------------------------------------- #

CREATE TABLE `words_main` (
    `word_id` INTEGER NOT NULL AUTO_INCREMENT,
    `dict_id` INTEGER NOT NULL,
    `original_word` VARCHAR(40) NOT NULL,
    `phoneme` VARCHAR(40) DEFAULT 'NULL',
    `review_status` ENUM(PENDING, ACCEPTED, REJECTED) DEFAULT 'PENDING',
    `added_on` DATE NOT NULL DEFAULT NOW(),
    `comments` VARCHAR(40) DEFAULT 'NULL',
    CONSTRAINT `PK_words_main` PRIMARY KEY (`word_id`)
);

CREATE INDEX `IX_words_orig_word` ON `words_main` (`original_word` ASC);

CREATE INDEX `IX_words_status_time` ON `words_main` (`review_status`,`added_on`);

ALTER TABLE `words_main` ADD CONSTRAINT `CC_words_main_original_word` 
    CHECK (UNIQUE);

# ---------------------------------------------------------------------- #
# Add table "word_synonyms"                                              #
# ---------------------------------------------------------------------- #

CREATE TABLE `word_synonyms` (
    `word_id` INTEGER NOT NULL,
    `contributor_id` INTEGER,
    `synonym` VARCHAR(40) NOT NULL,
    `review_status` ENUM(PENDING, ACCEPTED, REJECTED) NOT NULL DEFAULT 'PENDING',
    `last_updated` TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX `IX_word_synonyms_word` ON `word_synonyms` (`word_id`);

CREATE INDEX `IX_word_synonyms_status_time` ON `word_synonyms` (`review_status`,`last_updated`);

# ---------------------------------------------------------------------- #
# Add table "word_antonyms"                                              #
# ---------------------------------------------------------------------- #

CREATE TABLE `word_antonyms` (
    `word_id` INTEGER NOT NULL,
    `contributor_id` INTEGER,
    `antonym` VARCHAR(40) NOT NULL,
    `review_statss` ENUM(PENDING, ACCEPTED, REJECTED) NOT NULL DEFAULT 'PENDING',
    `last_updated` TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX `IX_word_antonyms_word` ON `word_antonyms` (`word_id`);

CREATE INDEX `IX_word_antonyms_status_time` ON `word_antonyms` (`review_statss`,`last_updated`);

# ---------------------------------------------------------------------- #
# Add table "word_translations"                                          #
# ---------------------------------------------------------------------- #

CREATE TABLE `word_translations` (
    `word_id` INTEGER NOT NULL,
    `pos_id` INTEGER NOT NULL,
    `contributor_id` INTEGER DEFAULT NULL,
    `translation_entry` VARCHAR(80) NOT NULL,
    `review_status` ENUM(PENDING, ACCEPTED, REJECTED) DEFAULT 'PENDING',
    `last_updated` TIMESTAMP DEFAULT NOW(),
    `comments` VARCHAR(40) DEFAULT 'NULL'
);

CREATE INDEX `IX_trans_word` ON `word_translations` (`word_id`);

CREATE INDEX `IX_trans_time_status` ON `word_translations` (`last_updated`,`review_status`);

# ---------------------------------------------------------------------- #
# Add table "parts_of_speech"                                            #
# ---------------------------------------------------------------------- #

CREATE TABLE `parts_of_speech` (
    `pos_id` INTEGER NOT NULL,
    `pos_name` VARCHAR(40) NOT NULL,
    CONSTRAINT `PK_parts_of_speech` PRIMARY KEY (`pos_id`)
);

CREATE INDEX `IX_parts_of_speech` ON `parts_of_speech` (`pos_name`);

# ---------------------------------------------------------------------- #
# Add table "word_usages"                                                #
# ---------------------------------------------------------------------- #

CREATE TABLE `word_usages` (
    `word_id` INTEGER NOT NULL,
    `contributor_id` INTEGER,
    `example_phrase` VARCHAR(80) NOT NULL,
    `translated_phrase` VARCHAR(240) NOT NULL,
    `review_status` ENUM(PENDING, ACCEPTED, REJECTED) DEFAULT 'PENDING',
    `last_updated` TIMESTAMP NOT NULL DEFAULT NOW(),
    `comments` VARCHAR(40) DEFAULT 'NULL'
);

CREATE INDEX `IX_usages_word` ON `word_usages` (`word_id`);

CREATE INDEX `IDX_usages_time_status` ON `word_usages` (`last_updated`,`review_status`);

# ---------------------------------------------------------------------- #
# Add table "contributors"                                               #
# ---------------------------------------------------------------------- #

CREATE TABLE `contributors` (
    `contributor_id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(20) NOT NULL UNIQUE,
    `password` VARCHAR(20) NOT NULL,
    `real_name` VARCHAR(80) DEFAULT 'NULL',
    `email` VARCHAR(40) NOT NULL UNIQUE,
    `website` VARCHAR(80) DEFAULT 'NULL',
    `date_registered` TIMESTAMP NOT NULL DEFAULT NOW(),
    `comments` VARCHAR(80) DEFAULT 'NULL',
    CONSTRAINT `PK_contributors` PRIMARY KEY (`contributor_id`)
);

CREATE INDEX `IDX_contributors` ON `contributors` (`username`,`password`);

# ---------------------------------------------------------------------- #
# Foreign key constraints                                                #
# ---------------------------------------------------------------------- #

ALTER TABLE `words_main` ADD CONSTRAINT `dictionary_words_main` 
    FOREIGN KEY (`dict_id`) REFERENCES `dictionary` (`dict_id`) ON DELETE CASCADE;

ALTER TABLE `word_synonyms` ADD CONSTRAINT `words_main_word_synonyms` 
    FOREIGN KEY (`word_id`) REFERENCES `words_main` (`word_id`) ON DELETE CASCADE;

ALTER TABLE `word_synonyms` ADD CONSTRAINT `contributors_word_synonyms` 
    FOREIGN KEY (`contributor_id`) REFERENCES `contributors` (`contributor_id`);

ALTER TABLE `word_antonyms` ADD CONSTRAINT `words_main_word_antonyms` 
    FOREIGN KEY (`word_id`) REFERENCES `words_main` (`word_id`) ON DELETE CASCADE;

ALTER TABLE `word_antonyms` ADD CONSTRAINT `contributors_word_antonyms` 
    FOREIGN KEY (`contributor_id`) REFERENCES `contributors` (`contributor_id`);

ALTER TABLE `word_translations` ADD CONSTRAINT `words_main_word_translations` 
    FOREIGN KEY (`word_id`) REFERENCES `words_main` (`word_id`) ON DELETE CASCADE;

ALTER TABLE `word_translations` ADD CONSTRAINT `parts_of_speech_word_translations` 
    FOREIGN KEY (`pos_id`) REFERENCES `parts_of_speech` (`pos_id`) ON DELETE CASCADE;

ALTER TABLE `word_translations` ADD CONSTRAINT `contributors_word_translations` 
    FOREIGN KEY (`contributor_id`) REFERENCES `contributors` (`contributor_id`);

ALTER TABLE `word_usages` ADD CONSTRAINT `words_main_word_usages` 
    FOREIGN KEY (`word_id`) REFERENCES `words_main` (`word_id`) ON DELETE CASCADE;

ALTER TABLE `word_usages` ADD CONSTRAINT `contributors_word_usages` 
    FOREIGN KEY (`contributor_id`) REFERENCES `contributors` (`contributor_id`);

`review_status` ENUM(PENDING, ACCEPTED, REJECTED) DEFAULT 'PENDING'