Database Schema

The Suggested Products feature uses a well-designed database schema that efficiently stores product suggestions, frequent product sets, and error logs. The schema is optimized for both read and write operations while maintaining data integrity.

Entity Relationship Diagram

erDiagram
    CUSTOMERS ||--o{ SUGGESTED_PRODUCTS : "has"
    SUGGESTED_PRODUCTS ||--o{ FREQUENT_PRODUCT_SETS : "contains"
    PRODUCTS ||--o{ FREQUENT_PRODUCT_SETS : "includes"
    
    SUGGESTED_PRODUCTS {
        int id PK
        int customer_id FK
        varchar product_set_id
        timestamp apriori_timestamp
        date start_date
        date end_date
        timestamp created_at
        timestamp updated_at
    }
    
    FREQUENT_PRODUCT_SETS {
        int id PK
        varchar product_set_id FK
        int product_id FK
        varchar status
        int priority
        varchar source
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }
    
    GENERATING_SUGGESTED_PRODUCTS_ERROR_LOGS {
        int id PK
        text error_message
        timestamp created_at
        timestamp updated_at
    }
    
    CUSTOMERS {
        int id PK
        varchar name
        varchar email
    }
    
    PRODUCTS {
        int id PK
        varchar name
        varchar description
        decimal price
    }

Table Definitions

suggested_products

This is the main table that stores the generated product suggestions for each customer.

CREATE TABLE suggested_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_set_id VARCHAR(255) NOT NULL,
    apriori_timestamp TIMESTAMP NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_customer_id (customer_id),
    INDEX idx_product_set_id (product_set_id),
    INDEX idx_apriori_timestamp (apriori_timestamp),
    INDEX idx_date_range (start_date, end_date),
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

Field Descriptions

Field	Type	Description
`id`	INT	Primary key, auto-incrementing
`customer_id`	INT	Foreign key to customers table
`product_set_id`	VARCHAR(255)	UUID linking to frequent product sets
`apriori_timestamp`	TIMESTAMP	When the Apriori algorithm was executed
`start_date`	DATE	Start date of the analysis period
`end_date`	DATE	End date of the analysis period
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Record last update timestamp

Indexes

Primary Index: id for unique identification
Customer Index: customer_id for fast customer-based queries
Product Set Index: product_set_id for linking to frequent sets
Timestamp Index: apriori_timestamp for temporal queries
Date Range Index: Composite index on (start_date, end_date) for period filtering

frequent_product_sets

This table stores the individual products within each suggested product set.

CREATE TABLE frequent_product_sets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_set_id VARCHAR(255) NOT NULL,
    product_id INT NOT NULL,
    status VARCHAR(255) DEFAULT 'active',
    priority INT NULL,
    source VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_product_set_id (product_set_id),
    INDEX idx_product_id (product_id),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_deleted_at (deleted_at),
    
    UNIQUE KEY unique_product_set (product_set_id, product_id),
    
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Field Descriptions

Field	Type	Description
`id`	INT	Primary key, auto-incrementing
`product_set_id`	VARCHAR(255)	Links to suggested_products table
`product_id`	INT	Foreign key to products table
`status`	VARCHAR(255)	Status: 'active', 'inactive', 'discontinued'
`priority`	INT	Suggestion priority (1 = highest)
`source`	VARCHAR(255)	Data source identifier
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Record last update timestamp
`deleted_at`	TIMESTAMP	Soft deletion timestamp

Indexes and Constraints

Primary Index: id for unique identification
Product Set Index: product_set_id for grouping products
Product Index: product_id for product-based queries
Status Index: status for filtering active/inactive suggestions
Priority Index: priority for ordering suggestions
Soft Delete Index: deleted_at for excluding deleted records
Unique Constraint: (product_set_id, product_id) prevents duplicates

generating_suggested_products_error_logs

This table stores error logs from the suggestion generation process.

CREATE TABLE generating_suggested_products_error_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    error_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_created_at (created_at)
);

Field Descriptions

Field	Type	Description
`id`	INT	Primary key, auto-incrementing
`error_message`	TEXT	Detailed error message and stack trace
`created_at`	TIMESTAMP	When the error occurred
`updated_at`	TIMESTAMP	Record last update timestamp

Data Relationships

One-to-Many Relationships

Customers → Suggested Products
- One customer can have multiple suggested product sets
- Relationship: customers.id → suggested_products.customer_id
Suggested Products → Frequent Product Sets
- One suggestion can contain multiple products
- Relationship: suggested_products.product_set_id → frequent_product_sets.product_set_id
Products → Frequent Product Sets
- One product can appear in multiple suggestion sets
- Relationship: products.id → frequent_product_sets.product_id

Data Integrity Constraints

-- Foreign key constraints for data integrity
ALTER TABLE suggested_products 
ADD CONSTRAINT fk_suggested_products_customer 
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

ALTER TABLE frequent_product_sets 
ADD CONSTRAINT fk_frequent_product_sets_product 
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE;

-- Check constraints for data validation
ALTER TABLE frequent_product_sets 
ADD CONSTRAINT chk_status 
CHECK (status IN ('active', 'inactive', 'discontinued'));

ALTER TABLE frequent_product_sets 
ADD CONSTRAINT chk_priority 
CHECK (priority >= 1 AND priority <= 100);

Query Patterns

Common Read Queries

1. Get Customer Suggestions

SELECT 
    sp.customer_id,
    sp.product_set_id,
    sp.apriori_timestamp,
    fps.product_id,
    fps.priority,
    p.name as product_name,
    p.price
FROM suggested_products sp
JOIN frequent_product_sets fps ON sp.product_set_id = fps.product_set_id
JOIN products p ON fps.product_id = p.id
WHERE sp.customer_id = ? 
  AND fps.status = 'active'
  AND fps.deleted_at IS NULL
ORDER BY fps.priority ASC, sp.apriori_timestamp DESC;

2. Get Recent Suggestions by Date Range

SELECT 
    sp.*,
    COUNT(fps.product_id) as product_count
FROM suggested_products sp
LEFT JOIN frequent_product_sets fps ON sp.product_set_id = fps.product_set_id
WHERE sp.apriori_timestamp BETWEEN ? AND ?
  AND fps.status = 'active'
GROUP BY sp.id
ORDER BY sp.apriori_timestamp DESC;

3. Popular Product Combinations

SELECT 
    fps.product_set_id,
    GROUP_CONCAT(p.name ORDER BY fps.priority) as product_combination,
    COUNT(DISTINCT sp.customer_id) as customer_count
FROM frequent_product_sets fps
JOIN products p ON fps.product_id = p.id
JOIN suggested_products sp ON fps.product_set_id = sp.product_set_id
WHERE fps.status = 'active'
GROUP BY fps.product_set_id
HAVING COUNT(fps.product_id) >= 2
ORDER BY customer_count DESC;

Common Write Queries

1. Insert Suggestion Set

-- Insert main suggestion record
INSERT INTO suggested_products (customer_id, product_set_id, apriori_timestamp, start_date, end_date)
VALUES (?, ?, NOW(), ?, ?);

-- Insert product set items
INSERT INTO frequent_product_sets (product_set_id, product_id, status, priority, source)
VALUES 
    (?, ?, 'active', 1, 'apriori'),
    (?, ?, 'active', 2, 'apriori'),
    (?, ?, 'active', 3, 'apriori');

2. Update Product Priority

UPDATE frequent_product_sets 
SET priority = ?, updated_at = NOW()
WHERE product_set_id = ? AND product_id = ?;

This database schema provides a robust foundation for the Suggested Products feature, ensuring data integrity, optimal performance, and scalability for growing datasets.

Database Schema

On this page