Scribd
Upload
11 views5 pages

Book Recommendation Database Schema

The document outlines the SQL schema for a book recommendation database, including core tables for users, publishers, authors, genres, and books. It also details tables for authentication, customer orders, delivery logistics, payments, subscriptions, royalties, and reviews, establishing relationships and constraints among them. The structure supports functionalities such as user management, order processing, and content delivery.

Uploaded by

prince447366
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views5 pages

Book Recommendation Database Schema

The document outlines the SQL schema for a book recommendation database, including core tables for users, publishers, authors, genres, and books. It also details tables for authentication, customer orders, delivery logistics, payments, subscriptions, royalties, and reviews, establishing relationships and constraints among them. The structure supports functionalities such as user management, order processing, and content delivery.

Uploaded by

prince447366
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

-- Create the database (Optional: if it doesn't exist)

CREATE DATABASE IF NOT EXISTS book_recommendation_db;


USE book_recommendation_db;

-- -----------------------------------------------------
-- CORE TABLES
-- -----------------------------------------------------

CREATE TABLE User (


`userID` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(100) NOT NULL UNIQUE,
`email` VARCHAR(255) NOT NULL UNIQUE,
`passwordHash` VARCHAR(255) NOT NULL,
`role` ENUM('admin', 'customer', 'author', 'publisher', 'delivery') NOT NULL DEFAULT
'customer',
`createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Publisher (


`publisherID` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`location` VARCHAR(255) NULL,
`userID` INT NULL UNIQUE, -- Link to a user account for portal access
CONSTRAINT `fk_publisher_user` FOREIGN KEY (`userID`) REFERENCES
User(`userID`) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Author (


`authorID` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`biography` TEXT NULL,
`userID` INT NULL UNIQUE, -- Link to a user account for portal access
CONSTRAINT `fk_author_user` FOREIGN KEY (`userID`) REFERENCES User(`userID`)
ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Genre (


`genreID` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Book (


`bookID` INT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`isbn` VARCHAR(25) NULL UNIQUE,
`description` TEXT NULL,
`coverImageURL` VARCHAR(2048) NULL,
`publicationYear` INT NULL,
`price` DECIMAL(10, 2) NOT NULL,
`isPremium` BOOLEAN NOT NULL DEFAULT FALSE, -- For premium content
`publisherID` INT NULL,
CONSTRAINT `fk_book_publisher` FOREIGN KEY (`publisherID`) REFERENCES
Publisher(`publisherID`) ON DELETE SET NULL ON UPDATE CASCADE
);

-- -----------------------------------------------------
-- AUTHENTICATION & SESSIONS
-- -----------------------------------------------------
CREATE TABLE UserSession (
`sessionID` INT PRIMARY KEY AUTO_INCREMENT,
`userID` INT NOT NULL,
`authToken` VARCHAR(255) NOT NULL UNIQUE,
`createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expiresAt` DATETIME NOT NULL,
`lastUsedAt` DATETIME NULL,
CONSTRAINT `fk_session_user` FOREIGN KEY (`userID`) REFERENCES User
(`userID`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- -----------------------------------------------------
-- CUSTOMER ORDER & DELIVERY LOGISTICS
-- -----------------------------------------------------

CREATE TABLE ShippingAddress (


`addressID` INT PRIMARY KEY AUTO_INCREMENT,
`street` VARCHAR(255) NOT NULL,
`city` VARCHAR(100) NOT NULL,
`state` VARCHAR(100) NOT NULL,
`zipCode` VARCHAR(20) NOT NULL,
`country` VARCHAR(100) NOT NULL DEFAULT 'India',
`userID` INT NOT NULL,
CONSTRAINT `fk_address_user` FOREIGN KEY (`userID`) REFERENCES
User(`userID`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `Order` (


`orderID` INT PRIMARY KEY AUTO_INCREMENT,
`orderDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`totalAmount` DECIMAL(10, 2) NOT NULL,
`status` ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT
'pending',
`userID` INT NOT NULL,
`shippingAddressID` INT NOT NULL,
CONSTRAINT `fk_order_user` FOREIGN KEY (`userID`) REFERENCES User(`userID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_order_address` FOREIGN KEY (`shippingAddressID`) REFERENCES
ShippingAddress(`addressID`) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE OrderItem (


`orderID` INT NOT NULL,
`bookID` INT NOT NULL,
`quantity` INT NOT NULL DEFAULT 1,
`priceAtPurchase` DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (`orderID`, `bookID`),
CONSTRAINT `fk_oi_order` FOREIGN KEY (`orderID`) REFERENCES `Order`(`orderID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_oi_book` FOREIGN KEY (`bookID`) REFERENCES Book(`bookID`)
ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE DeliveryPartner (


`partnerID` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`contactEmail` VARCHAR(255) UNIQUE,
`trackingAPIBaseURL` VARCHAR(2048) NULL,
`userID` INT NULL UNIQUE, -- Link to a user account for portal access
CONSTRAINT `fk_delivery_user` FOREIGN KEY (`userID`) REFERENCES
User(`userID`) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Shipment (


`shipmentID` INT PRIMARY KEY AUTO_INCREMENT,
`orderID` INT NOT NULL UNIQUE, -- Each order gets one shipment
`partnerID` INT NOT NULL,
`trackingNumber` VARCHAR(100) NULL,
`shippedDate` DATETIME NULL,
`estimatedDeliveryDate` DATE NULL,
`actualDeliveryDate` DATETIME NULL,
`status` ENUM('label_created', 'in_transit', 'out_for_delivery', 'delivered', 'failed') NOT
NULL,
CONSTRAINT `fk_shipment_order` FOREIGN KEY (`orderID`) REFERENCES
`Order`(`orderID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_shipment_partner` FOREIGN KEY (`partnerID`) REFERENCES
DeliveryPartner(`partnerID`) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- -----------------------------------------------------
-- PAYMENTS, SUBSCRIPTIONS & ROYALTIES
-- -----------------------------------------------------

CREATE TABLE Subscription (


`subscriptionID` INT PRIMARY KEY AUTO_INCREMENT,
`userID` INT NOT NULL,
`planType` ENUM('monthly', 'yearly') NOT NULL,
`startDate` DATETIME NOT NULL,
`endDate` DATETIME NOT NULL,
`status` ENUM('active', 'cancelled', 'expired') NOT NULL,
CONSTRAINT `fk_subscription_user` FOREIGN KEY (`userID`) REFERENCES
User(`userID`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE PaymentTransaction (


`transactionID` INT PRIMARY KEY AUTO_INCREMENT,
`userID` INT NOT NULL,
`orderID` INT NULL, -- Can be for an order
`subscriptionID` INT NULL, -- Or for a subscription
`gateway` VARCHAR(50) NOT NULL, -- e.g., 'Stripe', 'PayPal', 'Razorpay'
`gatewayTransactionID` VARCHAR(255) NOT NULL,
`amount` DECIMAL(10, 2) NOT NULL,
`currency` VARCHAR(10) NOT NULL DEFAULT 'INR',
`status` ENUM('pending', 'success', 'failed') NOT NULL,
`createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT `fk_payment_user` FOREIGN KEY (`userID`) REFERENCES
User(`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_payment_order` FOREIGN KEY (`orderID`) REFERENCES
`Order`(`orderID`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_subscription` FOREIGN KEY (`subscriptionID`)
REFERENCES `Subscription`(`subscriptionID`) ON DELETE SET NULL ON UPDATE
CASCADE
);

CREATE TABLE RoyaltyPayment (


`royaltyID` INT PRIMARY KEY AUTO_INCREMENT,
`authorID` INT NULL,
`publisherID` INT NULL,
`paymentPeriodStart` DATE NOT NULL,
`paymentPeriodEnd` DATE NOT NULL,
`amount` DECIMAL(12, 2) NOT NULL,
`paymentDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`transactionID` INT NOT NULL, -- Links to the outgoing payment transaction
CONSTRAINT `fk_royalty_author` FOREIGN KEY (`authorID`) REFERENCES
Author(`authorID`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_royalty_publisher` FOREIGN KEY (`publisherID`) REFERENCES
Publisher(`publisherID`) ON DELETE SET NULL ON UPDATE CASCADE
);

-- -----------------------------------------------------
-- REVIEWS & MANY-TO-MANY LINKING TABLES
-- -----------------------------------------------------

CREATE TABLE Review (


`reviewID` INT PRIMARY KEY AUTO_INCREMENT,
`rating` INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
`comment` TEXT NULL,
`createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`userID` INT NOT NULL,
`bookID` INT NOT NULL,
CONSTRAINT `fk_review_user` FOREIGN KEY (`userID`) REFERENCES User(`userID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_review_book` FOREIGN KEY (`bookID`) REFERENCES
Book(`bookID`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE BookAuthor (


`bookID` INT NOT NULL,
`authorID` INT NOT NULL,
PRIMARY KEY (`bookID`, `authorID`),
CONSTRAINT `fk_ba_book` FOREIGN KEY (`bookID`) REFERENCES Book(`bookID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ba_author` FOREIGN KEY (`authorID`) REFERENCES
Author(`authorID`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE BookGenre (


`bookID` INT NOT NULL,
`genreID` INT NOT NULL,
PRIMARY KEY (`bookID`, `genreID`),
CONSTRAINT `fk_bg_book` FOREIGN KEY (`bookID`) REFERENCES Book(`bookID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_bg_genre` FOREIGN KEY (`genreID`) REFERENCES
Genre(`genreID`) ON DELETE CASCADE ON UPDATE CASCADE
);

You might also like