Database Schema Design

[AtmajKoppikar]

Database Schema Design

• As a developer
• I need to design the database schema to store user data, product information, and interaction data,
• So that the recommendation system can retrieve and process data efficiently.

Details and Assumptions

• Use a relational database.
• Optimize schema for read and write performance.
• Ensure scalability.

Acceptance Criteria

Given a set of database tables,
When querying for user interactions,
Then the database should return results efficiently and accurately.

[Dreamsofriver]

-- Users Table CREATE TABLE Users ( user_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE, registration_date DATE );

-- Products Table CREATE TABLE Products ( product_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), description TEXT, category VARCHAR(50), price DECIMAL(10, 2) );

-- Interactions Table CREATE TABLE Interactions ( interaction_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, product_id INT, interaction_type VARCHAR(50), interaction_date TIMESTAMP, FOREIGN KEY (user_id) REFERENCES Users(user_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) );

-- Recommendations Table CREATE TABLE Recommendations ( recommendation_id INT AUTO_INCREMENT PRIMARY KEY, product_id INT, recommended_product_id INT, recommendation_type ENUM('cross-sell', 'up-sell', 'accessory', 'customer_purchase'), FOREIGN KEY (product_id) REFERENCES Products(product_id), FOREIGN KEY (recommended_product_id) REFERENCES Products(recommended_product_id) );