messenger.sql

CREATE TABLE users (
  ID INT NOT NULL PRIMARY KEY IDENTITY(0,1),
  userName VARCHAR(50) NOT NULL,
  isActive CHAR(1)
);

CREATE TABLE messagess (
  messageID INT NOT NULL PRIMARY KEY IDENTITY(0,1),
  content VARCHAR(150) NOT NULL,
  createDate DATETIME NOT NULL,
);

CREATE TABLE groups (
  groupId INT NOT NULL PRIMARY KEY IDENTITY(0,1),
  groupName VARCHAR(150) NOT NULL,
  createDate DATE NOT NULL,
  isActive CHAR(1),
);

CREATE TABLE user_group (
    userID INT NOT NULL FOREIGN KEY REFERENCES users(ID),
    groupId INT NOT NULL FOREIGN KEY REFERENCES groups(groupID),
    CONSTRAINT user_groupID PRIMARY KEY (userID, groupID)
);

CREATE TABLE user_to_user (
	Id INT NOT NULL PRIMARY KEY IDENTITY(0,1),
    senderID INT NOT NULL FOREIGN KEY REFERENCES users(ID),
    recieverID INT NOT NULL FOREIGN KEY REFERENCES users(ID),
	messageID INT NOT NULL FOREIGN KEY REFERENCES messagess(messageID)
);

CREATE TABLE user_to_group (
	Id INT NOT NULL PRIMARY KEY IDENTITY(0,1),
    senderID INT NOT NULL FOREIGN KEY REFERENCES users(ID),
    recieverID INT NOT NULL FOREIGN KEY REFERENCES groups(groupID),
	messageID INT NOT NULL FOREIGN KEY REFERENCES messagess(messageID)
);