Simple Notifications Data Model

noti_data_model.sql

-- DDL Statement --

create table users(
	id 			  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	full_name     varchar(100)
);

insert into users(full_name) 
VALUES 
	('Manju Desappa'),
	('Rose'),
	('Emille');

create table notifications(
	id 			  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	category	  varchar(10),
	title         varchar(150),
	description   text,
	is_deleted    boolean default false,
	created_on 	  timestamp
);

insert into notifications(category, title, description, created_on) 
VALUES 
	('info', 'Check profile', 'Some update has happened to your profile', NOW()),
	('critical', 'New report generated', 'Total sales for a month generated', NOW()),
	('medium', 'New feature available', 'Now send messages to other users from the app',NOW());

create table notifications_user(
	id 			  	int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	user_id 	  	int,
	notification_id int,
	read_on       	timestamp default null,
	CONSTRAINT fk_nu_user_id
		FOREIGN KEY(user_id)
		REFERENCES users(id),
	CONSTRAINT fk_nu_notification_id
		FOREIGN KEY(notification_id)
		REFERENCES  notifications(id)
);

insert into notifications_user(user_id, notification_id) 
VALUES 
 (1, 1),
 (1, 2),
 (2, 2),
 (3, 1),
 (3, 2)
	 
-- Queries --

-- Get 10 recent unread notifications for user with id = 1
select t2.user_id, t1.id, t1.category, t1.title, t1.description, t1.created_on, t2.read_on
from notifications t1, notifications_user t2
where t1.id = t2.notification_id
and t2.user_id = 3
and read_on is null
and is_deleted = false
order by 5 desc
limit 10;

-- Update read status of notifications for user with id = 1
update notifications_user
set read_on = NOW()
where notification_id in (1,2)
and user_id = 1