Part 1: Designing the Database with MSSQL for a Film/Series Review Application

Introduction to Part 1

In this first part, we’ll start by laying the foundation for our review application: the database design. A well-structured database is critical for ensuring data integrity, scalability, and performance. In this section, we’ll cover everything from table structures to defining relationships between entities, setting up primary and foreign keys, and preparing for efficient data retrieval.

What We’ll Cover

1. Database Structure and Schema Design:
   We’ll discuss the essential tables needed for this application — Users, Movies/Series, Reviews, Ratings, and Tags — and how they will interact.
2. Entity Relationships and Normalization:
   Each table will have specific relationships (one-to-many, many-to-many) based on the application’s needs. We’ll focus on third normal form (3NF) to avoid data redundancy and ensure efficient data management.
3. Setting Up Tables and Constraints:
   We’ll create each table in MSSQL, specify the data types for each column, and establish primary and foreign keys.
4. Stored Procedures for Common Queries:
   As we plan for the API, we’ll create stored procedures to handle frequent operations like retrieving a movie’s reviews or listing top-rated content.

Planning the Database

A film/series review application requires several key entities to function effectively:

• Users: Stores information about each registered user, including unique IDs and profile data.
• Movies/Series: Contains details about the movies and series, such as titles, genres, release dates, and descriptions.
• Reviews: Stores user-submitted reviews linked to specific movies or series.
• Ratings: Allows users to rate movies/series, with scores linked back to the Movies/Series table.
• Tags: Allows categorization of content, enabling users to filter by genre, mood, or themes.

By breaking down each entity in this way, we ensure that our database will be both organized and scalable, accommodating future features without significant redesign.

User Table

CREATE TABLE Users (
    user_id INT PRIMARY KEY IDENTITY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT GETDATE()
);

The Users table is the core of the application's user management. It includes:

• user_id: A primary key with an identity auto-increment for unique user identification.
• username: Holds the display name of each user, set with a NOT NULL constraint to ensure that each user has a valid username.
• email: Unique email addresses are enforced with a UNIQUE constraint, a critical feature for identifying users and ensuring account uniqueness.
• created_at: Automatically records the user’s registration date using GETDATE(), essential for tracking the creation timeline.

The Users table is connected to both the Reviews and Ratings tables through user_id as a foreign key, allowing us to retrieve reviews and ratings made by specific users.

MoviesSeries Table

CREATE TABLE MoviesSeries (
    movie_series_id INT PRIMARY KEY IDENTITY,
    title VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    release_date DATE,
    description TEXT
);

The MoviesSeries table serves as the primary data source for film and series information:

• movie_series_id: A unique identifier as the primary key.
• title: A mandatory field representing the film or series title, essential for identifying each media entry.
• genre: Describes the media’s genre (e.g., Drama, Sci-Fi), useful for filtering.
• release_date: Captures the original release date, aiding in sorting or search functionality.
• description: Provides a summary of the film or series, improving user engagement.

This table is referenced by the Reviews, Ratings, and MovieSeriesTags tables, forming the backbone for relationships across reviews, ratings, and tags related to each media entry.

Reviews Table

CREATE TABLE Reviews (
    review_id INT PRIMARY KEY IDENTITY,
    user_id INT NOT NULL,
    movie_series_id INT NOT NULL,
    review_text TEXT,
    review_date DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (movie_series_id) REFERENCES MoviesSeries(movie_series_id) ON DELETE CASCADE
);

The Reviews table captures individual reviews:

• review_id: Unique identifier for each review entry.
• user_id: A foreign key linking to Users, establishing that each review is created by a specific user.
• movie_series_id: Connects each review to a film or series, reinforcing the relationship between reviews and media entries.
• review_text: Stores the review content.
• review_date: Automatically records the review submission date.

The foreign key constraints with ON DELETE CASCADE ensure that if a user or movie/series is removed, all associated reviews are deleted automatically, preserving data integrity without orphaned records.

Ratings Table

CREATE TABLE Ratings (
    rating_id INT PRIMARY KEY IDENTITY,
    user_id INT NOT NULL,
    movie_series_id INT NOT NULL,
    rating DECIMAL(3, 2) CHECK (rating >= 0 AND rating <= 10),
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (movie_series_id) REFERENCES MoviesSeries(movie_series_id) ON DELETE CASCADE
);

The Ratings table enables users to rate media content:

• rating_id: Unique identifier for each rating.
• user_id and movie_series_id: Foreign keys linking ratings to specific users and movies/series.
• rating: Stores the user’s rating on a decimal scale between 0 and 10, enforced by a CHECK constraint to prevent invalid data entry.

This table supports the application’s rating system and ensures each user can only rate a specific film or series once. ON DELETE CASCADE constraints also apply here, ensuring ratings remain consistent with user and media data.

Tags Table

CREATE TABLE Tags (
    tag_id INT PRIMARY KEY IDENTITY,
    tag_name VARCHAR(50) NOT NULL UNIQUE
);

The Tags table offers categorization functionality:

• tag_id: Unique identifier for each tag.
• tag_name: Contains the tag label, set to be unique to prevent duplicate tags.

Tags enhance search and filtering capabilities, allowing users to view movies or series by themes, genres, or other descriptors.

MovieSeriesTags Table (Join Table for Many-to-Many Relationship)

CREATE TABLE MovieSeriesTags (
    movie_series_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (movie_series_id, tag_id),
    FOREIGN KEY (movie_series_id) REFERENCES MoviesSeries(movie_series_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES Tags(tag_id) ON DELETE CASCADE
);

MovieSeriesTags is a junction table supporting the many-to-many relationship between MoviesSeries and Tags:

• movie_series_id and tag_id: Composite primary key to uniquely identify each combination of a media entry and tag.
• Foreign Key Constraints with ON DELETE CASCADE: If a tag or movie is deleted, related entries in this table are automatically removed, maintaining relational integrity.

This table enables tagging of films/series with multiple descriptors, a powerful feature for enhanced discoverability and categorization.

Final Remarks

In this first part, we’ve built a relational data model that emphasizes data integrity, efficiency, and scalability. By carefully designing each table and defining the relationships, our database structure is now ready to handle essential interactions for the film and series review application.

In Part 2, we’ll enhance this foundation by implementing stored procedures to optimize frequent data operations and manage complex queries effectively. Additionally, we’ll explore other SQL techniques necessary to ensure the application can handle data efficiently as it scales, setting the stage for smooth integration with our backend in C# .NET Core.

Next Part

Part 2: Optimizing the Database with Stored Procedures for a Film/Series Review Application