Advertisement
akozhomka

NG test task

Apr 5th, 2022
197
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE IF NOT EXISTS news (
  2.     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
  3.     , date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  4.     , status ENUM('enabled', 'disabled') DEFAULT 'disabled'
  5.     , INDEX (status, date)
  6. );
  7.  
  8. CREATE TABLE IF NOT EXISTS lang (
  9.     id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
  10.     , name VARCHAR(100) NOT NULL
  11.     , abbr CHAR(2) NOT NULL COMMENT "Unique short abbreviation for language, like: ua, en, cz, pl"
  12.     , UNIQUE (abbr)
  13. );
  14.  
  15. CREATE TABLE IF NOT EXISTS translate_news_title (
  16.     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
  17.     , news_id INT UNSIGNED NOT NULL REFERENCES news(id)
  18.     , lang_id SMALLINT UNSIGNED NOT NULL REFERENCES lang(id)
  19.     , title VARCHAR(100) NOT NULL
  20.     , FOREIGN KEY (news_id) REFERENCES news(id)
  21.     , FOREIGN KEY (lang_id) REFERENCES lang(id)
  22.     , UNIQUE (news_id, lang_id)
  23. );
  24.  
  25. CREATE TABLE IF NOT EXISTS translate_news_text (
  26.     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
  27.     , news_id INT UNSIGNED NOT NULL
  28.     , lang_id SMALLINT UNSIGNED NOT NULL
  29.     , text TEXT NOT NULL
  30.     , FOREIGN KEY (news_id) REFERENCES news(id)
  31.     , FOREIGN KEY (lang_id) REFERENCES lang(id)
  32.     , UNIQUE (news_id, lang_id)
  33. );
Advertisement
RAW Paste Data Copied
Advertisement