Društvena mreža - SQL projekat

Ovaj projekat predstavlja SQL domaći zadatak modelovan po uzoru na Discord-like aplikaciju, odnosno društvenu mrežu zasnovanu na serverima, kanalima, porukama, prijateljstvima, ulogama i privatnim porukama.

Cilj projekta je da se kroz relacijski model baze podataka prikažu osnovne funkcionalnosti jedne moderne komunikacione platforme. Projekat obuhvata definisanje strukture tabela, njihovih relacija, kao i SQL skripte za kreiranje baze i unos testnih podataka.

dbdiagram.io

Table users {
  id int [pk]
  username varchar(50)
  email varchar(100)
  password varchar(255)
  display_name varchar(100)
  bio text
  avatar_url varchar(255)
  status varchar(20)
  created_at datetime
  updated_at datetime
}

Table servers {
  id int [pk]
  owner_id int [ref: > users.id]
  name varchar(100)
  description text
  icon_url varchar(255)
  created_at datetime
  updated_at datetime
}

Table server_members {
  id int [pk]
  server_id int [ref: > servers.id]
  user_id int [ref: > users.id]
  nickname varchar(100)
  joined_at datetime
}

Table channels {
  id int [pk]
  server_id int [ref: > servers.id]
  name varchar(100)
  type varchar(20)
  topic text
  created_at datetime
  updated_at datetime
}

Table messages {
  id int [pk]
  channel_id int [ref: > channels.id]
  user_id int [ref: > users.id]
  content text
  is_edited boolean
  created_at datetime
  updated_at datetime
}

Table friendships {
  id int [pk]
  requester_id int [ref: > users.id]
  addressee_id int [ref: > users.id]
  status varchar(20)
  created_at datetime
  updated_at datetime
}

Table roles {
  id int [pk]
  server_id int [ref: > servers.id]
  name varchar(50)
  color varchar(20)
  created_at datetime
  updated_at datetime
}

Table user_roles {
  id int [pk]
  user_id int [ref: > users.id]
  role_id int [ref: > roles.id]
  created_at datetime
}

Table reactions {
  id int [pk]
  message_id int [ref: > messages.id]
  user_id int [ref: > users.id]
  emoji varchar(50)
  created_at datetime
}

Table direct_messages {
  id int [pk]
  sender_id int [ref: > users.id]
  receiver_id int [ref: > users.id]
  content text
  is_read boolean
  created_at datetime
  updated_at datetime
}

Table voice_sessions {
  id int [pk]
  channel_id int [ref: > channels.id]
  user_id int [ref: > users.id]
  joined_at datetime
  left_at datetime
}

Google Spreadsheets

Google Sheets dokument.

Supabase - SQL kod

-- USERS
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(255),
    status VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- SERVERS
