Changes between Initial Version and Version 1 of DatabaseCreation

Timestamp:

06/15/26 23:27:34 (2 days ago)

Author:

231166

Comment:

--

DatabaseCreation

@@ +1 @@
+= Database Creation =
+
+== Database DDL Script ==
+Во овој дел е прикачена комплетната SQL скрипта за креирање на табелите на PostgreSQL DBMS. Скрипта содржи дефиниции за примарни и надворешни клучеви, автоматски генератори на клучни вредности (SERIAL) и соодветни ограничувања за домените.
+
+Оригиналната SQL скрипта е достапна тука: [attachment:VidiDB_Creation.sql VidiDB_Creation.sql]
+
+---
+
+== DDL Structure by Logical Segments ==
+
+=== 1. User & Subscription Modules ===
+Овие табели го менаџираат автентицирањето на корисниците, нивните уреди и активните претплати. Користиме комплексен релационен модел за да спречиме злоупотреба на профилите.
+
+{{{
+#!sql
+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` објекти.
+
+{{{
+#!sql
+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).
+
+{{{
+#!sql
+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 ===
+Овој сегмент ги следи сите кориснички акции во системот: оценување, пишување рецензии, додавање во листи за следење и детална историја на гледање со статус на прогрес.
+
+{{{
+#!sql
+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 со времетраење, што ја прави базата исклучително скалабилна за идни проширувања.