Database Schema - luckydeva03/barbershop_app GitHub Wiki

🗄️ Database Schema

Dokumentasi lengkap struktur database sistem manajemen barbershop.

📊 Entity Relationship Diagram

erDiagram
    users ||--o{ history_points : has
    users ||--o{ reviews : writes
    users {
        bigint id PK
        string name
        string email UK
        string password
        string phone
        string profile_photo
        string google_id
        timestamp deleted_at
        timestamps created_updated
    }
    
    admins ||--o{ reedem_codes : creates
    admins {
        bigint id PK
        string name
        string email UK
        string password
        timestamp deleted_at
        timestamps created_updated
    }
    
    stores ||--o{ reviews : receives
    stores {
        bigint id PK
        string name
        text description
        text address
        string phone
        decimal latitude
        decimal longitude
        string image_url
        timestamps created_updated
    }
    
    history_points {
        bigint id PK
        bigint user_id FK
        integer points
        string type
        text description
        timestamps created_updated
    }
    
    reedem_codes {
        bigint id PK
        string code UK
        integer points
        text description
        datetime expires_at
        integer max_uses
        integer used_count
        boolean is_active
        timestamps created_updated
    }
    
    reviews {
        bigint id PK
        bigint user_id FK
        bigint store_id FK
        integer rating
        text comment
        timestamps created_updated
    }

📝 Table Details

👥 Users Table

Tabel utama untuk customer/pengguna aplikasi.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`name`	VARCHAR(255)	NO	-	Nama lengkap user
`email`	VARCHAR(255)	NO	-	Email address (unique)
`email_verified_at`	TIMESTAMP	YES	NULL	Waktu verifikasi email
`password`	VARCHAR(255)	NO	-	Hashed password
`phone`	VARCHAR(20)	YES	NULL	Nomor telepon
`profile_photo`	TEXT	YES	NULL	URL foto profil
`google_id`	VARCHAR(255)	YES	NULL	Google OAuth ID
`remember_token`	VARCHAR(100)	YES	NULL	Remember token
`deleted_at`	TIMESTAMP	YES	NULL	Soft delete timestamp
`created_at`	TIMESTAMP	YES	NULL	Waktu dibuat
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
UNIQUE KEY: users_email_unique (email)
KEY: users_google_id_index (google_id)
KEY: users_deleted_at_index (deleted_at)

Model: App\Models\User

Features:

Soft deletes
Google OAuth integration
Email verification
Remember token untuk "Remember Me"

👨‍💼 Admins Table

Tabel untuk administrator sistem.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`name`	VARCHAR(255)	NO	-	Nama admin
`email`	VARCHAR(255)	NO	-	Email admin (unique)
`email_verified_at`	TIMESTAMP	YES	NULL	Waktu verifikasi email
`password`	VARCHAR(255)	NO	-	Hashed password
`remember_token`	VARCHAR(100)	YES	NULL	Remember token
`deleted_at`	TIMESTAMP	YES	NULL	Soft delete timestamp
`created_at`	TIMESTAMP	YES	NULL	Waktu dibuat
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
UNIQUE KEY: admins_email_unique (email)
KEY: admins_deleted_at_index (deleted_at)

Model: App\Models\Admin

Features:

Soft deletes
Separate authentication guard
Admin-specific permissions

🏪 Stores Table

Tabel informasi toko/barbershop.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`name`	VARCHAR(255)	NO	-	Nama toko
`description`	TEXT	YES	NULL	Deskripsi toko
`address`	TEXT	NO	-	Alamat lengkap
`phone`	VARCHAR(20)	YES	NULL	Nomor telepon
`latitude`	DECIMAL(10,8)	YES	NULL	Koordinat latitude
`longitude`	DECIMAL(11,8)	YES	NULL	Koordinat longitude
`image_url`	TEXT	YES	NULL	URL gambar toko
`created_at`	TIMESTAMP	YES	NULL	Waktu dibuat
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
KEY: stores_latitude_longitude_index (latitude, longitude)

Model: App\Models\Store

Features:

Google Maps integration
Geolocation support
Image gallery support

💎 History Points Table

Tabel riwayat transaksi poin loyalitas.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`user_id`	BIGINT UNSIGNED	NO	-	Foreign key ke users
`points`	INTEGER	NO	-	Jumlah poin (+/-)
`type`	VARCHAR(50)	NO	-	Jenis transaksi
`description`	TEXT	YES	NULL	Deskripsi transaksi
`created_at`	TIMESTAMP	YES	NULL	Waktu transaksi
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
KEY: history_points_user_id_foreign (user_id)
KEY: history_points_user_created_index (user_id, created_at)
KEY: history_points_type_index (type)

Model: App\Models\HistoryPoint

Point Types:

earned: Poin didapat dari redeem code
redeemed: Poin ditukar/digunakan
expired: Poin kadaluarsa
bonus: Poin bonus dari admin
penalty: Pengurangan poin

Foreign Key:

CONSTRAINT `history_points_user_id_foreign` 
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
ON DELETE CASCADE

🎫 Reedem Codes Table

Tabel kode promo/redeem.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`code`	VARCHAR(50)	NO	-	Kode unik
`points`	INTEGER	NO	-	Nilai poin
`description`	TEXT	YES	NULL	Deskripsi kode
`expires_at`	DATETIME	YES	NULL	Tanggal kadaluarsa
`max_uses`	INTEGER	NO	1	Maksimal penggunaan
`used_count`	INTEGER	NO	0	Jumlah sudah digunakan
`is_active`	BOOLEAN	NO	TRUE	Status aktif
`created_at`	TIMESTAMP	YES	NULL	Waktu dibuat
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
UNIQUE KEY: reedem_codes_code_unique (code)
KEY: reedem_codes_active_expires_index (is_active, expires_at)
KEY: reedem_codes_expires_at_index (expires_at)

Model: App\Models\ReedemCode

Features:

Usage tracking
Expiration management
Active/inactive status
Unique code validation

⭐ Reviews Table

Tabel review dan rating toko.

Column	Type	Nullable	Default	Description
`id`	BIGINT UNSIGNED	NO	AUTO_INCREMENT	Primary key
`user_id`	BIGINT UNSIGNED	NO	-	Foreign key ke users
`store_id`	BIGINT UNSIGNED	NO	-	Foreign key ke stores
`rating`	INTEGER	NO	-	Rating 1-5
`comment`	TEXT	YES	NULL	Komentar review
`created_at`	TIMESTAMP	YES	NULL	Waktu review
`updated_at`	TIMESTAMP	YES	NULL	Waktu diupdate

Indexes:

PRIMARY KEY: id
KEY: reviews_user_id_foreign (user_id)
KEY: reviews_store_id_foreign (store_id)
KEY: reviews_store_rating_index (store_id, rating)
KEY: reviews_rating_index (rating)

Model: App\Models\Review

Foreign Keys:

CONSTRAINT `reviews_user_id_foreign` 
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
ON DELETE CASCADE

CONSTRAINT `reviews_store_id_foreign` 
FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) 
ON DELETE CASCADE

Validation Rules:

Rating: 1-5 (integer)
Comment: Max 1000 characters
One review per user per store

🔗 Relationships

User Model Relationships

// User has many history points
public function historyPoints()
{
    return $this->hasMany(HistoryPoint::class);
}

// User has many reviews
public function reviews()
{
    return $this->hasMany(Review::class);
}

// Calculate total points
public function getTotalPointsAttribute()
{
    return $this->historyPoints()->sum('points');
}

Store Model Relationships

// Store has many reviews
public function reviews()
{
    return $this->hasMany(Review::class);
}

// Get average rating
public function getAverageRatingAttribute()
{
    return $this->reviews()->avg('rating') ?? 0;
}

// Get review count
public function getReviewCountAttribute()
{
    return $this->reviews()->count();
}

HistoryPoint Model Relationships

// History point belongs to user
public function user()
{
    return $this->belongsTo(User::class);
}

Review Model Relationships

// Review belongs to user
public function user()
{
    return $this->belongsTo(User::class);
}

// Review belongs to store
public function store()
{
    return $this->belongsTo(Store::class);
}

📈 Database Optimization

Recommended Indexes

-- Performance indexes untuk query yang sering digunakan
CREATE INDEX idx_users_email_verified ON users(email_verified_at);
CREATE INDEX idx_history_points_type_created ON history_points(type, created_at);
CREATE INDEX idx_reviews_created_at ON reviews(created_at DESC);
CREATE INDEX idx_reedem_codes_used_count ON reedem_codes(used_count, max_uses);

-- Composite indexes untuk complex queries
CREATE INDEX idx_history_user_type_created ON history_points(user_id, type, created_at);
CREATE INDEX idx_reviews_store_rating_created ON reviews(store_id, rating, created_at);

Query Optimization Tips

Efficient Point Calculation

// ❌ Inefficient - Multiple queries
$user = User::find(1);
$totalPoints = $user->historyPoints->sum('points');

// ✅ Efficient - Single query with aggregate
$user = User::withSum('historyPoints', 'points')->find(1);
$totalPoints = $user->history_points_sum_points;

Efficient Store Listing with Reviews

// ✅ Eager loading untuk menghindari N+1 problem
$stores = Store::with(['reviews' => function($query) {
    $query->select('store_id', 'rating')
          ->selectRaw('AVG(rating) as avg_rating')
          ->selectRaw('COUNT(*) as review_count')
          ->groupBy('store_id');
}])->get();

Efficient History Points Query

// ✅ Query dengan pagination dan filter
$history = HistoryPoint::where('user_id', $userId)
    ->where('type', 'earned')
    ->orderBy('created_at', 'desc')
    ->paginate(20);

🔄 Migration Files

Create Tables Migrations

# Migration files yang tersedia
database/migrations/
├── 0001_01_01_000000_create_users_table.php
├── 2024_12_17_093431_create_admins_table.php
├── 2024_12_17_111332_create_history_points_table.php
├── 2024_12_17_151944_create_reedem_codes_table.php
├── 2024_12_18_032226_create_reviews_table.php
├── 2025_08_03_121518_create_personal_access_tokens_table.php
├── 2025_08_03_122319_add_soft_deletes_to_users_table.php
└── 2025_08_03_122512_add_soft_deletes_to_admins_table.php

Running Migrations

# Run all migrations
php artisan migrate

# Rollback last migration
php artisan migrate:rollback

# Reset all migrations
php artisan migrate:reset

# Fresh migration dengan seeder
php artisan migrate:fresh --seed

# Check migration status
php artisan migrate:status

🌱 Database Seeders

Available Seeders

database/seeders/
├── DatabaseSeeder.php
├── AdminSeeder.php
├── UserSeeder.php
└── StoreSeeder.php

Running Seeders

# Run all seeders
php artisan db:seed

# Run specific seeder
php artisan db:seed --class=AdminSeeder
php artisan db:seed --class=UserSeeder
php artisan db:seed --class=StoreSeeder

# Fresh migration with all seeders
php artisan migrate:fresh --seed

🔍 Database Queries Examples

Common Queries

Get User with Total Points

$user = User::withSum('historyPoints', 'points')
    ->find($userId);
    
$totalPoints = $user->history_points_sum_points ?? 0;

Get Active Redeem Codes

$activeCodes = ReedemCode::where('is_active', true)
    ->where('expires_at', '>', now())
    ->whereColumn('used_count', '<', 'max_uses')
    ->get();

Get Stores with Average Rating

$stores = Store::withAvg('reviews', 'rating')
    ->withCount('reviews')
    ->get();

Get User Point History

$history = HistoryPoint::where('user_id', $userId)
    ->orderBy('created_at', 'desc')
    ->paginate(10);

Get Top Rated Stores

$topStores = Store::withAvg('reviews', 'rating')
    ->having('reviews_avg_rating', '>=', 4.0)
    ->orderBy('reviews_avg_rating', 'desc')
    ->limit(10)
    ->get();

📊 Database Maintenance

Regular Maintenance Tasks

# Optimize database tables
php artisan db:optimize

# Clear expired codes (custom command)
php artisan app:cleanup-expired-codes

# Backup database
mysqldump -u username -p database_name > backup.sql

# Restore database
mysql -u username -p database_name < backup.sql

Performance Monitoring

-- Check table sizes
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES 
WHERE table_schema = 'barbershop_app'
ORDER BY (data_length + index_length) DESC;

-- Check slow queries
SHOW PROCESSLIST;

-- Analyze table performance
ANALYZE TABLE users, history_points, reviews, stores, reedem_codes;

Next: API Documentation untuk detail endpoint dan response format.