Database Creation

Database DDL Script

Во овој дел е прикачена комплетната SQL скрипта за креирање на табелите на PostgreSQL DBMS. Скрипта содржи дефиниции за примарни и надворешни клучеви, автоматски генератори на клучни вредности (SERIAL) и соодветни ограничувања за домените.

Оригиналната SQL скрипта е достапна тука: VidiDB_Creation.sql​

---

DDL Structure by Logical Segments

1. User & Subscription Modules

Овие табели го менаџираат автентицирањето на корисниците, нивните уреди и активните претплати. Користиме комплексен релационен модел за да спречиме злоупотреба на профилите.

CREATE TABLE Subscription (
  SubscriptionID SERIAL PRIMARY KEY,
  Name           varchar(255) NOT NULL,
  Price          numeric(19, 2) NOT NULL CHECK (Price >= 0),
  MaxDevices     int4 NOT NULL CHECK (MaxDevices > 0),
  VideoQuality   varchar(255)
);

CREATE TABLE "User" (
  UserID          SERIAL PRIMARY KEY,
  FirstName       varchar(255),
  LastName        varchar(255),
  Username        varchar(255) NOT NULL UNIQUE,
  Email           varchar(255) NOT NULL UNIQUE 
                  CHECK (Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  password        varchar(255) NOT NULL,
  Date_registered date DEFAULT CURRENT_DATE
);

CREATE TABLE User_Subscription (
  UserSubscriptionID         SERIAL PRIMARY KEY,
  UserUserID                 int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  SubscriptionSubscriptionID int4 NOT NULL REFERENCES Subscription (SubscriptionID) ON DELETE RESTRICT ON UPDATE CASCADE,
  Start_date                 date NOT NULL DEFAULT CURRENT_DATE,
  End_date                   date CHECK (End_date IS NULL OR End_date >= Start_date),
  Status                     varchar(255),
  Auto_renew                 int4 DEFAULT 0 CHECK (Auto_renew IN (0, 1))
);

CREATE TABLE Devices (
  DeviceID           SERIAL PRIMARY KEY,
  DeviceType         varchar(255),
  LastLogIn          date,
  UserSubscriptionID int4 NOT NULL REFERENCES User_Subscription (UserSubscriptionID) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE User_Devices (
  UserUserID      int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  DevicesDeviceID int4 NOT NULL REFERENCES Devices (DeviceID) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (UserUserID, DevicesDeviceID)
);

2. Media Hierarchy & Watchables

Овој дел ја имплементира наследната структура каде филмовите (Movie) и сериите (Series) наследуваат од генералниот медиумски ентитет (Media). За ефикасно следење на репродукцијата, воведен е концептот на Watchable објекти.

CREATE TABLE Media (
  ContentID   SERIAL PRIMARY KEY,
  title       varchar(255) NOT NULL,
  description varchar(255),
  releaseDate date,
  AgeRating   numeric(19, 0) CHECK (AgeRating >= 0 AND AgeRating <= 21)
);

CREATE TABLE Movie (
  MovieID  int4 PRIMARY KEY REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  Duration int4 CHECK (Duration > 0)
);

CREATE TABLE Series (
  SeriesID     int4 PRIMARY KEY REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  TotalSeasons int4 CHECK (TotalSeasons > 0)
);

CREATE TABLE Season (
  SeasonID       SERIAL PRIMARY KEY,
  SeasonNumber   int4 NOT NULL CHECK (SeasonNumber > 0),
  ReleaseYear    int4,
  SeriesSeriesID int4 NOT NULL REFERENCES Series (SeriesID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Episode (
  EpisodeID      SERIAL PRIMARY KEY,
  Title          varchar(255),
  episodeNumber  int4 NOT NULL CHECK (episodeNumber > 0),
  Duration       int4 CHECK (Duration > 0),
  SeasonSeasonID int4 NOT NULL REFERENCES Season (SeasonID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Watchable (
  WatchableID SERIAL PRIMARY KEY,
  duration    int4 CHECK (duration > 0),
  MovieID     int4 REFERENCES Movie (MovieID) ON DELETE SET NULL ON UPDATE CASCADE,
  EpisodeID   int4 REFERENCES Episode (EpisodeID) ON DELETE SET NULL ON UPDATE CASCADE
);

3. Metadata & Taxonomies

Овие табели служат за класификација на медиумската содржина по јазик, жанр и инволвирани артисти (актери, режисери итн.). Врските се реализирани преку чисти релациони табели од тип многу-кон-многу (N:M).

CREATE TABLE Artist (
  ArtistID  SERIAL PRIMARY KEY,
  FirstName varchar(255),
  LastName  varchar(255),
  BirthDate date,
  RoleType  varchar(255) CHECK (RoleType IN ('Actor', 'Director', 'Cameraman', 'Both'))
);

CREATE TABLE Genre (
  GenreID SERIAL PRIMARY KEY,
  Name    varchar(255) NOT NULL UNIQUE
);

CREATE TABLE Language (
  LanguageID SERIAL PRIMARY KEY,
  Name       varchar(255) NOT NULL UNIQUE
);

CREATE TABLE Content_Artist (
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  ArtistArtistID   int4 NOT NULL REFERENCES Artist (ArtistID) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, ArtistArtistID)
);

CREATE TABLE Content_Genre (
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  GenreGenreID     int4 NOT NULL REFERENCES Genre (GenreID) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, GenreGenreID)
);

CREATE TABLE Content_Language (
  ContentContentID   int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  LanguageLanguageID int4 NOT NULL REFERENCES Language (LanguageID) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, LanguageLanguageID)
);

4. User Interaction & History

Овој сегмент ги следи сите кориснички акции во системот: оценување, пишување рецензии, додавање во листи за следење и детална историја на гледање со статус на прогрес.

CREATE TABLE Rating (
  RatingID         SERIAL PRIMARY KEY,
  Rating_Date      date DEFAULT CURRENT_DATE,
  RatingValue      int4 NOT NULL CHECK (RatingValue BETWEEN 1 AND 10),
  UserUserID       int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (UserUserID, ContentContentID)
);

CREATE TABLE Review (
  ReviewID         SERIAL PRIMARY KEY,
  Comment          varchar(255),
  ReviewDate       date DEFAULT CURRENT_DATE,
  UserUserID       int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE WatchHistory (
  HistoryID            SERIAL PRIMARY KEY,
  WatchedAt            date DEFAULT CURRENT_DATE,
  Progress_percentage  int4 CHECK (Progress_percentage BETWEEN 0 AND 100),
  UserUserID           int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID     int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID) ON DELETE CASCADE ON UPDATE CASCADE,
  DevicesDeviceID      int4 REFERENCES Devices (DeviceID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Watchlist (
  WatchlistID          SERIAL PRIMARY KEY,
  dateAdded            date DEFAULT CURRENT_DATE,
  UserUserID           int4 NOT NULL REFERENCES "User" (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID     int4 NOT NULL REFERENCES Media (ContentID) ON DELETE CASCADE ON UPDATE CASCADE,
  WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (UserUserID, ContentContentID)
);

---

Key Implementation Highlights

• Е-mail Валидација преку Регуларен Израз (RegEx): Во табелата "User", внесот на е-маил адресата е строго контролиран на ниво на база со CHECK (Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'). Ова спречува невалидни податоци уште пред да стигнат до апликативниот слој.
• Интегритет на претплати (ON DELETE RESTRICT): За разлика од другите табели каде бришењето оди каскадно (CASCADE), кај User_Subscription кон Subscription е ставено RESTRICT. Тоа значи дека системот нема да дозволи да се избрише одреден претплатен пакет (на пр. "Premium") ако постојат активни корисници кои моментално го плаќаат и користат тој пакет.
• Униформно следење на содржини преку Watchable: Посредничката логика во Watchable овозможува WatchHistory да не се грижи дали корисникот гледал филм или единечна епизода од серија – двата типа се сведуваат на ист апстрактен ID со времетраење, што ја прави базата исклучително скалабилна за идни проширувања.