Entity-Relationship Diagram

Database schema and relationships for DataXpert

Database Structure

Database Schema Overview

The DataXpert database consists of 4 main tables that manage user authentication, business data storage, and AI analysis results. All relationships are defined with foreign keys to maintain data integrity.

Main Entities: Users, Business Data, Chats, Analysis Results

Database Tables

1. USERS

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique user identifier
name string NOT NULL User's full name
email string UNIQUE, NOT NULL User's email address
password string NULLABLE Hashed password (null for OAuth users)
google_id string NULLABLE, UNIQUE Google OAuth identifier
role string DEFAULT 'user' User role (user/admin)
created_at datetime DEFAULT NOW() Account creation timestamp

2. BUSINESS_DATA

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique data entry identifier
user_id int FK (users.id) Data owner reference
record_date date NOT NULL Date of business record
sales float NOT NULL Sales amount
expenses float NOT NULL Expenses amount
profit float COMPUTED Profit (sales - expenses)
category string NULLABLE Business category

3. CHATS

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique chat identifier
user_id int FK (users.id) Chat owner reference
message string NOT NULL User message
response string NULLABLE AI response
timestamp datetime DEFAULT NOW() Message timestamp

4. ANALYSIS_RESULTS

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique result identifier
chat_id int FK (chats.id) Related chat reference
summary string NOT NULL Analysis summary
anomaly_score float DEFAULT 0.0 Anomaly detection score
insight_level string NOT NULL Insight quality (low/medium/high)
created_at datetime DEFAULT NOW() Analysis timestamp

Relationships

  • Users → Business_Data: One-to-Many (A user has multiple data entries)
  • Users → Chats: One-to-Many (A user has multiple chat sessions)
  • Chats → Analysis_Results: One-to-Many (A chat can have multiple analysis results)