akkirilov

Untitled

Jun 19th, 2017
107
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `credentials` (
  2. `id` INT PRIMARY KEY,
  3. `email` VARCHAR(30),
  4. `password` VARCHAR(20)
  5. );
  6.  
  7. CREATE TABLE `locations` (
  8. `id` INT PRIMARY KEY,
  9. `latitude` DECIMAL(9,5),
  10. `longitude` DECIMAL(9,5)
  11. );
  12.  
  13. CREATE TABLE `users` (
  14. `id` INT PRIMARY KEY,
  15. `nickname` VARCHAR(25),
  16. `gender` CHAR(1),
  17. `age` INT,
  18. `location_id` INT,
  19. `credential_id` INT UNIQUE,
  20. CONSTRAINT `fk_users_locations` FOREIGN KEY (`location_id`)
  21. REFERENCES `locations` (`id`),
  22. CONSTRAINT `fk_users_credentials` FOREIGN KEY (`credential_id`)
  23. REFERENCES `credentials` (`id`)
  24. );
  25.  
  26. CREATE TABLE `chats` (
  27. `id` INT PRIMARY KEY,
  28. `title` VARCHAR(32),
  29. `start_date` DATE,
  30. `is_active` BIT
  31. );
  32.  
  33. CREATE TABLE `messages` (
  34. `id` INT PRIMARY KEY,
  35. `content` VARCHAR(200),
  36. `sent_on` DATE,
  37. `chat_id` INT,
  38. `user_id` INT,
  39. CONSTRAINT `fk_messages_chats` FOREIGN KEY (`chat_id`)
  40. REFERENCES `chats` (`id`),
  41. CONSTRAINT `fk_messages_users` FOREIGN KEY (`user_id`)
  42. REFERENCES `users` (`id`)
  43. );
  44.  
  45. CREATE TABLE `users_chats` (
  46. `user_id` INT,
  47. `chat_id` INT,
  48. CONSTRAINT `pk_users_chats` PRIMARY KEY (`user_id`, `chat_id`),
  49. CONSTRAINT `fk_users_chats_users` FOREIGN KEY (`user_id`)
  50. REFERENCES `users` (`id`),
  51. CONSTRAINT `fk_users_chats_chats` FOREIGN KEY (`chat_id`)
  52. REFERENCES `chats` (`id`)
  53. );
RAW Paste Data