This is the third of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. The most important outputs of the six step database design process are depicted here, but the details of each of the steps and intermediary outputs are outlined in the book.
| What's on this page |
|---|
| Scenario List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario
A startup company plans to develop a digital streaming platform to deliver on-demand video content, including movies, TV shows, and documentaries. The platform must support multiple user accounts, each with one or more associated profiles that provide a personalized, subscription-based experience. The system must be capable of managing users, subscriptions, content, viewing activity, ratings, and billing to ensure a seamless and engaging user experience.
The application must be capable of:
- Managing user accounts, including login credentials and account status.
- Managing user profiles, including preferences and viewing history.
- Maintaining user subscriptions, including subscription plans, start and end dates, renewal status, and billing records (invoices and payments).
- Managing a digital content catalog, including metadata, availability, and categorization (e.g. movies, documentaries and series with seasons and episodes).
- Recording and tracking user viewing activity through their various profiles, including content viewed, time of viewing, and completion status.
- Recording and tracking user-generated ratings (e.g. star ratings or like/dislike) to support content evaluation and personalized recommendations.
Additionally, the system must be password-protected and support role-based access control, ensuring that users (e.g. viewers, content managers, administrators) can only access features appropriate to their roles. User login activity must also be recorded.
To promote transparency and accountability, the application should include basic auditing features, such as tracking who created or modified records and when. Extensive logging of database changes is not required.
Your job is to design a database that will serve as the back end of this application.
List of entities and their corresponding attributes
Below is the list of entities and their corresponding attributes that is output from Step 1 of the six step database design process as described in the Six-Step Relational Database Design™ book:
| Accounts |
|---|
| (PK) AccountId |
| AccountName |
| ContactNumber |
| GeoLocation |
| Status |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Profiles |
|---|
| (PK) ProfileId |
| ProfileName |
| ProfileType |
| LanguagePreference |
| Avatar |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| SubscriptionPlans |
|---|
| (PK) PlanId |
| PlanName |
| PlanDescription |
| MonthlyPrice |
| MaxProfiles |
| Status |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Invoices |
|---|
| (PK) InvoiceId |
| ReferenceNumber |
| InvoiceDate |
| BillingPeriodStart |
| BillingPeriodEnd |
| Amount |
| Status |
| Comments |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Payments |
|---|
| (PK) PaymentId |
| ReferenceNumber |
| PaymentDate |
| PaymentMethod |
| Amount |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| ContentCatalogItems |
|---|
| (PK) ItemId |
| ContentType |
| ContentTitle |
| ContentDescription |
| ReleaseDate |
| Genre |
| GeoRestriction |
| Language |
| AgeRating |
| RunningTime |
| IsAvailable |
| Thumbnail |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Series |
|---|
| (PK) SeriesId |
| SeriesNumber |
| SeriesTitle |
| SeriesDescription |
| ReleaseYear |
| IsActive |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Seasons |
|---|
| (PK) SeasonId |
| SeasonNumber |
| SeasonTitle |
| SeasonDescription |
| ReleaseYear |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Episodes |
|---|
| (PK) EpisodeId |
| EpisodeNumber |
| EpisodeTitle |
| EpisodeDescription |
| AirDate |
| RunningTime |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Ratings |
|---|
| (PK) RatingId |
| RatingType |
| RatingValue |
| RatingTimestamp |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Users |
|---|
| (PK) UserId |
| Login |
| UserName |
| PasswordHash |
| PasswordSalt |
| IsActive |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Roles |
|---|
| (PK) RoleId |
| RoleName |
| RoleDescription |
| Comments |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| LoginEntries |
|---|
| (PK) LoginEntryId |
| LoggedOn |
| LoggedOff |
| IPAddress |
| CreatedOn |
| ModifiedOn |
Top
Entity-Relationship diagrams
Below are the Simplified Entity-Relationship diagrams that are output from Step 3 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Below are the Detailed Entity-Relationship diagrams that are output from Step 5 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
Relational-Model diagrams
Below are the Relational-Model diagrams that are output from Step 6 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
SQL Commands
The SQL commands below can be used to implement the design depicted above in a MySQL database. Some modifications will be necessary to execute these commands on MS SQL Server, Oracle, or any other RDBMS. Detailed implementation considerations can be found in the Six-Step Relational Database Design™ book.
CREATE TABLE Users ( UserId SERIAL, Login VARCHAR(50) NOT NULL, UserName VARCHAR(50) NOT NULL, PasswordHash VARCHAR(255) NOT NULL, PasswordSalt VARCHAR(255) NOT NULL, IsActive BOOLEAN DEFAULT '1', Role VARCHAR(25) NOT NULL, Email VARCHAR(50) NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (UserId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, UNIQUE INDEX Login (Login), UNIQUE INDEX Email (Email) ); CREATE TABLE LoginEntries ( LogEntryId SERIAL, LoggedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LoggedOff DATETIME DEFAULT NULL, IPAddress VARCHAR(46) NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UserId BIGINT UNSIGNED NOT NULL, PRIMARY KEY (LogEntryId), FOREIGN KEY UserId (UserId) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX LoggedOn (LoggedOn) ); CREATE TABLE ContentCatalogItems ( ItemId SERIAL, ContentType VARCHAR(20) DEFAULT 'Movie', ContentTitle VARCHAR(255) NOT NULL, ContentDescription VARCHAR(1000) NOT NULL, ReleaseDate DATE NOT NULL, Genre VARCHAR(20) DEFAULT 'Drama', GeoRestriction BOOLEAN DEFAULT '0', Language VARCHAR(20) DEFAULT 'English', AgeRating VARCHAR(10) DEFAULT 'PG', RunningTime INTEGER DEFAULT 10, IsAvailable BOOLEAN DEFAULT '1', Thumbnail VARCHAR(2000) DEFAULT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (ItemId), INDEX ContentTitle (ContentTitle), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE SubscriptionPlans ( PlanId SERIAL, PlanName VARCHAR(50) DEFAULT 'Basic', PlanDescription VARCHAR(1000) DEFAULT 'Basic', MonthlyPrice DECIMAL(8,2) DEFAULT '0.00', MaxProfiles SMALLINT DEFAULT 3, Status VARCHAR(20) DEFAULT 'Active', CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (PlanId), INDEX PlanName (PlanName), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Invoices ( InvoiceId SERIAL, ReferenceNumber BIGINT UNSIGNED NOT NULL, InvoiceDate DATETIME DEFAULT CURRENT_TIMESTAMP, BillingPeriodStart DATE NOT NULL, BillingPeriodEnd DATE NOT NULL, Amount DECIMAL(8,2) DEFAULT '0.00', Status VARCHAR(20) DEFAULT 'Up to Date', Comments VARCHAR(1000) NOT NULL, PlanId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (InvoiceId), UNIQUE INDEX ReferenceNumber (ReferenceNumber), INDEX InvoiceDate (InvoiceDate), INDEX BillingPeriodStart (BillingPeriodStart), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY PlanId (PlanId) REFERENCES SubscriptionPlans (PlanId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Payments ( PaymentId SERIAL, ReferenceNumber BIGINT UNSIGNED NOT NULL, PaymentDate DATETIME DEFAULT CURRENT_TIMESTAMP, PaymentNumber BIGINT UNSIGNED NOT NULL, PaymentMethod VARCHAR(20) DEFAULT 'Credit Card', Amount DECIMAL(8,2) DEFAULT '0.00', InvoiceId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (PaymentId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY InvoiceId (InvoiceId) REFERENCES Invoices (InvoiceId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Series ( SeriesId SERIAL, SeriesNumber SMALLINT DEFAULT 1, SeriesTitle VARCHAR(255) NOT NULL, SeriesDescription VARCHAR(1000) NOT NULL, ReleaseYear DATE NOT NULL, IsActive BOOLEAN DEFAULT '1', ItemId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (SeriesId), INDEX SeriesTitle (SeriesTitle), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ItemId (ItemId) REFERENCES ContentCatalogItems (ItemId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Seasons ( SeasonId SERIAL, SeasonNumber SMALLINT DEFAULT 1, SeasonTitle VARCHAR(255) NOT NULL, SeasonDescription VARCHAR(1000) NOT NULL, ReleaseYear DATE NOT NULL, SeriesId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (SeasonId), INDEX SeasonTitle (SeasonTitle), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY SeriesId (SeriesId) REFERENCES Series (SeriesId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Episodes ( EpisodeId SERIAL, EpisodeNumber SMALLINT DEFAULT 1, EpisodeTitle VARCHAR(255) NOT NULL, EpisodeDescription VARCHAR(1000) NOT NULL, AirDate DATE NOT NULL, RunningTime INTEGER UNSIGNED NOT NULL, SeasonId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (EpisodeId), INDEX EpisodeTitle (EpisodeTitle), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY SeasonId (SeasonId) REFERENCES Seasons (SeasonId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Accounts ( AccountId SERIAL, AccountName VARCHAR(50) NOT NULL, Email VARCHAR(50) NOT NULL, ContactNumber VARCHAR(14) DEFAULT NULL, GeoLocation VARCHAR(3) DEFAULT 'US', Status VARCHAR(20) DEFAULT 'Active', PlanId BIGINT UNSIGNED NOT NULL, UserId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (AccountId), INDEX AccountName (AccountName), UNIQUE INDEX Email (Email), INDEX ContactNumber (ContactNumber), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY PlanId (PlanId) REFERENCES SubscriptionPlans (PlanId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY UserId (UserId) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Profiles ( ProfileId SERIAL, ProfileName VARCHAR(10) NOT NULL, ProfileType VARCHAR(10) DEFAULT 'Adult', LanguagePreference VARCHAR(20) DEFAULT 'English', Avatar VARCHAR(2000) DEFAULT NULL, AccountId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (ProfileId), INDEX ProfileName (ProfileName), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY AccountId (AccountId) REFERENCES Accounts (AccountId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Ratings ( RatingId SERIAL, RatingType VARCHAR(10) DEFAULT 'Stars', RatingValue SMALLINT DEFAULT 1, RatingTimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, ItemId BIGINT UNSIGNED NOT NULL, ProfileId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (RatingId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ItemId (ItemId) REFERENCES ContentCatalogItems (ItemId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ProfileId (ProfileId) REFERENCES Profiles (ProfileId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE ProfileEpisodes ( ProfileEpisodeId SERIAL, LastViewedTimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, MinutesViewed INTEGER UNSIGNED NOT NULL, ProfileId BIGINT UNSIGNED NOT NULL, EpisodeId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (ProfileEpisodeId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ProfileId (ProfileId) REFERENCES Profiles (ProfileId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY EpisodeId (EpisodeId) REFERENCES Episodes (EpisodeId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE ProfileCatalogItems ( ProfileItemId SERIAL, LastViewedTimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, MinutesViewed INTEGER UNSIGNED NOT NULL, ProfileId BIGINT UNSIGNED NOT NULL, ItemId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (ProfileItemId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ProfileId (ProfileId) REFERENCES Profiles (ProfileId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ItemId (ItemId) REFERENCES ContentCatalogItems (ItemId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE PlanItems ( PlanItemId SERIAL, PlanId BIGINT UNSIGNED NOT NULL, ItemId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (PlanItemId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY PlanId (PlanId) REFERENCES SubscriptionPlans (PlanId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ItemId (ItemId) REFERENCES ContentCatalogItems (ItemId) ON UPDATE CASCADE ON DELETE RESTRICT );
| Other Case Studies |
|---|
| Case Study 1 Case Study 2 |
Follow