Entity-Relationship Diagram

Database schema and relationships for DataXpert

Database Structure

ER Diagram

Complete database schema showing all tables and relationships

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. TEAMS

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique team identifier
team_name string NOT NULL Name of the team
owner_id int FK (users.id) Team owner reference
created_at datetime DEFAULT NOW() Team creation timestamp

3. TEAM_MEMBERS

Column Type Constraints Description
id int PK, AUTO_INCREMENT Unique membership identifier
team_id int FK (teams.id) Team reference
user_id int FK (users.id) User reference
role string DEFAULT 'member' Member role (owner/admin/member)
joined_at datetime DEFAULT NOW() Join timestamp

4. 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

5. 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

6. 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 → Teams: One-to-Many (A user can create multiple teams)
  • Users → Team_Members: One-to-Many (A user can be in multiple teams)
  • Teams → Team_Members: One-to-Many (A team has multiple members)
  • 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)