CREATE TABLE servers (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    owner_id BIGINT REFERENCES users(id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon_url VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- SERVER MEMBERS
CREATE TABLE server_members (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    server_id BIGINT REFERENCES servers(id),
    user_id BIGINT REFERENCES users(id),
    nickname VARCHAR(100),
    joined_at TIMESTAMP
);

-- CHANNELS
CREATE TABLE channels (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    server_id BIGINT REFERENCES servers(id),
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20),
    topic TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- MESSAGES
CREATE TABLE messages (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    channel_id BIGINT REFERENCES channels(id),
    user_id BIGINT REFERENCES users(id),
    content TEXT NOT NULL,
    is_edited BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- FRIENDSHIPS
CREATE TABLE friendships (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    requester_id BIGINT REFERENCES users(id),
    addressee_id BIGINT REFERENCES users(id),
    status VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- ROLES
CREATE TABLE roles (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    server_id BIGINT REFERENCES servers(id),
    name VARCHAR(50),
    color VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- USER ROLES
CREATE TABLE user_roles (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id BIGINT REFERENCES users(id),
    role_id BIGINT REFERENCES roles(id),
    created_at TIMESTAMP
);

-- REACTIONS
CREATE TABLE reactions (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    message_id BIGINT REFERENCES messages(id),
    user_id BIGINT REFERENCES users(id),
    emoji VARCHAR(50),
    created_at TIMESTAMP
);

-- DIRECT MESSAGES
CREATE TABLE direct_messages (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    sender_id BIGINT REFERENCES users(id),
    receiver_id BIGINT REFERENCES users(id),
    content TEXT NOT NULL,
    is_read BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- VOICE SESSIONS
CREATE TABLE voice_sessions (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    channel_id BIGINT REFERENCES channels(id),
    user_id BIGINT REFERENCES users(id),
    joined_at TIMESTAMP,
    left_at TIMESTAMP
);

-- USERS
INSERT INTO users (id, username, email, password, display_name, bio, avatar_url, status, created_at, updated_at) VALUES
(1, 'niko', 'niko@gmail.com', 'hash123', 'Niko Belic', 'Volim backend i SQL', 'avatar1.png', 'online', '2026-05-01 12:00:00', '2026-05-10 14:00:00'),
(2, 'luka', 'luka@gmail.com', 'hash456', 'Luka Petrovic', 'Gaming i programiranje', 'avatar2.png', 'idle', '2026-05-01 12:30:00', '2026-05-11 15:00:00'),
(3, 'sara', 'sara@gmail.com', 'hash789', 'Sara Kovacevic', 'UI/UX dizajn', 'avatar3.png', 'offline', '2026-05-02 10:00:00', '2026-05-09 13:00:00'),
(4, 'milan', 'milan@gmail.com', 'hash321', 'Milan Jovic', 'Linux user', 'avatar4.png', 'online', '2026-05-02 11:00:00', '2026-05-12 18:00:00'),
(5, 'ana', 'ana@gmail.com', 'hash654', 'Ana Ilic', 'Frontend developer', 'avatar5.png', 'dnd', '2026-05-03 09:00:00', '2026-05-13 19:00:00'),
(6, 'marko', 'marko@gmail.com', 'hash987', 'Marko Simic', 'Discord moderator', 'avatar6.png', 'online', '2026-05-03 16:00:00', '2026-05-14 20:00:00');

-- SERVERS
INSERT INTO servers (id, owner_id, name, description, icon_url, created_at, updated_at) VALUES
(1, 1, 'Gaming Balkan', 'Server za gaming i chat', 'gaming.png', '2026-05-01 12:00:00', '2026-05-10 12:00:00'),
(2, 2, 'FIT', 'Fakultetski server', 'fit.png', '2026-05-02 13:00:00', '2026-05-11 13:00:00'),
(3, 4, 'Chill Zona', 'Opusteni voice i chat', 'chill.png', '2026-05-03 14:00:00', '2026-05-12 14:00:00');

-- SERVER MEMBERS
INSERT INTO server_members (id, server_id, user_id, nickname, joined_at) VALUES
(1, 1, 1, 'niko', '2026-05-01 12:05:00'),
(2, 1, 2, 'luka', '2026-05-01 12:10:00'),
(3, 1, 3, 'sara', '2026-05-01 12:15:00'),
(4, 2, 2, 'prof_luka', '2026-05-02 13:10:00'),
(5, 2, 5, 'ana', '2026-05-02 13:15:00'),
(6, 2, 6, 'marko', '2026-05-02 13:20:00'),
(7, 3, 4, 'milan', '2026-05-03 14:10:00'),
(8, 3, 1, 'niko', '2026-05-03 14:15:00'),
(9, 3, 5, 'ana', '2026-05-03 14:20:00');

-- CHANNELS
INSERT INTO channels (id, server_id, name, type, topic, created_at, updated_at) VALUES
(1, 1, 'general', 'text', 'Glavni chat', '2026-05-01 12:00:00', '2026-05-01 12:00:00'),
(2, 1, 'memes', 'text', 'Meme kanal', '2026-05-01 12:05:00', '2026-05-01 12:05:00'),
(3, 1, 'voice-chat', 'voice', 'Gaming voice', '2026-05-01 12:10:00', '2026-05-01 12:10:00'),
(4, 2, 'sql-help', 'text', 'Pomoc oko SQL-a', '2026-05-02 13:00:00', '2026-05-02 13:00:00'),
(5, 2, 'assignments', 'text', 'Domaci zadaci', '2026-05-02 13:05:00', '2026-05-02 13:05:00'),
(6, 3, 'chill', 'text', 'Opusten razgovor', '2026-05-03 14:00:00', '2026-05-03 14:00:00'),
(7, 3, 'music', 'voice', 'Muzika i voice', '2026-05-03 14:05:00', '2026-05-03 14:05:00');

-- MESSAGES
INSERT INTO messages (id, channel_id, user_id, content, is_edited, created_at, updated_at) VALUES
(1, 1, 2, 'Jel neko igra CS2 veceras', false, '2026-05-05 18:00:00', '2026-05-05 18:00:00'),
(2, 1, 1, 'Ja mogu oko 9', false, '2026-05-05 18:02:00', '2026-05-05 18:02:00'),
(3, 2, 3, 'Pogledajte ovaj meme', false, '2026-05-05 18:05:00', '2026-05-05 18:05:00'),
(4, 4, 5, 'Kako radi JOIN u SQL-u', false, '2026-05-06 12:00:00', '2026-05-06 12:00:00'),
(5, 4, 6, 'Koristi INNER JOIN', true, '2026-05-06 12:05:00', '2026-05-06 12:10:00'),
(6, 5, 2, 'Rok za domaci je petak', false, '2026-05-06 14:00:00', '2026-05-06 14:00:00'),
(7, 6, 4, 'Ima li neko film preporuku', false, '2026-05-07 20:00:00', '2026-05-07 20:00:00'),
(8, 6, 1, 'Interstellar obavezno', false, '2026-05-07 20:02:00', '2026-05-07 20:02:00'),
(9, 1, 5, 'Instalirao sam novi Linux distro', false, '2026-05-08 10:00:00', '2026-05-08 10:00:00'),
(10, 2, 6, 'Ovo je najbolji meme danas', false, '2026-05-08 11:00:00', '2026-05-08 11:00:00');

-- FRIENDSHIPS
INSERT INTO friendships (id, requester_id, addressee_id, status, created_at, updated_at) VALUES
(1, 1, 2, 'accepted', '2026-05-01 15:00:00', '2026-05-01 15:10:00'),
(2, 1, 3, 'pending', '2026-05-02 16:00:00', '2026-05-02 16:00:00'),
(3, 2, 5, 'accepted', '2026-05-03 17:00:00', '2026-05-03 17:05:00'),
(4, 4, 6, 'accepted', '2026-05-04 18:00:00', '2026-05-04 18:10:00');

-- ROLES
INSERT INTO roles (id, server_id, name, color, created_at, updated_at) VALUES
(1, 1, 'Admin', 'red', '2026-05-01 12:00:00', '2026-05-01 12:00:00'),
(2, 1, 'Moderator', 'blue', '2026-05-01 12:05:00', '2026-05-01 12:05:00'),
(3, 1, 'Member', 'gray', '2026-05-01 12:10:00', '2026-05-01 12:10:00'),
(4, 2, 'Profesor', 'green', '2026-05-02 13:00:00', '2026-05-02 13:00:00'),
(5, 2, 'Student', 'yellow', '2026-05-02 13:05:00', '2026-05-02 13:05:00'),
(6, 3, 'VIP', 'purple', '2026-05-03 14:00:00', '2026-05-03 14:00:00');

-- USER ROLES
INSERT INTO user_roles (id, user_id, role_id, created_at) VALUES
(1, 1, 1, '2026-05-01 12:00:00'),
(2, 2, 2, '2026-05-01 12:05:00'),
(3, 3, 3, '2026-05-01 12:10:00'),
(4, 2, 4, '2026-05-02 13:00:00'),
(5, 5, 5, '2026-05-02 13:05:00'),
(6, 4, 6, '2026-05-03 14:00:00');

-- REACTIONS
INSERT INTO reactions (id, message_id, user_id, emoji, created_at) VALUES
(1, 1, 3, '👍', '2026-05-05 18:01:00'),
(2, 2, 2, '😂', '2026-05-05 18:03:00'),
(3, 4, 6, '❤️', '2026-05-06 12:06:00'),
(4, 7, 1, '🎬', '2026-05-07 20:01:00'),
(5, 10, 5, '🔥', '2026-05-08 11:01:00');

-- DIRECT MESSAGES
INSERT INTO direct_messages (id, sender_id, receiver_id, content, is_read, created_at, updated_at) VALUES
(1, 1, 2, 'Jesi zavrsio domaci', true, '2026-05-09 10:00:00', '2026-05-09 10:05:00'),
(2, 2, 1, 'Skoro pa jesam', true, '2026-05-09 10:10:00', '2026-05-09 10:15:00'),
(3, 3, 5, 'Posalji mi prezentaciju', false, '2026-05-09 11:00:00', '2026-05-09 11:00:00'),
(4, 6, 4, 'Dolazis na voice', true, '2026-05-09 12:00:00', '2026-05-09 12:02:00');

-- VOICE SESSIONS
INSERT INTO voice_sessions (id, channel_id, user_id, joined_at, left_at) VALUES
(1, 3, 1, '2026-05-10 20:00:00', '2026-05-10 22:00:00'),
(2, 3, 2, '2026-05-10 20:05:00', '2026-05-10 21:30:00'),
(3, 7, 4, '2026-05-11 19:00:00', '2026-05-11 20:00:00'),
(4, 7, 5, '2026-05-11 19:10:00', '2026-05-11 20:10:00');

dbdiagram.io​

Google Spreadsheets​

Supabase - SQL kod​

dbdiagram.io

Google Spreadsheets

Supabase - SQL kod