21 lines
1.5 KiB
SQL
21 lines
1.5 KiB
SQL
CREATE TABLE subscriptions (
|
||
id SERIAL PRIMARY KEY,
|
||
follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- Кто подписывается
|
||
following_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- На кого подписываются
|
||
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
||
UNIQUE(follower_id, following_id), -- Уникальная подписка
|
||
CHECK (follower_id <> following_id) -- Запрет подписки на самого себя
|
||
);
|
||
|
||
-- Уведомления о подписках
|
||
CREATE TABLE subscription_notifications (
|
||
id SERIAL PRIMARY KEY,
|
||
subscription_id INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
|
||
is_read BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Индексы для улучшения производительности
|
||
CREATE INDEX idx_subscriptions_follower_id ON subscriptions(follower_id);
|
||
CREATE INDEX idx_subscriptions_following_id ON subscriptions(following_id);
|
||
CREATE INDEX idx_subscription_notifications_subscription_id ON subscription_notifications(subscription_id); |