I’ll help you create a PostgreSQL demo for a slot game. Here’s a complete example with database schema, sample data, and queries:
## Database Schema
“`sql
— Create database
CREATE DATABASE slot_game_demo;
\c slot_game_demo;
— Users table
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
balance DECIMAL(10,2) DEFAULT 100.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
);
— Games table
CREATE TABLE games (
game_id SERIAL PRIMARY KEY,
game_name VARCHAR(100) NOT NULL,
min_bet DECIMAL(5,2) DEFAULT 0.50,
max_bet DECIMAL(10,2) DEFAULT 100.00,
rtp_percentage DECIMAL(5,2) DEFAULT 95.00,
is_active BOOLEAN DEFAULT TRUE
);
— Symbols table for slot reels
CREATE TABLE symbols (
symbol_id SERIAL PRIMARY KEY,
symbol_name VARCHAR(50) NOT NULL,
symbol_value INTEGER NOT NULL, — Payout multiplier
image_url VARCHAR(255),
is_wild BOOLEAN DEFAULT FALSE,
is_scatter BOOLEAN DEFAULT FALSE
);
— Game sessions
CREATE TABLE game_sessions (
session_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
game_id INTEGER REFERENCES games(game_id),
bet_amount DECIMAL(10,2) NOT NULL,
win_amount DECIMAL(10,2) DEFAULT 0.00,
result_symbols JSONB, — Stores the spin result
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_free_spin BOOLEAN DEFAULT FALSE
);
— Transactions table
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
amount DECIMAL(10,2) NOT NULL,
transaction_type VARCHAR(20) CHECK (transaction_type IN (‘deposit’, ‘withdrawal’, ‘bet’, ‘win’)),
status VARCHAR(20) DEFAULT ‘completed’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— Bonus features
CREATE TABLE bonuses (
bonus_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
bonus_type VARCHAR(50) CHECK (bonus_type IN (‘free_spins’, ‘cash_bonus’, ‘multiplier’)),
amount INTEGER, — For free spins count or multiplier value
bonus_amount DECIMAL(10,2), — For cash bonuses
expires_at TIMESTAMP,
is_used BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— Create indexes for performance
CREATE INDEX idx_game_sessions_user_id ON game_sessions(user_id);
CREATE INDEX idx_game_sessions_created_at ON game_sessions(created_at);
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
“`
## Sample Data
“`sql
— Insert sample users
INSERT INTO users (username, email, balance) VALUES
(‘john_doe’, ‘[email protected]’, 500.00),
(‘jane_smith’, ‘[email protected]’, 750.00),
(‘slot_lover’, ‘[email protected]’, 250.00),
(‘demo_player’, ‘[email protected]’, 1000.00);
— Insert games
INSERT INTO games (game_name, min_bet, max_bet, rtp_percentage) VALUES
(‘Lucky 7s’, 0.50, 50.00, 96.50),
(‘Fruit Mania’, 1.00, 100.00, 95.20),
(‘Diamond Rush’, 2.00, 200.00, 97.10),
(‘Mystic Forest’, 0.25, 25.00, 94.80);
— Insert symbols
INSERT INTO symbols (symbol_name, symbol_value, is_wild, is_scatter) VALUES
(‘Seven’, 10, FALSE, FALSE),
(‘Cherry’, 5, FALSE, FALSE),
(‘Lemon’, 3, FALSE, FALSE),
(‘Bell’, 7, FALSE, FALSE),
(‘Bar’, 15, FALSE, FALSE),
(‘Wild’, 0, TRUE, FALSE), — Wild substitutes for others
(‘Scatter’, 0, FALSE, TRUE), — Triggers bonus
(‘Diamond’, 20, FALSE, FALSE);
— Sample game sessions
INSERT INTO game_sessions (user_id, game_id, bet_amount, win_amount, result_symbols) VALUES
(1, 1, 5.00, 50.00, ‘[“Seven”, “Seven”, “Seven”]’),
(1, 1, 5.00, 0.00, ‘[“Cherry”, “Lemon”, “Bell”]’),
(2, 2, 10.00, 150.00, ‘[“Diamond”, “Diamond”, “Diamond”]’),
(3, 3, 2.00, 40.00, ‘[“Bar”, “Bar”, “Wild”]’);
— Sample transactions
INSERT INTO transactions (user_id, amount, transaction_type) VALUES
(1, 100.00, ‘deposit’),
(1, 5.00, ‘bet’),
(1, 50.00, ‘win’),
(2, 50.00, ‘deposit’),
(2, 10.00, ‘bet’),
(2, 150.00, ‘win’);
“`
## Useful Queries
### 1. Player Statistics
“`sql
— Get player’s game history with win rate
SELECT
u.username,
COUNT(gs.session_id) as total_spins,
SUM(gs.bet_amount) as total_wagered,
SUM(gs.win_amount) as total_won,
ROUND(SUM(gs.win_amount) / NULLIF(SUM(gs.bet_amount), 0), 2) as roi,
COUNT(CASE WHEN gs.win_amount > 0 THEN 1 END) as winning_spins,
ROUND(COUNT(CASE WHEN gs.win_amount > 0 THEN 1 END) * 100.0 / COUNT(*), 2) as win_rate
FROM users u
LEFT JOIN game_sessions gs ON u.user_id = gs.user_id
GROUP BY u.user_id
ORDER BY total_wagered DESC;
“`
### 2. Game Performance
“`sql
— Game statistics
SELECT
g.game_name,
COUNT(gs.session_id) as total_spins,
SUM(gs.bet_amount) as total_bets,
SUM(gs.win_amount) as total_payouts,
ROUND(SUM(gs.win_amount) * 100.0 / NULLIF(SUM(gs.bet_amount), 0), 2) as actual_rtp,
g.rtp_percentage as target_rtp
FROM games g
LEFT JOIN game_sessions gs ON g.game_id = gs.game_id
GROUP BY g.game_id
ORDER BY total_bets DESC;
“`
### 3. Recent Big Wins
“`sql
— Top 10 biggest wins
SELECT
u.username,
g.game_name,
gs.bet_amount,
gs.win_amount,
ROUND(gs.win_amount / gs.bet_amount, 2) as multiplier,
gs.created_at
FROM game_sessions gs
JOIN users u ON gs.user_id = u.user_id
JOIN games g ON gs.game_id = g.game_id
WHERE gs.win_amount > 0
ORDER BY gs.win_amount DESC
LIMIT 10;
“`
### 4. Player Balance and Activity
“`sql
— Current player balances with recent activity
SELECT
u.username,
u.balance as current_balance,
COALESCE(SUM(CASE WHEN t.transaction_type = ‘deposit’ THEN t.amount ELSE 0 END), 0) as total_deposits,
COALESCE(SUM(CASE WHEN t.transaction_type = ‘bet’ THEN t.amount ELSE 0 END), 0) as total_bets,
COALESCE(SUM(CASE WHEN t.transaction_type = ‘win’ THEN t.amount ELSE 0 END), 0) as total_wins,
COUNT(gs.session_id) as total_spins,
MAX(gs.created_at) as last_played
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
LEFT JOIN game_sessions gs ON u.user_id = gs.user_id
GROUP BY u.user_id
ORDER BY u.balance DESC;
“`
### 5. Hourly Activity Analysis
“`sql
— Peak playing hours
SELECT
EXTRACT(HOUR FROM created_at) as hour_of_day,
COUNT(*) as total_spins,
SUM(bet_amount) as total_bets,
SUM(win_amount) as total_wins,
ROUND(AVG(win_amount), 2) as avg_win
FROM game_sessions
GROUP BY hour_of_day
ORDER BY total_spins DESC;
“`
### 6. Bonus Trigger Simulation
“`sql
— Function to simulate a spin
CREATE OR REPLACE FUNCTION spin_slot(
p_user_id INTEGER,
p_game_id INTEGER,
p_bet_amount DECIMAL
) RETURNS JSONB AS $$
DECLARE
v_user_balance DECIMAL;
v_min_bet DECIMAL;
v_max_bet DECIMAL;
v_result_symbols TEXT[];
v_win_amount DECIMAL := 0;
v_multiplier INTEGER;
v_bonus_awarded BOOLEAN := FALSE;
BEGIN
— Check user balance
SELECT balance INTO v_user_balance FROM users WHERE user_id = p_user_id;
IF v_user_balance < p_bet_amount THEN
RETURN jsonb_build_object('error', 'Insufficient balance');
END IF;
-- Check bet limits
SELECT min_bet, max_bet INTO v_min_bet, v_max_bet
FROM games WHERE game_id = p_game_id;
IF p_bet_amount < v_min_bet OR p_bet_amount > v_max_bet THEN
RETURN jsonb_build_object(‘error’, ‘Bet amount outside limits’);
END IF;
— Deduct bet amount
UPDATE users SET balance = balance – p_bet_amount
WHERE user_id = p_user_id;
— Insert bet transaction
INSERT INTO transactions (user_id, amount, transaction_type)
VALUES (p_user_id, p_bet_amount, ‘bet’);
— Generate random symbols (simplified)
v_result_symbols := ARRAY[
(SELECT symbol_name FROM symbols ORDER BY RANDOM() LIMIT 1),
(SELECT symbol_name FROM symbols ORDER BY RANDOM() LIMIT 1),
(SELECT symbol_name FROM symbols ORDER BY RANDOM() LIMIT 1)
];
— Calculate win (simplified logic)
— In reality, you’d have complex game logic here
IF v_result_symbols[1] = v_result_symbols[2] AND
v_result_symbols[2] = v_result_symbols[3] THEN
— Three matching symbols
SELECT symbol_value INTO v_multiplier
FROM symbols WHERE symbol_name = v_result_symbols[1];
v_win_amount := p_bet_amount * v_multiplier;
END IF;
— Check for scatter (bonus trigger)
IF ‘Scatter’ = ANY(v_result_symbols) THEN
v_bonus_awarded := TRUE;
— Award free spins
INSERT INTO bonuses (user_id, bonus_type, amount, expires_at)
VALUES (p_user_id, ‘free_spins’, 10, NOW() + INTERVAL ‘7 days’);
END IF;
— Add winnings to balance
IF v_win_amount > 0 THEN
UPDATE users SET balance = balance + v_win_amount
WHERE user_id = p_user_id;
INSERT INTO transactions (user_id, amount, transaction_type)
VALUES (p_user_id, v_win_amount, ‘win’);
END IF;
— Record game session
INSERT INTO game_sessions (user_id, game_id, bet_amount, win_amount, result_symbols)
VALUES (p_user_id, p_game_id, p_bet_amount, v_win_amount,
array_to_json(v_result_symbols)::jsonb);
— Return result
RETURN jsonb_build_object(
‘success’, true,
‘symbols’, v_result_symbols,
‘bet_amount’, p_bet_amount,
‘win_amount’, v_win_amount,
‘new_balance’, v_user_balance – p_bet_amount + v_win_amount,
‘bonus_awarded’, v_bonus_awarded,
‘multiplier’, COALESCE(v_multiplier, 0)
);
END;
$$ LANGUAGE plpgsql;
“`
### 7. Usage Example
“`sql
— Execute a spin
SELECT spin_slot(1, 1, 5.00);
— Check results
SELECT * FROM game_sessions WHERE user_id = 1 ORDER BY created_at DESC LIMIT 5;
— Check user balance
SELECT username, balance FROM users WHERE user_id = 1;
“`
## Additional Features You Could Add:
1. **Progressive Jackpots**:
“`sql
CREATE TABLE jackpots (
jackpot_id SERIAL PRIMARY KEY,
game_id INTEGER REFERENCES games(game_id),
current_amount DECIMAL(15,2) DEFAULT 0.00,
contribution_rate DECIMAL(5,2) DEFAULT 0.01,
must_hit_by DECIMAL(15,2)
);
“`
2. **Player Loyalty Levels**:
“`sql
CREATE TABLE loyalty_levels (
level_id SERIAL PRIMARY KEY,
level_name VARCHAR(50),
min_points INTEGER,
benefits JSONB
);
CREATE TABLE player_loyalty (
user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
loyalty_points INTEGER DEFAULT 0,
current_level INTEGER REFERENCES loyalty_levels(level_id)
);
“`
3. **Game Features**:
“`sql
CREATE TABLE game_features (
feature_id SERIAL PRIMARY KEY,
game_id INTEGER REFERENCES games(game_id),
feature_type VARCHAR(50),
trigger_condition JSONB,
payout_multiplier DECIMAL(5,2)
);
“`
This demo provides a solid foundation for a slot game backend. You can extend it with:
– More complex game logic
– Different slot machine types (3-reel, 5-reel, etc.)
– Bonus games and features
– Tournament systems
– Real-time analytics
– Fraud detection
– Regulatory compliance tracking
Would you like me to elaborate on any specific aspect or add more features to this demo?