fml chatroom schema

generatedtables

ysql> describe users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> describe rooms;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   | UNI | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe messages;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| text       | varchar(255) | NO   |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
| room_id    | int(11)      | YES  |     | NULL    |                |
| user_id    | int(11)      | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

gistfile1.js

var User = sequelize.define('User', {
  name: {type: Sequelize.STRING, allowNull: false}
});

var Message = sequelize.define('Message', {
  text: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

var Room = sequelize.define('Room', {
  name: {type: Sequelize.STRING, allowNull: false, unique: true}
});

Room.hasMany(Message);
Message.belongsTo(User, {foreignKeyConstraint:true }); 
// Message.belongsTo(Room, {foreignKeyConstraint:true }); 

gistfile2.sql

DROP TABLE IF EXISTS `users`;
    
CREATE TABLE `users` (
  `id_user` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `name_user` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id_user`)
);

-- ---
-- Table 'friendship'
-- 
-- ---

DROP TABLE IF EXISTS `friendship`;
    
CREATE TABLE `friendship` (
  `id_user_from` INTEGER NULL DEFAULT NULL,
  `id_user_to` INTEGER NULL DEFAULT NULL,
  `id_friendship` INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (`id_friendship`)
);

-- ---
-- Table 'messages'
-- 
-- ---

DROP TABLE IF EXISTS `messages`;
    
CREATE TABLE `messages` (
  `id_message` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `id_user_from` INTEGER NULL DEFAULT NULL,
  `message_text` VARCHAR(1000) NULL DEFAULT NULL,
  `createdAt` VARCHAR(100) NULL DEFAULT NULL,
  `id_room` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id_message`)
);

-- ---
-- Table 'rooms'
-- 
-- ---

DROP TABLE IF EXISTS `rooms`;
    
CREATE TABLE `rooms` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `name_room` VARCHAR(20) NULL DEFAULT NULL UNIQUE,
  PRIMARY KEY (`id`)
);

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `friendship` ADD FOREIGN KEY (id_user_from) REFERENCES `users` (`id_user`);
ALTER TABLE `friendship` ADD FOREIGN KEY (id_user_to) REFERENCES `users` (`id_user`);
ALTER TABLE `messages` ADD FOREIGN KEY (id_user_from) REFERENCES `users` (`id_user`);
ALTER TABLE `messages` ADD FOREIGN KEY (id_room) REFERENCES `rooms` (`id`);