twitter

CREATE DATABASE `twitter`;


CREATE TABLE `users` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_date` TIMESTAMP
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `categories` (
  `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `category_name` VARCHAR(255) NOT NULL
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `themes` (
  `theme_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `theme_name` VARCHAR(255) NOT NULL,
  `category_fk` INT UNSIGNED NOT NULL,
  KEY `category_fk` (`category_fk`),
  CONSTRAINT FOREIGN KEY (`category_fk`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `tags` (
  `tag_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tag_name` VARCHAR(255) NOT NULL,
  `theme_fk` INT NOT NULL,
  KEY `theme_fk` (`theme_fk`),
  CONSTRAINT FOREIGN KEY (`theme_fk`) REFERENCES `themeS` (`theme_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `twitts`(
  `twitt_id` INT UNSIGNED NOT NULL PRIMARY KEY,
  `user_fk` INT,
  `twitt_text` VARCHAR(140) NOT NULL,
  `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tag_fk` INT,
  KEY `user_fk` (`user_fk`),
  KEY `tag_fk` (`tag_fk`),
  CONSTRAINT FOREIGN KEY (`user_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY (`tag_fk`) REFERENCES `tags` (`tags_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `lists`(
  `list_id` INT UNSIGNED NOT NULL PRIMARY KEY,
  `list_name` VARCHAR(255) NOT NULL,
  `user_fk` INT UNSIGNED,
  KEY `user_fk` (`user_fk`),
  CONSTRAINT FOREIGN KEY (`user_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `followers`(
  `user_fk` INT UNSIGNED NOT NULL,
  `follower_fk` INT UNSIGNED,
  KEY `user_fk` (`user_fk`),
  CONSTRAINT FOREIGN KEY (`user_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY (`follower_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `favorites`(
  `twitt_fk` INT UNSIGNED NOT NULL,
  `user_fk` INT UNSIGNED NOT NULL,
  KEY `user_fk` (`user_fk`),
  KEY `twitt_fk` (`twitt_fk`),
  CONSTRAINT FOREIGN KEY (`user_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY (`twitt_fk`) REFERENCES `twitts` (`twitt_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `rettwits`(
  `rettwitt_id` INT UNSIGNED NOT NULL PRIMARY KEY,
  `twitt_fk` INT UNSIGNED NOT NULL,
  `user_fk` INT UNSIGNED NOT NULL,
  `retwitt_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `user_fk` (`user_fk`),
  KEY `twitt_fk` (`twitt_fk`),
  CONSTRAINT FOREIGN KEY (`user_fk`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY (`twitt_fk`) REFERENCES `twitts` (`twitt_id`) ON DELETE CASCADE
)ENGINE=InnoDB CHARACTER SET=UTF8;


CREATE TABLE `private`(
  `message_id` INT UNSIGNED NOT NULL PRIMARY KEY,
  `message_sender_fk` INT UNSIGNED,
  `message_receiver_fk` INT UNSIGNED,
  `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `message_text` VARCHAR(255) NOT NULL,
  CONSTRAINT FOREIGN KEY (`message_sender_fk`) REFERENCES `users` (`user_id`) ON SET NULL,
  CONSTRAINT FOREIGN KEY (`message_receiver_fk`) REFERENCES `twitts` (`user_id`) ON SET NULL
)ENGINE=InnoDB CHARACTER SET=UTF8;